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 VIEW addr_nsp.genview AS SELECT * from addr_nsp.gentable; 26CREATE MATERIALIZED VIEW addr_nsp.genmatview AS SELECT * FROM addr_nsp.gentable; 27CREATE TYPE addr_nsp.gencomptype AS (a int); 28CREATE TYPE addr_nsp.genenum AS ENUM ('one', 'two'); 29CREATE FOREIGN TABLE addr_nsp.genftable (a int) SERVER addr_fserv; 30CREATE AGGREGATE addr_nsp.genaggr(int4) (sfunc = int4pl, stype = int4); 31CREATE DOMAIN addr_nsp.gendomain AS int4 CONSTRAINT domconstr CHECK (value > 0); 32CREATE FUNCTION addr_nsp.trig() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN END; $$; 33CREATE TRIGGER t BEFORE INSERT ON addr_nsp.gentable FOR EACH ROW EXECUTE PROCEDURE addr_nsp.trig(); 34CREATE POLICY genpol ON addr_nsp.gentable; 35CREATE SERVER "integer" FOREIGN DATA WRAPPER addr_fdw; 36CREATE USER MAPPING FOR regress_addr_user SERVER "integer"; 37ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user IN SCHEMA public GRANT ALL ON TABLES TO regress_addr_user; 38ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user REVOKE DELETE ON TABLES FROM regress_addr_user; 39-- this transform would be quite unsafe to leave lying around, 40-- except that the SQL language pays no attention to transforms: 41CREATE TRANSFORM FOR int LANGUAGE SQL ( 42 FROM SQL WITH FUNCTION prsd_lextype(internal), 43 TO SQL WITH FUNCTION int4recv(internal)); 44 45-- test some error cases 46SELECT pg_get_object_address('stone', '{}', '{}'); 47SELECT pg_get_object_address('table', '{}', '{}'); 48SELECT pg_get_object_address('table', '{NULL}', '{}'); 49 50-- unrecognized object types 51DO $$ 52DECLARE 53 objtype text; 54BEGIN 55 FOR objtype IN VALUES ('toast table'), ('index column'), ('sequence column'), 56 ('toast table column'), ('view column'), ('materialized view column') 57 LOOP 58 BEGIN 59 PERFORM pg_get_object_address(objtype, '{one}', '{}'); 60 EXCEPTION WHEN invalid_parameter_value THEN 61 RAISE WARNING 'error for %: %', objtype, sqlerrm; 62 END; 63 END LOOP; 64END; 65$$; 66 67-- miscellaneous other errors 68select * from pg_get_object_address('operator of access method', '{btree,integer_ops,1}', '{int4,bool}'); 69select * from pg_get_object_address('operator of access method', '{btree,integer_ops,99}', '{int4,int4}'); 70select * from pg_get_object_address('function of access method', '{btree,integer_ops,1}', '{int4,bool}'); 71select * from pg_get_object_address('function of access method', '{btree,integer_ops,99}', '{int4,int4}'); 72 73DO $$ 74DECLARE 75 objtype text; 76 names text[]; 77 args text[]; 78BEGIN 79 FOR objtype IN VALUES 80 ('table'), ('index'), ('sequence'), ('view'), 81 ('materialized view'), ('foreign table'), 82 ('table column'), ('foreign table column'), 83 ('aggregate'), ('function'), ('type'), ('cast'), 84 ('table constraint'), ('domain constraint'), ('conversion'), ('default value'), 85 ('operator'), ('operator class'), ('operator family'), ('rule'), ('trigger'), 86 ('text search parser'), ('text search dictionary'), 87 ('text search template'), ('text search configuration'), 88 ('policy'), ('user mapping'), ('default acl'), ('transform'), 89 ('operator of access method'), ('function of access method') 90 LOOP 91 FOR names IN VALUES ('{eins}'), ('{addr_nsp, zwei}'), ('{eins, zwei, drei}') 92 LOOP 93 FOR args IN VALUES ('{}'), ('{integer}') 94 LOOP 95 BEGIN 96 PERFORM pg_get_object_address(objtype, names, args); 97 EXCEPTION WHEN OTHERS THEN 98 RAISE WARNING 'error for %,%,%: %', objtype, names, args, sqlerrm; 99 END; 100 END LOOP; 101 END LOOP; 102 END LOOP; 103END; 104$$; 105 106-- these object types cannot be qualified names 107SELECT pg_get_object_address('language', '{one}', '{}'); 108SELECT pg_get_object_address('language', '{one,two}', '{}'); 109SELECT pg_get_object_address('large object', '{123}', '{}'); 110SELECT pg_get_object_address('large object', '{123,456}', '{}'); 111SELECT pg_get_object_address('large object', '{blargh}', '{}'); 112SELECT pg_get_object_address('schema', '{one}', '{}'); 113SELECT pg_get_object_address('schema', '{one,two}', '{}'); 114SELECT pg_get_object_address('role', '{one}', '{}'); 115SELECT pg_get_object_address('role', '{one,two}', '{}'); 116SELECT pg_get_object_address('database', '{one}', '{}'); 117SELECT pg_get_object_address('database', '{one,two}', '{}'); 118SELECT pg_get_object_address('tablespace', '{one}', '{}'); 119SELECT pg_get_object_address('tablespace', '{one,two}', '{}'); 120SELECT pg_get_object_address('foreign-data wrapper', '{one}', '{}'); 121SELECT pg_get_object_address('foreign-data wrapper', '{one,two}', '{}'); 122SELECT pg_get_object_address('server', '{one}', '{}'); 123SELECT pg_get_object_address('server', '{one,two}', '{}'); 124SELECT pg_get_object_address('extension', '{one}', '{}'); 125SELECT pg_get_object_address('extension', '{one,two}', '{}'); 126SELECT pg_get_object_address('event trigger', '{one}', '{}'); 127SELECT pg_get_object_address('event trigger', '{one,two}', '{}'); 128SELECT pg_get_object_address('access method', '{one}', '{}'); 129SELECT pg_get_object_address('access method', '{one,two}', '{}'); 130 131-- test successful cases 132WITH objects (type, name, args) AS (VALUES 133 ('table', '{addr_nsp, gentable}'::text[], '{}'::text[]), 134 ('index', '{addr_nsp, gentable_pkey}', '{}'), 135 ('sequence', '{addr_nsp, gentable_a_seq}', '{}'), 136 -- toast table 137 ('view', '{addr_nsp, genview}', '{}'), 138 ('materialized view', '{addr_nsp, genmatview}', '{}'), 139 ('foreign table', '{addr_nsp, genftable}', '{}'), 140 ('table column', '{addr_nsp, gentable, b}', '{}'), 141 ('foreign table column', '{addr_nsp, genftable, a}', '{}'), 142 ('aggregate', '{addr_nsp, genaggr}', '{int4}'), 143 ('function', '{pg_catalog, pg_identify_object}', '{pg_catalog.oid, pg_catalog.oid, int4}'), 144 ('type', '{pg_catalog._int4}', '{}'), 145 ('type', '{addr_nsp.gendomain}', '{}'), 146 ('type', '{addr_nsp.gencomptype}', '{}'), 147 ('type', '{addr_nsp.genenum}', '{}'), 148 ('cast', '{int8}', '{int4}'), 149 ('collation', '{default}', '{}'), 150 ('table constraint', '{addr_nsp, gentable, a_chk}', '{}'), 151 ('domain constraint', '{addr_nsp.gendomain}', '{domconstr}'), 152 ('conversion', '{pg_catalog, ascii_to_mic}', '{}'), 153 ('default value', '{addr_nsp, gentable, b}', '{}'), 154 ('language', '{plpgsql}', '{}'), 155 -- large object 156 ('operator', '{+}', '{int4, int4}'), 157 ('operator class', '{btree, int4_ops}', '{}'), 158 ('operator family', '{btree, integer_ops}', '{}'), 159 ('operator of access method', '{btree,integer_ops,1}', '{integer,integer}'), 160 ('function of access method', '{btree,integer_ops,2}', '{integer,integer}'), 161 ('rule', '{addr_nsp, genview, _RETURN}', '{}'), 162 ('trigger', '{addr_nsp, gentable, t}', '{}'), 163 ('schema', '{addr_nsp}', '{}'), 164 ('text search parser', '{addr_ts_prs}', '{}'), 165 ('text search dictionary', '{addr_ts_dict}', '{}'), 166 ('text search template', '{addr_ts_temp}', '{}'), 167 ('text search configuration', '{addr_ts_conf}', '{}'), 168 ('role', '{regress_addr_user}', '{}'), 169 -- database 170 -- tablespace 171 ('foreign-data wrapper', '{addr_fdw}', '{}'), 172 ('server', '{addr_fserv}', '{}'), 173 ('user mapping', '{regress_addr_user}', '{integer}'), 174 ('default acl', '{regress_addr_user,public}', '{r}'), 175 ('default acl', '{regress_addr_user}', '{r}'), 176 -- extension 177 -- event trigger 178 ('policy', '{addr_nsp, gentable, genpol}', '{}'), 179 ('transform', '{int}', '{sql}'), 180 ('access method', '{btree}', '{}') 181 ) 182SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.subobjid)).*, 183 -- test roundtrip through pg_identify_object_as_address 184 ROW(pg_identify_object(addr1.classid, addr1.objid, addr1.subobjid)) = 185 ROW(pg_identify_object(addr2.classid, addr2.objid, addr2.subobjid)) 186 FROM objects, pg_get_object_address(type, name, args) addr1, 187 pg_identify_object_as_address(classid, objid, subobjid) ioa(typ,nms,args), 188 pg_get_object_address(typ, nms, ioa.args) as addr2 189 ORDER BY addr1.classid, addr1.objid, addr1.subobjid; 190 191--- 192--- Cleanup resources 193--- 194SET client_min_messages TO 'warning'; 195 196DROP FOREIGN DATA WRAPPER addr_fdw CASCADE; 197 198DROP SCHEMA addr_nsp CASCADE; 199 200DROP OWNED BY regress_addr_user; 201DROP USER regress_addr_user; 202