1-- 2-- Test for pg_get_object_address 3-- 4 5-- Clean up in case a prior regression run failed 6SET client_min_messages TO 'warning'; 7DROP ROLE IF EXISTS regress_addr_user; 8RESET client_min_messages; 9 10CREATE USER regress_addr_user; 11 12-- Test generic object addressing/identification functions 13CREATE SCHEMA addr_nsp; 14SET search_path TO 'addr_nsp'; 15CREATE FOREIGN DATA WRAPPER addr_fdw; 16CREATE SERVER addr_fserv FOREIGN DATA WRAPPER addr_fdw; 17CREATE TEXT SEARCH DICTIONARY addr_ts_dict (template=simple); 18CREATE TEXT SEARCH CONFIGURATION addr_ts_conf (copy=english); 19CREATE TEXT SEARCH TEMPLATE addr_ts_temp (lexize=dsimple_lexize); 20CREATE TEXT SEARCH PARSER addr_ts_prs 21 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); 22CREATE TABLE addr_nsp.gentable ( 23 a serial primary key CONSTRAINT a_chk CHECK (a > 0), 24 b text DEFAULT 'hello'); 25CREATE TABLE addr_nsp.parttable ( 26 a int PRIMARY KEY 27) PARTITION BY RANGE (a); 28CREATE VIEW addr_nsp.genview AS SELECT * from addr_nsp.gentable; 29CREATE MATERIALIZED VIEW addr_nsp.genmatview AS SELECT * FROM addr_nsp.gentable; 30CREATE TYPE addr_nsp.gencomptype AS (a int); 31CREATE TYPE addr_nsp.genenum AS ENUM ('one', 'two'); 32CREATE FOREIGN TABLE addr_nsp.genftable (a int) SERVER addr_fserv; 33CREATE AGGREGATE addr_nsp.genaggr(int4) (sfunc = int4pl, stype = int4); 34CREATE DOMAIN addr_nsp.gendomain AS int4 CONSTRAINT domconstr CHECK (value > 0); 35CREATE FUNCTION addr_nsp.trig() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN END; $$; 36CREATE TRIGGER t BEFORE INSERT ON addr_nsp.gentable FOR EACH ROW EXECUTE PROCEDURE addr_nsp.trig(); 37CREATE POLICY genpol ON addr_nsp.gentable; 38CREATE PROCEDURE addr_nsp.proc(int4) LANGUAGE SQL AS $$ $$; 39CREATE SERVER "integer" FOREIGN DATA WRAPPER addr_fdw; 40CREATE USER MAPPING FOR regress_addr_user SERVER "integer"; 41ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user IN SCHEMA public GRANT ALL ON TABLES TO regress_addr_user; 42ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user REVOKE DELETE ON TABLES FROM regress_addr_user; 43-- this transform would be quite unsafe to leave lying around, 44-- except that the SQL language pays no attention to transforms: 45CREATE TRANSFORM FOR int LANGUAGE SQL ( 46 FROM SQL WITH FUNCTION prsd_lextype(internal), 47 TO SQL WITH FUNCTION int4recv(internal)); 48CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable; 49CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE); 50CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable; 51 52-- test some error cases 53SELECT pg_get_object_address('stone', '{}', '{}'); 54SELECT pg_get_object_address('table', '{}', '{}'); 55SELECT pg_get_object_address('table', '{NULL}', '{}'); 56 57-- unrecognized object types 58DO $$ 59DECLARE 60 objtype text; 61BEGIN 62 FOR objtype IN VALUES ('toast table'), ('index column'), ('sequence column'), 63 ('toast table column'), ('view column'), ('materialized view column') 64 LOOP 65 BEGIN 66 PERFORM pg_get_object_address(objtype, '{one}', '{}'); 67 EXCEPTION WHEN invalid_parameter_value THEN 68 RAISE WARNING 'error for %: %', objtype, sqlerrm; 69 END; 70 END LOOP; 71END; 72$$; 73 74-- miscellaneous other errors 75select * from pg_get_object_address('operator of access method', '{btree,integer_ops,1}', '{int4,bool}'); 76select * from pg_get_object_address('operator of access method', '{btree,integer_ops,99}', '{int4,int4}'); 77select * from pg_get_object_address('function of access method', '{btree,integer_ops,1}', '{int4,bool}'); 78select * from pg_get_object_address('function of access method', '{btree,integer_ops,99}', '{int4,int4}'); 79 80DO $$ 81DECLARE 82 objtype text; 83 names text[]; 84 args text[]; 85BEGIN 86 FOR objtype IN VALUES 87 ('table'), ('index'), ('sequence'), ('view'), 88 ('materialized view'), ('foreign table'), 89 ('table column'), ('foreign table column'), 90 ('aggregate'), ('function'), ('procedure'), ('type'), ('cast'), 91 ('table constraint'), ('domain constraint'), ('conversion'), ('default value'), 92 ('operator'), ('operator class'), ('operator family'), ('rule'), ('trigger'), 93 ('text search parser'), ('text search dictionary'), 94 ('text search template'), ('text search configuration'), 95 ('policy'), ('user mapping'), ('default acl'), ('transform'), 96 ('operator of access method'), ('function of access method'), 97 ('publication relation') 98 LOOP 99 FOR names IN VALUES ('{eins}'), ('{addr_nsp, zwei}'), ('{eins, zwei, drei}') 100 LOOP 101 FOR args IN VALUES ('{}'), ('{integer}') 102 LOOP 103 BEGIN 104 PERFORM pg_get_object_address(objtype, names, args); 105 EXCEPTION WHEN OTHERS THEN 106 RAISE WARNING 'error for %,%,%: %', objtype, names, args, sqlerrm; 107 END; 108 END LOOP; 109 END LOOP; 110 END LOOP; 111END; 112$$; 113 114-- these object types cannot be qualified names 115SELECT pg_get_object_address('language', '{one}', '{}'); 116SELECT pg_get_object_address('language', '{one,two}', '{}'); 117SELECT pg_get_object_address('large object', '{123}', '{}'); 118SELECT pg_get_object_address('large object', '{123,456}', '{}'); 119SELECT pg_get_object_address('large object', '{blargh}', '{}'); 120SELECT pg_get_object_address('schema', '{one}', '{}'); 121SELECT pg_get_object_address('schema', '{one,two}', '{}'); 122SELECT pg_get_object_address('role', '{one}', '{}'); 123SELECT pg_get_object_address('role', '{one,two}', '{}'); 124SELECT pg_get_object_address('database', '{one}', '{}'); 125SELECT pg_get_object_address('database', '{one,two}', '{}'); 126SELECT pg_get_object_address('tablespace', '{one}', '{}'); 127SELECT pg_get_object_address('tablespace', '{one,two}', '{}'); 128SELECT pg_get_object_address('foreign-data wrapper', '{one}', '{}'); 129SELECT pg_get_object_address('foreign-data wrapper', '{one,two}', '{}'); 130SELECT pg_get_object_address('server', '{one}', '{}'); 131SELECT pg_get_object_address('server', '{one,two}', '{}'); 132SELECT pg_get_object_address('extension', '{one}', '{}'); 133SELECT pg_get_object_address('extension', '{one,two}', '{}'); 134SELECT pg_get_object_address('event trigger', '{one}', '{}'); 135SELECT pg_get_object_address('event trigger', '{one,two}', '{}'); 136SELECT pg_get_object_address('access method', '{one}', '{}'); 137SELECT pg_get_object_address('access method', '{one,two}', '{}'); 138SELECT pg_get_object_address('publication', '{one}', '{}'); 139SELECT pg_get_object_address('publication', '{one,two}', '{}'); 140SELECT pg_get_object_address('subscription', '{one}', '{}'); 141SELECT pg_get_object_address('subscription', '{one,two}', '{}'); 142 143-- test successful cases 144WITH objects (type, name, args) AS (VALUES 145 ('table', '{addr_nsp, gentable}'::text[], '{}'::text[]), 146 ('table', '{addr_nsp, parttable}'::text[], '{}'::text[]), 147 ('index', '{addr_nsp, gentable_pkey}', '{}'), 148 ('index', '{addr_nsp, parttable_pkey}', '{}'), 149 ('sequence', '{addr_nsp, gentable_a_seq}', '{}'), 150 -- toast table 151 ('view', '{addr_nsp, genview}', '{}'), 152 ('materialized view', '{addr_nsp, genmatview}', '{}'), 153 ('foreign table', '{addr_nsp, genftable}', '{}'), 154 ('table column', '{addr_nsp, gentable, b}', '{}'), 155 ('foreign table column', '{addr_nsp, genftable, a}', '{}'), 156 ('aggregate', '{addr_nsp, genaggr}', '{int4}'), 157 ('function', '{pg_catalog, pg_identify_object}', '{pg_catalog.oid, pg_catalog.oid, int4}'), 158 ('procedure', '{addr_nsp, proc}', '{int4}'), 159 ('type', '{pg_catalog._int4}', '{}'), 160 ('type', '{addr_nsp.gendomain}', '{}'), 161 ('type', '{addr_nsp.gencomptype}', '{}'), 162 ('type', '{addr_nsp.genenum}', '{}'), 163 ('cast', '{int8}', '{int4}'), 164 ('collation', '{default}', '{}'), 165 ('table constraint', '{addr_nsp, gentable, a_chk}', '{}'), 166 ('domain constraint', '{addr_nsp.gendomain}', '{domconstr}'), 167 ('conversion', '{pg_catalog, ascii_to_mic}', '{}'), 168 ('default value', '{addr_nsp, gentable, b}', '{}'), 169 ('language', '{plpgsql}', '{}'), 170 -- large object 171 ('operator', '{+}', '{int4, int4}'), 172 ('operator class', '{btree, int4_ops}', '{}'), 173 ('operator family', '{btree, integer_ops}', '{}'), 174 ('operator of access method', '{btree,integer_ops,1}', '{integer,integer}'), 175 ('function of access method', '{btree,integer_ops,2}', '{integer,integer}'), 176 ('rule', '{addr_nsp, genview, _RETURN}', '{}'), 177 ('trigger', '{addr_nsp, gentable, t}', '{}'), 178 ('schema', '{addr_nsp}', '{}'), 179 ('text search parser', '{addr_ts_prs}', '{}'), 180 ('text search dictionary', '{addr_ts_dict}', '{}'), 181 ('text search template', '{addr_ts_temp}', '{}'), 182 ('text search configuration', '{addr_ts_conf}', '{}'), 183 ('role', '{regress_addr_user}', '{}'), 184 -- database 185 -- tablespace 186 ('foreign-data wrapper', '{addr_fdw}', '{}'), 187 ('server', '{addr_fserv}', '{}'), 188 ('user mapping', '{regress_addr_user}', '{integer}'), 189 ('default acl', '{regress_addr_user,public}', '{r}'), 190 ('default acl', '{regress_addr_user}', '{r}'), 191 -- extension 192 -- event trigger 193 ('policy', '{addr_nsp, gentable, genpol}', '{}'), 194 ('transform', '{int}', '{sql}'), 195 ('access method', '{btree}', '{}'), 196 ('publication', '{addr_pub}', '{}'), 197 ('publication relation', '{addr_nsp, gentable}', '{addr_pub}'), 198 ('subscription', '{addr_sub}', '{}'), 199 ('statistics object', '{addr_nsp, gentable_stat}', '{}') 200 ) 201SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*, 202 -- test roundtrip through pg_identify_object_as_address 203 ROW(pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)) = 204 ROW(pg_identify_object(addr2.classid, addr2.objid, addr2.objsubid)) 205 FROM objects, pg_get_object_address(type, name, args) addr1, 206 pg_identify_object_as_address(classid, objid, objsubid) ioa(typ,nms,args), 207 pg_get_object_address(typ, nms, ioa.args) as addr2 208 ORDER BY addr1.classid, addr1.objid, addr1.objsubid; 209 210--- 211--- Cleanup resources 212--- 213DROP FOREIGN DATA WRAPPER addr_fdw CASCADE; 214DROP PUBLICATION addr_pub; 215DROP SUBSCRIPTION addr_sub; 216 217DROP SCHEMA addr_nsp CASCADE; 218 219DROP OWNED BY regress_addr_user; 220DROP USER regress_addr_user; 221