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-- check that spgist index works correctly 97CREATE INDEX inet_idx3 ON inet_tbl using spgist (i); 98SET enable_seqscan TO off; 99SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i; 100SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i; 101SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i; 102SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i; 103SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i; 104SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i; 105SELECT * FROM inet_tbl WHERE i <= '192.168.1.0/24'::cidr ORDER BY i; 106SELECT * FROM inet_tbl WHERE i = '192.168.1.0/24'::cidr ORDER BY i; 107SELECT * FROM inet_tbl WHERE i >= '192.168.1.0/24'::cidr ORDER BY i; 108SELECT * FROM inet_tbl WHERE i > '192.168.1.0/24'::cidr ORDER BY i; 109SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i; 110 111-- test index-only scans 112EXPLAIN (COSTS OFF) 113SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i; 114SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i; 115 116SET enable_seqscan TO on; 117DROP INDEX inet_idx3; 118 119-- simple tests of inet boolean and arithmetic operators 120SELECT i, ~i AS "~i" FROM inet_tbl; 121SELECT i, c, i & c AS "and" FROM inet_tbl; 122SELECT i, c, i | c AS "or" FROM inet_tbl; 123SELECT i, i + 500 AS "i+500" FROM inet_tbl; 124SELECT i, i - 500 AS "i-500" FROM inet_tbl; 125SELECT i, c, i - c AS "minus" FROM inet_tbl; 126SELECT '127.0.0.1'::inet + 257; 127SELECT ('127.0.0.1'::inet + 257) - 257; 128SELECT '127::1'::inet + 257; 129SELECT ('127::1'::inet + 257) - 257; 130SELECT '127.0.0.2'::inet - ('127.0.0.2'::inet + 500); 131SELECT '127.0.0.2'::inet - ('127.0.0.2'::inet - 500); 132SELECT '127::2'::inet - ('127::2'::inet + 500); 133SELECT '127::2'::inet - ('127::2'::inet - 500); 134-- these should give overflow errors: 135SELECT '127.0.0.1'::inet + 10000000000; 136SELECT '127.0.0.1'::inet - 10000000000; 137SELECT '126::1'::inet - '127::2'::inet; 138SELECT '127::1'::inet - '126::2'::inet; 139-- but not these 140SELECT '127::1'::inet + 10000000000; 141SELECT '127::1'::inet - '127::2'::inet; 142 143-- insert one more row with addressed from different families 144INSERT INTO INET_TBL (c, i) VALUES ('10', '10::/8'); 145-- now, this one should fail 146SELECT inet_merge(c, i) FROM INET_TBL; 147-- fix it by inet_same_family() condition 148SELECT inet_merge(c, i) FROM INET_TBL WHERE inet_same_family(c, i); 149