1-- 2-- INET 3-- 4 5-- prepare the table... 6 7DROP TABLE INET_TBL; 8CREATE TABLE INET_TBL (c cidr, i inet); 9INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.226/24'); 10INSERT INTO INET_TBL (c, i) VALUES ('192.168.1.0/26', '192.168.1.226'); 11INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.0/24'); 12INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.0/25'); 13INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.255/24'); 14INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.255/25'); 15INSERT INTO INET_TBL (c, i) VALUES ('10', '10.1.2.3/8'); 16INSERT INTO INET_TBL (c, i) VALUES ('10.0.0.0', '10.1.2.3/8'); 17INSERT INTO INET_TBL (c, i) VALUES ('10.1.2.3', '10.1.2.3/32'); 18INSERT INTO INET_TBL (c, i) VALUES ('10.1.2', '10.1.2.3/24'); 19INSERT INTO INET_TBL (c, i) VALUES ('10.1', '10.1.2.3/16'); 20INSERT INTO INET_TBL (c, i) VALUES ('10', '10.1.2.3/8'); 21INSERT INTO INET_TBL (c, i) VALUES ('10', '11.1.2.3/8'); 22INSERT INTO INET_TBL (c, i) VALUES ('10', '9.1.2.3/8'); 23INSERT INTO INET_TBL (c, i) VALUES ('10:23::f1', '10:23::f1/64'); 24INSERT INTO INET_TBL (c, i) VALUES ('10:23::8000/113', '10:23::ffff'); 25INSERT INTO INET_TBL (c, i) VALUES ('::ffff:1.2.3.4', '::4.3.2.1/24'); 26-- check that CIDR rejects invalid input: 27INSERT INTO INET_TBL (c, i) VALUES ('192.168.1.2/30', '192.168.1.226'); 28INSERT INTO INET_TBL (c, i) VALUES ('1234::1234::1234', '::1.2.3.4'); 29-- check that CIDR rejects invalid input when converting from text: 30INSERT INTO INET_TBL (c, i) VALUES (cidr('192.168.1.2/30'), '192.168.1.226'); 31INSERT INTO INET_TBL (c, i) VALUES (cidr('ffff:ffff:ffff:ffff::/24'), '::192.168.1.226'); 32SELECT '' AS ten, c AS cidr, i AS inet FROM INET_TBL; 33 34-- now test some support functions 35 36SELECT '' AS ten, i AS inet, host(i), text(i), family(i) FROM INET_TBL; 37SELECT '' AS ten, c AS cidr, broadcast(c), 38 i AS inet, broadcast(i) FROM INET_TBL; 39SELECT '' AS ten, c AS cidr, network(c) AS "network(cidr)", 40 i AS inet, network(i) AS "network(inet)" FROM INET_TBL; 41SELECT '' AS ten, c AS cidr, masklen(c) AS "masklen(cidr)", 42 i AS inet, masklen(i) AS "masklen(inet)" FROM INET_TBL; 43 44SELECT '' AS four, c AS cidr, masklen(c) AS "masklen(cidr)", 45 i AS inet, masklen(i) AS "masklen(inet)" FROM INET_TBL 46 WHERE masklen(c) <= 8; 47 48SELECT '' AS six, c AS cidr, i AS inet FROM INET_TBL 49 WHERE c = i; 50 51SELECT '' AS ten, i, c, 52 i < c AS lt, i <= c AS le, i = c AS eq, 53 i >= c AS ge, i > c AS gt, i <> c AS ne, 54 i << c AS sb, i <<= c AS sbe, 55 i >> c AS sup, i >>= c AS spe, 56 i && c AS ovr 57 FROM INET_TBL; 58 59SELECT max(i) AS max, min(i) AS min FROM INET_TBL; 60SELECT max(c) AS max, min(c) AS min FROM INET_TBL; 61 62-- check the conversion to/from text and set_netmask 63SELECT '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL; 64 65-- check that btree index works correctly 66CREATE INDEX inet_idx1 ON inet_tbl(i); 67SET enable_seqscan TO off; 68SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr; 69SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr; 70SET enable_seqscan TO on; 71DROP INDEX inet_idx1; 72 73-- check that gist index works correctly 74CREATE INDEX inet_idx2 ON inet_tbl using gist (i inet_ops); 75SET enable_seqscan TO off; 76SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i; 77SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i; 78SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i; 79SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i; 80SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i; 81SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i; 82SELECT * FROM inet_tbl WHERE i <= '192.168.1.0/24'::cidr ORDER BY i; 83SELECT * FROM inet_tbl WHERE i = '192.168.1.0/24'::cidr ORDER BY i; 84SELECT * FROM inet_tbl WHERE i >= '192.168.1.0/24'::cidr ORDER BY i; 85SELECT * FROM inet_tbl WHERE i > '192.168.1.0/24'::cidr ORDER BY i; 86SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i; 87 88-- test index-only scans 89EXPLAIN (COSTS OFF) 90SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i; 91SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i; 92 93SET enable_seqscan TO on; 94DROP INDEX inet_idx2; 95 96-- simple tests of inet boolean and arithmetic operators 97SELECT i, ~i AS "~i" FROM inet_tbl; 98SELECT i, c, i & c AS "and" FROM inet_tbl; 99SELECT i, c, i | c AS "or" FROM inet_tbl; 100SELECT i, i + 500 AS "i+500" FROM inet_tbl; 101SELECT i, i - 500 AS "i-500" FROM inet_tbl; 102SELECT i, c, i - c AS "minus" FROM inet_tbl; 103SELECT '127.0.0.1'::inet + 257; 104SELECT ('127.0.0.1'::inet + 257) - 257; 105SELECT '127::1'::inet + 257; 106SELECT ('127::1'::inet + 257) - 257; 107SELECT '127.0.0.2'::inet - ('127.0.0.2'::inet + 500); 108SELECT '127.0.0.2'::inet - ('127.0.0.2'::inet - 500); 109SELECT '127::2'::inet - ('127::2'::inet + 500); 110SELECT '127::2'::inet - ('127::2'::inet - 500); 111-- these should give overflow errors: 112SELECT '127.0.0.1'::inet + 10000000000; 113SELECT '127.0.0.1'::inet - 10000000000; 114SELECT '126::1'::inet - '127::2'::inet; 115SELECT '127::1'::inet - '126::2'::inet; 116-- but not these 117SELECT '127::1'::inet + 10000000000; 118SELECT '127::1'::inet - '127::2'::inet; 119 120-- insert one more row with addressed from different families 121INSERT INTO INET_TBL (c, i) VALUES ('10', '10::/8'); 122-- now, this one should fail 123SELECT inet_merge(c, i) FROM INET_TBL; 124-- fix it by inet_same_family() condition 125SELECT inet_merge(c, i) FROM INET_TBL WHERE inet_same_family(c, i); 126