aboutsummaryrefslogtreecommitdiffstats
path: root/notes
diff options
context:
space:
mode:
authorDavid M. Syzdek <syzdek@bindlebinaries.com>2019-05-23 22:02:02 -0800
committerDavid M. Syzdek <syzdek@bindlebinaries.com>2019-05-23 22:02:02 -0800
commit543eb7bb4e8fad6e0ae80eb63f50dc44fccbe2c7 (patch)
tree3aca44f05d8260e670478f50ae637f4a00d0d0b0 /notes
parentce2cda9fe4fd2d45aca581db20d468d4ffdebc64 (diff)
downloadscratchpad-543eb7bb4e8fad6e0ae80eb63f50dc44fccbe2c7.tar.gz
scratchpad-543eb7bb4e8fad6e0ae80eb63f50dc44fccbe2c7.tar.bz2
scratchpad-543eb7bb4e8fad6e0ae80eb63f50dc44fccbe2c7.tar.xz
scratchpad-543eb7bb4e8fad6e0ae80eb63f50dc44fccbe2c7.zip
replacing netblock.assignable with netblock.busyout
Diffstat (limited to 'notes')
-rw-r--r--notes/projects/radius/sql/ippool-demo.sql13
-rw-r--r--notes/projects/radius/sql/ippool-functions.sql92
-rw-r--r--notes/projects/radius/sql/ippool-schema.sql47
-rw-r--r--notes/projects/radius/sql/untitled.sql129
4 files changed, 207 insertions, 74 deletions
diff --git a/notes/projects/radius/sql/ippool-demo.sql b/notes/projects/radius/sql/ippool-demo.sql
index 3693c7a..57352d8 100644
--- a/notes/projects/radius/sql/ippool-demo.sql
+++ b/notes/projects/radius/sql/ippool-demo.sql
@@ -21,12 +21,13 @@ INSERT INTO pool ( name ) VALUES ( 'reserved' );
21INSERT INTO region ( name ) VALUES ( 'Anchorage' ); 21INSERT INTO region ( name ) VALUES ( 'Anchorage' );
22INSERT INTO region ( name ) VALUES ( 'Soldotna' ); 22INSERT INTO region ( name ) VALUES ( 'Soldotna' );
23INSERT INTO region ( name ) VALUES ( 'Fairbanks' ); 23INSERT INTO region ( name ) VALUES ( 'Fairbanks' );
24INSERT INTO region ( name ) VALUES ( 'No where' ); 24INSERT INTO region ( name ) VALUES ( 'Juneau' );
25INSERT INTO region ( name ) VALUES ( 'Lab' );
25 26
26 27
27-- create example NAS clients 28-- create example NAS clients
28SELECT nas_add( nasName := 'br1.cwc', regionName := 'Anchorage' ); 29SELECT nas_add( nasName := 'br1.cwc', regionName := 'Anchorage' );
29SELECT nas_add( nasName := 'bras.eng', regionName := 'Anchorage' ); 30SELECT nas_add( nasName := 'bras.eng', regionName := 'Lab' );
30SELECT nas_add( nasName := 'br1.sol', regionName := 'Soldotna' ); 31SELECT nas_add( nasName := 'br1.sol', regionName := 'Soldotna' );
31 32
32 33
@@ -41,7 +42,7 @@ SELECT netblock_add
41 poolName := 'DHCP', 42 poolName := 'DHCP',
42 regionName := 'Soldotna', 43 regionName := 'Soldotna',
43 netDesc := 'Reserved for engineering test accounts', 44 netDesc := 'Reserved for engineering test accounts',
44 netAssignable := FALSE 45 netBusyout := TRUE
45 ); 46 );
46SELECT netblock_add 47SELECT netblock_add
47 ( netAddress := '209.193.4.4/30', 48 ( netAddress := '209.193.4.4/30',
@@ -75,19 +76,19 @@ SELECT netblock_add
75SELECT netblock_add 76SELECT netblock_add
76 ( netAddress := '209.193.4.96/28', 77 ( netAddress := '209.193.4.96/28',
77 poolName := 'DHCP', 78 poolName := 'DHCP',
78 netAssignable := FALSE, 79 netBusyout := TRUE,
79 addIpEntries := FALSE 80 addIpEntries := FALSE
80 ); 81 );
81SELECT netblock_add 82SELECT netblock_add
82 ( netAddress := '209.193.4.112/28', 83 ( netAddress := '209.193.4.112/28',
83 poolName := 'PPPoE', 84 poolName := 'PPPoE',
84 netAssignable := FALSE, 85 netBusyout := TRUE,
85 addIpEntries := FALSE 86 addIpEntries := FALSE
86 ); 87 );
87SELECT netblock_add 88SELECT netblock_add
88 ( netAddress := '209.193.4.128/25', 89 ( netAddress := '209.193.4.128/25',
89 poolName := 'Reserved', 90 poolName := 'Reserved',
90 netAssignable := FALSE, 91 netBusyout := TRUE,
91 addIpEntries := FALSE 92 addIpEntries := FALSE
92 ); 93 );
93 94
diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql
index dc91396..7ec18a6 100644
--- a/notes/projects/radius/sql/ippool-functions.sql
+++ b/notes/projects/radius/sql/ippool-functions.sql
@@ -175,7 +175,7 @@ BEGIN
175 PERFORM network 175 PERFORM network
176 FROM netblock 176 FROM netblock
177 WHERE netblock.network >>= assignIp 177 WHERE netblock.network >>= assignIp
178 AND netblock.assignable = FALSE; 178 AND netblock.busyout = TRUE;
179 IF FOUND THEN 179 IF FOUND THEN
180 RETURN FALSE; 180 RETURN FALSE;
181 END IF; 181 END IF;
@@ -228,7 +228,7 @@ CREATE OR REPLACE FUNCTION ip_audit
228 poolName VARCHAR(32), 228 poolName VARCHAR(32),
229 regionName VARCHAR(32), 229 regionName VARCHAR(32),
230 ipAddress INET, 230 ipAddress INET,
231 assignable BOOLEAN, 231 busyout BOOLEAN,
232 assigned BOOLEAN, 232 assigned BOOLEAN,
233 modified TIMESTAMP WITH TIME ZONE, 233 modified TIMESTAMP WITH TIME ZONE,
234 nasName VARCHAR(32), 234 nasName VARCHAR(32),
@@ -244,9 +244,9 @@ BEGIN
244 SELECT DISTINCT pool.name AS poolName, 244 SELECT DISTINCT pool.name AS poolName,
245 region.name AS regionName, 245 region.name AS regionName,
246 ip.address AS address, 246 ip.address AS address,
247 TRUE = ALL 247 TRUE = ANY
248 ( 248 (
249 SELECT nb2.assignable 249 SELECT nb2.busyout
250 FROM netblock AS nb2 250 FROM netblock AS nb2
251 WHERE ip.address <<= nb2.network 251 WHERE ip.address <<= nb2.network
252 ) AS assignable, 252 ) AS assignable,
@@ -561,7 +561,7 @@ CREATE OR REPLACE FUNCTION netblock_add
561 netAddress CIDR, 561 netAddress CIDR,
562 poolName VARCHAR(32), 562 poolName VARCHAR(32),
563 regionName VARCHAR(32) DEFAULT NULL, 563 regionName VARCHAR(32) DEFAULT NULL,
564 netAssignable BOOLEAN DEFAULT TRUE, 564 netBusyout BOOLEAN DEFAULT FALSE,
565 netDesc VARCHAR(128) DEFAULT NULL, 565 netDesc VARCHAR(128) DEFAULT NULL,
566 addIpEntries BOOLEAN DEFAULT TRUE 566 addIpEntries BOOLEAN DEFAULT TRUE
567) 567)
@@ -600,13 +600,13 @@ BEGIN
600 ( network, 600 ( network,
601 poolId, 601 poolId,
602 regionId, 602 regionId,
603 assignable, 603 busyout,
604 description 604 description
605 ) 605 )
606 VALUES ( netAddress, 606 VALUES ( netAddress,
607 netPoolId, 607 netPoolId,
608 netRegionId, 608 netRegionId,
609 netAssignable, 609 netBusyout,
610 netDesc 610 netDesc
611 ) 611 )
612 RETURNING id INTO returnId; 612 RETURNING id INTO returnId;
@@ -636,7 +636,7 @@ CREATE OR REPLACE FUNCTION netblock_audit
636 poolName VARCHAR(32), 636 poolName VARCHAR(32),
637 regionName VARCHAR(32), 637 regionName VARCHAR(32),
638 network CIDR, 638 network CIDR,
639 assignable BOOLEAN, 639 busyout BOOLEAN,
640 ipAssigned BIGINT, 640 ipAssigned BIGINT,
641 ipUnassigned BIGINT, 641 ipUnassigned BIGINT,
642 ipAvailable BIGINT, 642 ipAvailable BIGINT,
@@ -653,7 +653,7 @@ BEGIN
653 SELECT DISTINCT pool.name AS poolName, 653 SELECT DISTINCT pool.name AS poolName,
654 region.name AS regionname, 654 region.name AS regionname,
655 netblock.network AS network, 655 netblock.network AS network,
656 netblock.assignable AS assignable, 656 netblock.busyout AS busyout,
657 ( 657 (
658 SELECT count(address) 658 SELECT count(address)
659 FROM ip 659 FROM ip
@@ -671,7 +671,7 @@ BEGIN
671 FROM ip 671 FROM ip
672 INNER JOIN netblock AS nb2 672 INNER JOIN netblock AS nb2
673 ON nb2.id = ip.netblockId 673 ON nb2.id = ip.netblockId
674 WHERE nb2.assignable IS TRUE 674 WHERE nb2.busyout IS FALSE
675 AND ip.address <<= netblock.network 675 AND ip.address <<= netblock.network
676 AND ip.nasId IS NULL 676 AND ip.nasId IS NULL
677 ) AS ipAvailable, 677 ) AS ipAvailable,
@@ -705,10 +705,10 @@ CREATE OR REPLACE FUNCTION region_audit
705 ( 705 (
706 poolName VARCHAR(32), 706 poolName VARCHAR(32),
707 regionName VARCHAR(32), 707 regionName VARCHAR(32),
708 ipAssigned BIGINT, 708 ipAssignedFree BIGINT,
709 ipUnassigned BIGINT, 709 ipAssignedBusy BIGINT,
710 ipAvailable BIGINT, 710 ipAvailable BIGINT,
711 ipUnassignable BIGINT, 711 ipBusyout BIGINT,
712 ipTotal BIGINT, 712 ipTotal BIGINT,
713 description TEXT 713 description TEXT
714 ) 714 )
@@ -724,21 +724,23 @@ BEGIN
724 ( 724 (
725 SELECT COUNT( address ) 725 SELECT COUNT( address )
726 FROM ip 726 FROM ip
727 INNER JOIN netblock AS nb2 727 INNER JOIN netblock AS nb2
728 ON nb2.id = ip.netblockId 728 ON nb2.id = ip.netblockId
729 WHERE nb2.regionId = region.id 729 WHERE nb2.regionId = region.id
730 AND nb2.poolId = pool.id 730 AND nb2.poolId = pool.id
731 AND ip.nasId IS NOT NULL 731 AND nb2.busyout IS FALSE
732 ) AS ipAssigned, 732 AND ip.nasId IS NOT NULL
733 ( 733 ) AS ipAssignedFree,
734 (
734 SELECT COUNT( address ) 735 SELECT COUNT( address )
735 FROM ip 736 FROM ip
736 INNER JOIN netblock AS nb2 737 INNER JOIN netblock AS nb2
737 ON nb2.id = ip.netblockId 738 ON nb2.id = ip.netblockId
738 WHERE nb2.regionId = region.id 739 WHERE nb2.regionId = region.id
739 AND nb2.poolId = pool.id 740 AND nb2.poolId = pool.id
740 AND ip.nasId IS NULL 741 AND nb2.busyout IS TRUE
741 ) AS ipUnassigned, 742 AND ip.nasId IS NOT NULL
743 ) AS ipAssignedBusy,
742 ( 744 (
743 SELECT COUNT( address ) 745 SELECT COUNT( address )
744 FROM ip 746 FROM ip
@@ -746,7 +748,7 @@ BEGIN
746 ON nb2.id = ip.netblockId 748 ON nb2.id = ip.netblockId
747 WHERE nb2.regionId = region.id 749 WHERE nb2.regionId = region.id
748 AND nb2.poolId = pool.id 750 AND nb2.poolId = pool.id
749 AND nb2.assignable IS TRUE 751 AND nb2.busyout IS FALSE
750 AND ip.nasId IS NULL 752 AND ip.nasId IS NULL
751 ) AS ipAvailable, 753 ) AS ipAvailable,
752 ( 754 (
@@ -756,8 +758,8 @@ BEGIN
756 ON nb2.id = ip.netblockId 758 ON nb2.id = ip.netblockId
757 WHERE nb2.regionId = region.id 759 WHERE nb2.regionId = region.id
758 AND nb2.poolId = pool.id 760 AND nb2.poolId = pool.id
759 AND nb2.assignable IS FALSE 761 AND nb2.busyout IS TRUE
760 ) AS ipUnassignable, 762 ) AS ipBusyout,
761 ( 763 (
762 SELECT COUNT( address ) 764 SELECT COUNT( address )
763 FROM ip 765 FROM ip
@@ -784,21 +786,23 @@ BEGIN
784 ( 786 (
785 SELECT COUNT( address ) 787 SELECT COUNT( address )
786 FROM ip 788 FROM ip
787 INNER JOIN netblock AS nb2 789 INNER JOIN netblock AS nb2
788 ON nb2.id = ip.netblockId 790 ON nb2.id = ip.netblockId
789 WHERE nb2.regionId IS NULL 791 WHERE nb2.regionId IS NULL
790 AND nb2.poolId = pool.id 792 AND nb2.poolId = pool.id
791 AND ip.nasId IS NOT NULL 793 AND nb2.busyout IS FALSE
792 ) AS ipAssigned, 794 AND ip.nasId IS NOT NULL
795 ) AS ipAssignFree,
793 ( 796 (
794 SELECT COUNT( address ) 797 SELECT COUNT( address )
795 FROM ip 798 FROM ip
796 INNER JOIN netblock AS nb2 799 INNER JOIN netblock AS nb2
797 ON nb2.id = ip.netblockId 800 ON nb2.id = ip.netblockId
798 WHERE nb2.regionId IS NULL 801 WHERE nb2.regionId IS NULL
799 AND nb2.poolId = pool.id 802 AND nb2.poolId = pool.id
800 AND ip.nasId IS NULL 803 AND nb2.busyout IS TRUE
801 ) AS ipUnassigned, 804 AND ip.nasId IS NOT NULL
805 ) AS ipAssignFree,
802 ( 806 (
803 SELECT COUNT( address ) 807 SELECT COUNT( address )
804 FROM ip 808 FROM ip
@@ -806,7 +810,7 @@ BEGIN
806 ON nb2.id = ip.netblockId 810 ON nb2.id = ip.netblockId
807 WHERE nb2.regionId IS NULL 811 WHERE nb2.regionId IS NULL
808 AND nb2.poolId = pool.id 812 AND nb2.poolId = pool.id
809 AND nb2.assignable IS TRUE 813 AND nb2.busyout IS FALSE
810 AND ip.nasId IS NULL 814 AND ip.nasId IS NULL
811 ) AS ipAvailable, 815 ) AS ipAvailable,
812 ( 816 (
@@ -816,8 +820,8 @@ BEGIN
816 ON nb2.id = ip.netblockId 820 ON nb2.id = ip.netblockId
817 WHERE nb2.regionId IS NULL 821 WHERE nb2.regionId IS NULL
818 AND nb2.poolId = pool.id 822 AND nb2.poolId = pool.id
819 AND nb2.assignable IS FALSE 823 AND nb2.busyout IS TRUE
820 ) AS ipUnassignable, 824 ) AS ipBusyout,
821 ( 825 (
822 SELECT COUNT( address ) 826 SELECT COUNT( address )
823 FROM ip 827 FROM ip
diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index 00288b0..d64dfd5 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -145,8 +145,8 @@ CREATE INDEX
145 * regionId 145 * regionId
146 * : reference to the physical region served by the block 146 * : reference to the physical region served by the block
147 * 147 *
148 * active 148 * busyout
149 * : assignable to customer if true, disabled/inactive if false 149 * : auto assignable to customer if false, disabled/inactive if false
150 * 150 *
151 * description 151 * description
152 * : human readable description of network block 152 * : human readable description of network block
@@ -157,7 +157,7 @@ CREATE TABLE IF NOT EXISTS netblock
157 network CIDR NOT NULL UNIQUE, 157 network CIDR NOT NULL UNIQUE,
158 poolId INTEGER NOT NULL, 158 poolId INTEGER NOT NULL,
159 regionId INTEGER, 159 regionId INTEGER,
160 assignable BOOLEAN NOT NULL DEFAULT TRUE, 160 busyout BOOLEAN NOT NULL DEFAULT TRUE,
161 description VARCHAR(256), 161 description VARCHAR(256),
162 PRIMARY KEY ( id ), 162 PRIMARY KEY ( id ),
163 FOREIGN KEY ( poolId ) 163 FOREIGN KEY ( poolId )
@@ -173,7 +173,7 @@ CREATE INDEX
173 ( id, 173 ( id,
174 poolId, 174 poolId,
175 network, 175 network,
176 assignable 176 busyout
177 ); 177 );
178 178
179 179
@@ -299,7 +299,7 @@ CREATE OR REPLACE FUNCTION ip_before_update
299 LANGUAGE plpgsql 299 LANGUAGE plpgsql
300 AS $$ 300 AS $$
301DECLARE 301DECLARE
302 ipAssignable BOOLEAN; 302 ipBusyout BOOLEAN;
303BEGIN 303BEGIN
304 304
305 -- prevents IP address from being modified 305 -- prevents IP address from being modified
@@ -309,20 +309,20 @@ BEGIN
309 309
310 -- verifies netblock contains IP address 310 -- verifies netblock contains IP address
311 IF ( NEW.netblockId != NEW.netblockId ) THEN 311 IF ( NEW.netblockId != NEW.netblockId ) THEN
312 SELECT assignable INTO ipAssignable 312 SELECT busyout INTO ipBusyout
313 FROM netblock 313 FROM netblock
314 WHERE id = NEW.netblockId 314 WHERE id = NEW.netblockId
315 AND network >>= NEW.address; 315 AND network >>= NEW.address;
316 IF ipAssignable IS NULL THEN 316 IF ipBusyout IS NULL THEN
317 RAISE EXCEPTION 'invalid netblock'; 317 RAISE EXCEPTION 'invalid netblock';
318 END IF; 318 END IF;
319 IF ipAssignable IS TRUE THEN 319 IF ipBusyout IS FALSE THEN
320 PERFORM id 320 PERFORM id
321 FROM netblock 321 FROM netblock
322 WHERE assignable IS TRUE 322 WHERE busyout IS TRUE
323 AND network >>= NEW.address; 323 AND network >>= NEW.address;
324 IF FOUND THEN 324 IF FOUND THEN
325 RAISE EXCEPTION 'cannot associate assignable netblock if an unassignable netblock contains IP address'; 325 RAISE EXCEPTION 'cannot associate not busy netblock to a busied out netblock contains IP address';
326 END IF; 326 END IF;
327 END IF; 327 END IF;
328 END IF; 328 END IF;
@@ -330,8 +330,6 @@ BEGIN
330 -- update timestamp 330 -- update timestamp
331 IF ( ( OLD.nasId != NEW.nasId ) OR ( OLD.username != NEW.username ) ) THEN 331 IF ( ( OLD.nasId != NEW.nasId ) OR ( OLD.username != NEW.username ) ) THEN
332 NEW.modified := current_timestamp; 332 NEW.modified := current_timestamp;
333 ELSE
334 NEW.modified := OLD.modified;
335 END IF; 333 END IF;
336 334
337 RETURN NEW; 335 RETURN NEW;
@@ -409,17 +407,6 @@ BEGIN
409 RAISE EXCEPTION 'changing network will orphan existing IP addresses'; 407 RAISE EXCEPTION 'changing network will orphan existing IP addresses';
410 END IF; 408 END IF;
411 409
412 -- updates IP Association of netblock if an update and is now assignable
413 IF ( ( OLD.assignable IS FALSE ) AND ( NEW.assignable IS TRUE ) ) THEN
414 UPDATE ip
415 SET netblockId = nb.id
416 FROM ip
417 INNER JOIN netblock AS nb
418 ON nb.network >>= ip.address
419 WHERE ip.address <<= NEW.network
420 AND nb.assignable IS TRUE;
421 END IF;
422
423 RETURN NEW; 410 RETURN NEW;
424END; 411END;
425$$; 412$$;
@@ -432,8 +419,20 @@ CREATE OR REPLACE FUNCTION netblock_after_insert_or_update
432 AS $$ 419 AS $$
433DECLARE 420DECLARE
434BEGIN 421BEGIN
422
423 -- updates IP Association of netblock if an update and is now assignable
424 IF ( ( OLD.busyout IS TRUE ) AND ( NEW.busyout IS FALSE ) ) THEN
425 UPDATE ip
426 SET netblockId = nb.id
427 FROM ip
428 INNER JOIN netblock AS nb
429 ON nb.network >>= ip.address
430 WHERE ip.address <<= NEW.network
431 AND nb.busyout IS TRUE;
432 END IF;
433
435 -- updates IP association of netblock is not assignable 434 -- updates IP association of netblock is not assignable
436 IF ( NEW.assignable IS FALSE ) THEN 435 IF ( NEW.busyout IS TRUE ) THEN
437 UPDATE ip 436 UPDATE ip
438 SET netblockId = NEW.id 437 SET netblockId = NEW.id
439 WHERE address <<= NEW.network; 438 WHERE address <<= NEW.network;
diff --git a/notes/projects/radius/sql/untitled.sql b/notes/projects/radius/sql/untitled.sql
new file mode 100644
index 0000000..beacd77
--- /dev/null
+++ b/notes/projects/radius/sql/untitled.sql
@@ -0,0 +1,129 @@
1
2
3SELECT * FROM region_audit() ORDER BY poolName, regionName;
4SELECT * FROM netblock_audit() ORDER BY poolName, network;
5SELECT * FROM ip_audit() ORDER BY ipAddress;
6
7
8CREATE TABLE IF NOT EXISTS netblock
9(
10 id SERIAL,
11 network CIDR NOT NULL UNIQUE,
12 poolId INTEGER NOT NULL,
13 regionId INTEGER,
14 active BOOLEAN NOT NULL DEFAULT TRUE,
15 description VARCHAR(32),
16 PRIMARY KEY ( id ),
17 FOREIGN KEY ( poolId )
18 REFERENCES pool
19 ( id ),
20 FOREIGN KEY ( regionId )
21 REFERENCES region
22 ( id )
23);
24
25
26CREATE TABLE IF NOT EXISTS ip
27(
28 address INET,
29 modified timestamp WITH TIME ZONE NOT NULL
30 DEFAULT TIMESTAMP 'epoch',
31 username VARCHAR(64),
32 acctSession VARCHAR(32),
33 nasId INTEGER,
34 PRIMARY KEY ( address ),
35 FOREIGN KEY ( nasId )
36 REFERENCES nas
37 ( id )
38);
39
40
41\c syzdek
42set role wheel;
43drop database radius_ippool;
44
45
46psql -h raddbtest.prv.acsalaska.net -f radius-roles.sql
47psql -h raddbtest.prv.acsalaska.net -f ippool-database.sql
48psql -h raddbtest.prv.acsalaska.net -f ippool-schema.sql \
49 -f ippool-functions.sql radius_ippool
50psql -h raddbtest.prv.acsalaska.net -f ippool-demo.sql radius_ippool
51
52
53SELECT ip.address AS address,
54 region.name AS region,
55 pool.name AS pool,
56 ip.modified AS modified,
57 ip.username AS username,
58 ip.acctsession AS acctsession
59 FROM ip
60 INNER JOIN netblock
61 ON netblock.network >>= ip.address
62 INNER JOIN region
63 ON region.id = netblock.regionId
64 INNER JOIN pool
65 ON pool.id = netblock.poolId
66 INNER JOIN nas
67 ON nas.regionId = region.id
68 WHERE ip.nasId IS NULL
69 AND nas.name LIKE lower( 'br1.sol' )
70 AND pool.name LIKE lower( 'DHCP' )
71 AND netblock.active = TRUE
72 ORDER BY ip.modified ASC
73 LIMIT 1;
74
75
76UPDATE ip
77 SET nasId = nas.id,
78 username = 'test1'
79 FROM ip
80 INNER JOIN netblock
81 ON netblock.network >>= ip.address
82 INNER JOIN region
83 ON region.id = netblock.regionId
84 INNER JOIN pool
85 ON pool.id = netblock.poolId
86 INNER JOIN nas
87 ON nas.regionId = region.id
88 WHERE ip.nasId IS NULL
89 AND nas.name LIKE 'br1.sol'
90 AND pool.name LIKE lower( 'DHCP' )
91 AND netblock.active = TRUE
92 ORDER BY ip.modified ASC
93 LIMIT 1;
94
95DECLARE
96 blah := 'test1';
97BEGIN
98 SELECT *, blah, FROM region;
99END;
100
101
102
103
104INSERT INTO foo
105 ( attr1,
106 attr2,
107 attr3
108 )
109 VALUES ( 'val1',
110 'val2',
111 'val3'
112 );
113
114
115DELETE FROM foo
116 WHERE attr1 like 'val1';
117
118
119UPDATE foo
120 SET attr1 = 'valI',
121 attr2 = 'valII',
122 attr3 = 'valIII'
123 WHERE attr1 like 'val1';
124
125
126
127
128
129