1-- 2-- Test for pg_get_object_address 3-- 4-- Clean up in case a prior regression run failed 5SET client_min_messages TO 'warning'; 6DROP ROLE IF EXISTS regress_addr_user; 7RESET client_min_messages; 8CREATE USER regress_addr_user; 9-- Test generic object addressing/identification functions 10CREATE SCHEMA addr_nsp; 11SET search_path TO 'addr_nsp'; 12CREATE FOREIGN DATA WRAPPER addr_fdw; 13CREATE SERVER addr_fserv FOREIGN DATA WRAPPER addr_fdw; 14CREATE TEXT SEARCH DICTIONARY addr_ts_dict (template=simple); 15CREATE TEXT SEARCH CONFIGURATION addr_ts_conf (copy=english); 16CREATE TEXT SEARCH TEMPLATE addr_ts_temp (lexize=dsimple_lexize); 17CREATE TEXT SEARCH PARSER addr_ts_prs 18 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); 19CREATE TABLE addr_nsp.gentable ( 20 a serial primary key CONSTRAINT a_chk CHECK (a > 0), 21 b text DEFAULT 'hello'); 22CREATE VIEW addr_nsp.genview AS SELECT * from addr_nsp.gentable; 23CREATE MATERIALIZED VIEW addr_nsp.genmatview AS SELECT * FROM addr_nsp.gentable; 24CREATE TYPE addr_nsp.gencomptype AS (a int); 25CREATE TYPE addr_nsp.genenum AS ENUM ('one', 'two'); 26CREATE FOREIGN TABLE addr_nsp.genftable (a int) SERVER addr_fserv; 27CREATE AGGREGATE addr_nsp.genaggr(int4) (sfunc = int4pl, stype = int4); 28CREATE DOMAIN addr_nsp.gendomain AS int4 CONSTRAINT domconstr CHECK (value > 0); 29CREATE FUNCTION addr_nsp.trig() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN END; $$; 30CREATE TRIGGER t BEFORE INSERT ON addr_nsp.gentable FOR EACH ROW EXECUTE PROCEDURE addr_nsp.trig(); 31CREATE POLICY genpol ON addr_nsp.gentable; 32CREATE SERVER "integer" FOREIGN DATA WRAPPER addr_fdw; 33CREATE USER MAPPING FOR regress_addr_user SERVER "integer"; 34ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user IN SCHEMA public GRANT ALL ON TABLES TO regress_addr_user; 35ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user REVOKE DELETE ON TABLES FROM regress_addr_user; 36-- this transform would be quite unsafe to leave lying around, 37-- except that the SQL language pays no attention to transforms: 38CREATE TRANSFORM FOR int LANGUAGE SQL ( 39 FROM SQL WITH FUNCTION prsd_lextype(internal), 40 TO SQL WITH FUNCTION int4recv(internal)); 41CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable; 42CREATE SUBSCRIPTION addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE); 43WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables 44CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable; 45-- test some error cases 46SELECT pg_get_object_address('stone', '{}', '{}'); 47ERROR: unrecognized object type "stone" 48SELECT pg_get_object_address('table', '{}', '{}'); 49ERROR: name list length must be at least 1 50SELECT pg_get_object_address('table', '{NULL}', '{}'); 51ERROR: name or argument lists may not contain nulls 52-- unrecognized object types 53DO $$ 54DECLARE 55 objtype text; 56BEGIN 57 FOR objtype IN VALUES ('toast table'), ('index column'), ('sequence column'), 58 ('toast table column'), ('view column'), ('materialized view column') 59 LOOP 60 BEGIN 61 PERFORM pg_get_object_address(objtype, '{one}', '{}'); 62 EXCEPTION WHEN invalid_parameter_value THEN 63 RAISE WARNING 'error for %: %', objtype, sqlerrm; 64 END; 65 END LOOP; 66END; 67$$; 68WARNING: error for toast table: unsupported object type "toast table" 69WARNING: error for index column: unsupported object type "index column" 70WARNING: error for sequence column: unsupported object type "sequence column" 71WARNING: error for toast table column: unsupported object type "toast table column" 72WARNING: error for view column: unsupported object type "view column" 73WARNING: error for materialized view column: unsupported object type "materialized view column" 74-- miscellaneous other errors 75select * from pg_get_object_address('operator of access method', '{btree,integer_ops,1}', '{int4,bool}'); 76ERROR: operator 1 (int4, bool) of operator family integer_ops for access method btree does not exist 77select * from pg_get_object_address('operator of access method', '{btree,integer_ops,99}', '{int4,int4}'); 78ERROR: operator 99 (int4, int4) of operator family integer_ops for access method btree does not exist 79select * from pg_get_object_address('function of access method', '{btree,integer_ops,1}', '{int4,bool}'); 80ERROR: function 1 (int4, bool) of operator family integer_ops for access method btree does not exist 81select * from pg_get_object_address('function of access method', '{btree,integer_ops,99}', '{int4,int4}'); 82ERROR: function 99 (int4, int4) of operator family integer_ops for access method btree does not exist 83DO $$ 84DECLARE 85 objtype text; 86 names text[]; 87 args text[]; 88BEGIN 89 FOR objtype IN VALUES 90 ('table'), ('index'), ('sequence'), ('view'), 91 ('materialized view'), ('foreign table'), 92 ('table column'), ('foreign table column'), 93 ('aggregate'), ('function'), ('type'), ('cast'), 94 ('table constraint'), ('domain constraint'), ('conversion'), ('default value'), 95 ('operator'), ('operator class'), ('operator family'), ('rule'), ('trigger'), 96 ('text search parser'), ('text search dictionary'), 97 ('text search template'), ('text search configuration'), 98 ('policy'), ('user mapping'), ('default acl'), ('transform'), 99 ('operator of access method'), ('function of access method'), 100 ('publication relation') 101 LOOP 102 FOR names IN VALUES ('{eins}'), ('{addr_nsp, zwei}'), ('{eins, zwei, drei}') 103 LOOP 104 FOR args IN VALUES ('{}'), ('{integer}') 105 LOOP 106 BEGIN 107 PERFORM pg_get_object_address(objtype, names, args); 108 EXCEPTION WHEN OTHERS THEN 109 RAISE WARNING 'error for %,%,%: %', objtype, names, args, sqlerrm; 110 END; 111 END LOOP; 112 END LOOP; 113 END LOOP; 114END; 115$$; 116WARNING: error for table,{eins},{}: relation "eins" does not exist 117WARNING: error for table,{eins},{integer}: relation "eins" does not exist 118WARNING: error for table,{addr_nsp,zwei},{}: relation "addr_nsp.zwei" does not exist 119WARNING: error for table,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist 120WARNING: error for table,{eins,zwei,drei},{}: cross-database references are not implemented: "eins.zwei.drei" 121WARNING: error for table,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei" 122WARNING: error for index,{eins},{}: relation "eins" does not exist 123WARNING: error for index,{eins},{integer}: relation "eins" does not exist 124WARNING: error for index,{addr_nsp,zwei},{}: relation "addr_nsp.zwei" does not exist 125WARNING: error for index,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist 126WARNING: error for index,{eins,zwei,drei},{}: cross-database references are not implemented: "eins.zwei.drei" 127WARNING: error for index,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei" 128WARNING: error for sequence,{eins},{}: relation "eins" does not exist 129WARNING: error for sequence,{eins},{integer}: relation "eins" does not exist 130WARNING: error for sequence,{addr_nsp,zwei},{}: relation "addr_nsp.zwei" does not exist 131WARNING: error for sequence,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist 132WARNING: error for sequence,{eins,zwei,drei},{}: cross-database references are not implemented: "eins.zwei.drei" 133WARNING: error for sequence,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei" 134WARNING: error for view,{eins},{}: relation "eins" does not exist 135WARNING: error for view,{eins},{integer}: relation "eins" does not exist 136WARNING: error for view,{addr_nsp,zwei},{}: relation "addr_nsp.zwei" does not exist 137WARNING: error for view,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist 138WARNING: error for view,{eins,zwei,drei},{}: cross-database references are not implemented: "eins.zwei.drei" 139WARNING: error for view,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei" 140WARNING: error for materialized view,{eins},{}: relation "eins" does not exist 141WARNING: error for materialized view,{eins},{integer}: relation "eins" does not exist 142WARNING: error for materialized view,{addr_nsp,zwei},{}: relation "addr_nsp.zwei" does not exist 143WARNING: error for materialized view,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist 144WARNING: error for materialized view,{eins,zwei,drei},{}: cross-database references are not implemented: "eins.zwei.drei" 145WARNING: error for materialized view,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei" 146WARNING: error for foreign table,{eins},{}: relation "eins" does not exist 147WARNING: error for foreign table,{eins},{integer}: relation "eins" does not exist 148WARNING: error for foreign table,{addr_nsp,zwei},{}: relation "addr_nsp.zwei" does not exist 149WARNING: error for foreign table,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist 150WARNING: error for foreign table,{eins,zwei,drei},{}: cross-database references are not implemented: "eins.zwei.drei" 151WARNING: error for foreign table,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei" 152WARNING: error for table column,{eins},{}: column name must be qualified 153WARNING: error for table column,{eins},{integer}: column name must be qualified 154WARNING: error for table column,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist 155WARNING: error for table column,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist 156WARNING: error for table column,{eins,zwei,drei},{}: schema "eins" does not exist 157WARNING: error for table column,{eins,zwei,drei},{integer}: schema "eins" does not exist 158WARNING: error for foreign table column,{eins},{}: column name must be qualified 159WARNING: error for foreign table column,{eins},{integer}: column name must be qualified 160WARNING: error for foreign table column,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist 161WARNING: error for foreign table column,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist 162WARNING: error for foreign table column,{eins,zwei,drei},{}: schema "eins" does not exist 163WARNING: error for foreign table column,{eins,zwei,drei},{integer}: schema "eins" does not exist 164WARNING: error for aggregate,{eins},{}: aggregate eins(*) does not exist 165WARNING: error for aggregate,{eins},{integer}: aggregate eins(integer) does not exist 166WARNING: error for aggregate,{addr_nsp,zwei},{}: aggregate addr_nsp.zwei(*) does not exist 167WARNING: error for aggregate,{addr_nsp,zwei},{integer}: aggregate addr_nsp.zwei(integer) does not exist 168WARNING: error for aggregate,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei 169WARNING: error for aggregate,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei 170WARNING: error for function,{eins},{}: function eins() does not exist 171WARNING: error for function,{eins},{integer}: function eins(integer) does not exist 172WARNING: error for function,{addr_nsp,zwei},{}: function addr_nsp.zwei() does not exist 173WARNING: error for function,{addr_nsp,zwei},{integer}: function addr_nsp.zwei(integer) does not exist 174WARNING: error for function,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei 175WARNING: error for function,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei 176WARNING: error for type,{eins},{}: type "eins" does not exist 177WARNING: error for type,{eins},{integer}: type "eins" does not exist 178WARNING: error for type,{addr_nsp,zwei},{}: name list length must be exactly 1 179WARNING: error for type,{addr_nsp,zwei},{integer}: name list length must be exactly 1 180WARNING: error for type,{eins,zwei,drei},{}: name list length must be exactly 1 181WARNING: error for type,{eins,zwei,drei},{integer}: name list length must be exactly 1 182WARNING: error for cast,{eins},{}: argument list length must be exactly 1 183WARNING: error for cast,{eins},{integer}: type "eins" does not exist 184WARNING: error for cast,{addr_nsp,zwei},{}: name list length must be exactly 1 185WARNING: error for cast,{addr_nsp,zwei},{integer}: name list length must be exactly 1 186WARNING: error for cast,{eins,zwei,drei},{}: name list length must be exactly 1 187WARNING: error for cast,{eins,zwei,drei},{integer}: name list length must be exactly 1 188WARNING: error for table constraint,{eins},{}: must specify relation and object name 189WARNING: error for table constraint,{eins},{integer}: must specify relation and object name 190WARNING: error for table constraint,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist 191WARNING: error for table constraint,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist 192WARNING: error for table constraint,{eins,zwei,drei},{}: schema "eins" does not exist 193WARNING: error for table constraint,{eins,zwei,drei},{integer}: schema "eins" does not exist 194WARNING: error for domain constraint,{eins},{}: argument list length must be exactly 1 195WARNING: error for domain constraint,{eins},{integer}: type "eins" does not exist 196WARNING: error for domain constraint,{addr_nsp,zwei},{}: name list length must be exactly 1 197WARNING: error for domain constraint,{addr_nsp,zwei},{integer}: name list length must be exactly 1 198WARNING: error for domain constraint,{eins,zwei,drei},{}: name list length must be exactly 1 199WARNING: error for domain constraint,{eins,zwei,drei},{integer}: name list length must be exactly 1 200WARNING: error for conversion,{eins},{}: conversion "eins" does not exist 201WARNING: error for conversion,{eins},{integer}: conversion "eins" does not exist 202WARNING: error for conversion,{addr_nsp,zwei},{}: conversion "addr_nsp.zwei" does not exist 203WARNING: error for conversion,{addr_nsp,zwei},{integer}: conversion "addr_nsp.zwei" does not exist 204WARNING: error for conversion,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei 205WARNING: error for conversion,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei 206WARNING: error for default value,{eins},{}: column name must be qualified 207WARNING: error for default value,{eins},{integer}: column name must be qualified 208WARNING: error for default value,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist 209WARNING: error for default value,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist 210WARNING: error for default value,{eins,zwei,drei},{}: schema "eins" does not exist 211WARNING: error for default value,{eins,zwei,drei},{integer}: schema "eins" does not exist 212WARNING: error for operator,{eins},{}: argument list length must be exactly 2 213WARNING: error for operator,{eins},{integer}: argument list length must be exactly 2 214WARNING: error for operator,{addr_nsp,zwei},{}: argument list length must be exactly 2 215WARNING: error for operator,{addr_nsp,zwei},{integer}: argument list length must be exactly 2 216WARNING: error for operator,{eins,zwei,drei},{}: argument list length must be exactly 2 217WARNING: error for operator,{eins,zwei,drei},{integer}: argument list length must be exactly 2 218WARNING: error for operator class,{eins},{}: name list length must be at least 2 219WARNING: error for operator class,{eins},{integer}: name list length must be at least 2 220WARNING: error for operator class,{addr_nsp,zwei},{}: access method "addr_nsp" does not exist 221WARNING: error for operator class,{addr_nsp,zwei},{integer}: access method "addr_nsp" does not exist 222WARNING: error for operator class,{eins,zwei,drei},{}: access method "eins" does not exist 223WARNING: error for operator class,{eins,zwei,drei},{integer}: access method "eins" does not exist 224WARNING: error for operator family,{eins},{}: name list length must be at least 2 225WARNING: error for operator family,{eins},{integer}: name list length must be at least 2 226WARNING: error for operator family,{addr_nsp,zwei},{}: access method "addr_nsp" does not exist 227WARNING: error for operator family,{addr_nsp,zwei},{integer}: access method "addr_nsp" does not exist 228WARNING: error for operator family,{eins,zwei,drei},{}: access method "eins" does not exist 229WARNING: error for operator family,{eins,zwei,drei},{integer}: access method "eins" does not exist 230WARNING: error for rule,{eins},{}: must specify relation and object name 231WARNING: error for rule,{eins},{integer}: must specify relation and object name 232WARNING: error for rule,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist 233WARNING: error for rule,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist 234WARNING: error for rule,{eins,zwei,drei},{}: schema "eins" does not exist 235WARNING: error for rule,{eins,zwei,drei},{integer}: schema "eins" does not exist 236WARNING: error for trigger,{eins},{}: must specify relation and object name 237WARNING: error for trigger,{eins},{integer}: must specify relation and object name 238WARNING: error for trigger,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist 239WARNING: error for trigger,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist 240WARNING: error for trigger,{eins,zwei,drei},{}: schema "eins" does not exist 241WARNING: error for trigger,{eins,zwei,drei},{integer}: schema "eins" does not exist 242WARNING: error for text search parser,{eins},{}: text search parser "eins" does not exist 243WARNING: error for text search parser,{eins},{integer}: text search parser "eins" does not exist 244WARNING: error for text search parser,{addr_nsp,zwei},{}: text search parser "addr_nsp.zwei" does not exist 245WARNING: error for text search parser,{addr_nsp,zwei},{integer}: text search parser "addr_nsp.zwei" does not exist 246WARNING: error for text search parser,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei 247WARNING: error for text search parser,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei 248WARNING: error for text search dictionary,{eins},{}: text search dictionary "eins" does not exist 249WARNING: error for text search dictionary,{eins},{integer}: text search dictionary "eins" does not exist 250WARNING: error for text search dictionary,{addr_nsp,zwei},{}: text search dictionary "addr_nsp.zwei" does not exist 251WARNING: error for text search dictionary,{addr_nsp,zwei},{integer}: text search dictionary "addr_nsp.zwei" does not exist 252WARNING: error for text search dictionary,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei 253WARNING: error for text search dictionary,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei 254WARNING: error for text search template,{eins},{}: text search template "eins" does not exist 255WARNING: error for text search template,{eins},{integer}: text search template "eins" does not exist 256WARNING: error for text search template,{addr_nsp,zwei},{}: text search template "addr_nsp.zwei" does not exist 257WARNING: error for text search template,{addr_nsp,zwei},{integer}: text search template "addr_nsp.zwei" does not exist 258WARNING: error for text search template,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei 259WARNING: error for text search template,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei 260WARNING: error for text search configuration,{eins},{}: text search configuration "eins" does not exist 261WARNING: error for text search configuration,{eins},{integer}: text search configuration "eins" does not exist 262WARNING: error for text search configuration,{addr_nsp,zwei},{}: text search configuration "addr_nsp.zwei" does not exist 263WARNING: error for text search configuration,{addr_nsp,zwei},{integer}: text search configuration "addr_nsp.zwei" does not exist 264WARNING: error for text search configuration,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei 265WARNING: error for text search configuration,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei 266WARNING: error for policy,{eins},{}: must specify relation and object name 267WARNING: error for policy,{eins},{integer}: must specify relation and object name 268WARNING: error for policy,{addr_nsp,zwei},{}: relation "addr_nsp" does not exist 269WARNING: error for policy,{addr_nsp,zwei},{integer}: relation "addr_nsp" does not exist 270WARNING: error for policy,{eins,zwei,drei},{}: schema "eins" does not exist 271WARNING: error for policy,{eins,zwei,drei},{integer}: schema "eins" does not exist 272WARNING: error for user mapping,{eins},{}: argument list length must be exactly 1 273WARNING: error for user mapping,{eins},{integer}: user mapping for user "eins" on server "integer" does not exist 274WARNING: error for user mapping,{addr_nsp,zwei},{}: argument list length must be exactly 1 275WARNING: error for user mapping,{addr_nsp,zwei},{integer}: user mapping for user "addr_nsp" on server "integer" does not exist 276WARNING: error for user mapping,{eins,zwei,drei},{}: argument list length must be exactly 1 277WARNING: error for user mapping,{eins,zwei,drei},{integer}: user mapping for user "eins" on server "integer" does not exist 278WARNING: error for default acl,{eins},{}: argument list length must be exactly 1 279WARNING: error for default acl,{eins},{integer}: unrecognized default ACL object type "i" 280WARNING: error for default acl,{addr_nsp,zwei},{}: argument list length must be exactly 1 281WARNING: error for default acl,{addr_nsp,zwei},{integer}: unrecognized default ACL object type "i" 282WARNING: error for default acl,{eins,zwei,drei},{}: argument list length must be exactly 1 283WARNING: error for default acl,{eins,zwei,drei},{integer}: unrecognized default ACL object type "i" 284WARNING: error for transform,{eins},{}: argument list length must be exactly 1 285WARNING: error for transform,{eins},{integer}: type "eins" does not exist 286WARNING: error for transform,{addr_nsp,zwei},{}: name list length must be exactly 1 287WARNING: error for transform,{addr_nsp,zwei},{integer}: name list length must be exactly 1 288WARNING: error for transform,{eins,zwei,drei},{}: name list length must be exactly 1 289WARNING: error for transform,{eins,zwei,drei},{integer}: name list length must be exactly 1 290WARNING: error for operator of access method,{eins},{}: name list length must be at least 3 291WARNING: error for operator of access method,{eins},{integer}: name list length must be at least 3 292WARNING: error for operator of access method,{addr_nsp,zwei},{}: name list length must be at least 3 293WARNING: error for operator of access method,{addr_nsp,zwei},{integer}: name list length must be at least 3 294WARNING: error for operator of access method,{eins,zwei,drei},{}: argument list length must be exactly 2 295WARNING: error for operator of access method,{eins,zwei,drei},{integer}: argument list length must be exactly 2 296WARNING: error for function of access method,{eins},{}: name list length must be at least 3 297WARNING: error for function of access method,{eins},{integer}: name list length must be at least 3 298WARNING: error for function of access method,{addr_nsp,zwei},{}: name list length must be at least 3 299WARNING: error for function of access method,{addr_nsp,zwei},{integer}: name list length must be at least 3 300WARNING: error for function of access method,{eins,zwei,drei},{}: argument list length must be exactly 2 301WARNING: error for function of access method,{eins,zwei,drei},{integer}: argument list length must be exactly 2 302WARNING: error for publication relation,{eins},{}: argument list length must be exactly 1 303WARNING: error for publication relation,{eins},{integer}: relation "eins" does not exist 304WARNING: error for publication relation,{addr_nsp,zwei},{}: argument list length must be exactly 1 305WARNING: error for publication relation,{addr_nsp,zwei},{integer}: relation "addr_nsp.zwei" does not exist 306WARNING: error for publication relation,{eins,zwei,drei},{}: argument list length must be exactly 1 307WARNING: error for publication relation,{eins,zwei,drei},{integer}: cross-database references are not implemented: "eins.zwei.drei" 308-- these object types cannot be qualified names 309SELECT pg_get_object_address('language', '{one}', '{}'); 310ERROR: language "one" does not exist 311SELECT pg_get_object_address('language', '{one,two}', '{}'); 312ERROR: name list length must be exactly 1 313SELECT pg_get_object_address('large object', '{123}', '{}'); 314ERROR: large object 123 does not exist 315SELECT pg_get_object_address('large object', '{123,456}', '{}'); 316ERROR: name list length must be exactly 1 317SELECT pg_get_object_address('large object', '{blargh}', '{}'); 318ERROR: invalid input syntax for type oid: "blargh" 319SELECT pg_get_object_address('schema', '{one}', '{}'); 320ERROR: schema "one" does not exist 321SELECT pg_get_object_address('schema', '{one,two}', '{}'); 322ERROR: name list length must be exactly 1 323SELECT pg_get_object_address('role', '{one}', '{}'); 324ERROR: role "one" does not exist 325SELECT pg_get_object_address('role', '{one,two}', '{}'); 326ERROR: name list length must be exactly 1 327SELECT pg_get_object_address('database', '{one}', '{}'); 328ERROR: database "one" does not exist 329SELECT pg_get_object_address('database', '{one,two}', '{}'); 330ERROR: name list length must be exactly 1 331SELECT pg_get_object_address('tablespace', '{one}', '{}'); 332ERROR: tablespace "one" does not exist 333SELECT pg_get_object_address('tablespace', '{one,two}', '{}'); 334ERROR: name list length must be exactly 1 335SELECT pg_get_object_address('foreign-data wrapper', '{one}', '{}'); 336ERROR: foreign-data wrapper "one" does not exist 337SELECT pg_get_object_address('foreign-data wrapper', '{one,two}', '{}'); 338ERROR: name list length must be exactly 1 339SELECT pg_get_object_address('server', '{one}', '{}'); 340ERROR: server "one" does not exist 341SELECT pg_get_object_address('server', '{one,two}', '{}'); 342ERROR: name list length must be exactly 1 343SELECT pg_get_object_address('extension', '{one}', '{}'); 344ERROR: extension "one" does not exist 345SELECT pg_get_object_address('extension', '{one,two}', '{}'); 346ERROR: name list length must be exactly 1 347SELECT pg_get_object_address('event trigger', '{one}', '{}'); 348ERROR: event trigger "one" does not exist 349SELECT pg_get_object_address('event trigger', '{one,two}', '{}'); 350ERROR: name list length must be exactly 1 351SELECT pg_get_object_address('access method', '{one}', '{}'); 352ERROR: access method "one" does not exist 353SELECT pg_get_object_address('access method', '{one,two}', '{}'); 354ERROR: name list length must be exactly 1 355SELECT pg_get_object_address('publication', '{one}', '{}'); 356ERROR: publication "one" does not exist 357SELECT pg_get_object_address('publication', '{one,two}', '{}'); 358ERROR: name list length must be exactly 1 359SELECT pg_get_object_address('subscription', '{one}', '{}'); 360ERROR: subscription "one" does not exist 361SELECT pg_get_object_address('subscription', '{one,two}', '{}'); 362ERROR: name list length must be exactly 1 363-- test successful cases 364WITH objects (type, name, args) AS (VALUES 365 ('table', '{addr_nsp, gentable}'::text[], '{}'::text[]), 366 ('index', '{addr_nsp, gentable_pkey}', '{}'), 367 ('sequence', '{addr_nsp, gentable_a_seq}', '{}'), 368 -- toast table 369 ('view', '{addr_nsp, genview}', '{}'), 370 ('materialized view', '{addr_nsp, genmatview}', '{}'), 371 ('foreign table', '{addr_nsp, genftable}', '{}'), 372 ('table column', '{addr_nsp, gentable, b}', '{}'), 373 ('foreign table column', '{addr_nsp, genftable, a}', '{}'), 374 ('aggregate', '{addr_nsp, genaggr}', '{int4}'), 375 ('function', '{pg_catalog, pg_identify_object}', '{pg_catalog.oid, pg_catalog.oid, int4}'), 376 ('type', '{pg_catalog._int4}', '{}'), 377 ('type', '{addr_nsp.gendomain}', '{}'), 378 ('type', '{addr_nsp.gencomptype}', '{}'), 379 ('type', '{addr_nsp.genenum}', '{}'), 380 ('cast', '{int8}', '{int4}'), 381 ('collation', '{default}', '{}'), 382 ('table constraint', '{addr_nsp, gentable, a_chk}', '{}'), 383 ('domain constraint', '{addr_nsp.gendomain}', '{domconstr}'), 384 ('conversion', '{pg_catalog, ascii_to_mic}', '{}'), 385 ('default value', '{addr_nsp, gentable, b}', '{}'), 386 ('language', '{plpgsql}', '{}'), 387 -- large object 388 ('operator', '{+}', '{int4, int4}'), 389 ('operator class', '{btree, int4_ops}', '{}'), 390 ('operator family', '{btree, integer_ops}', '{}'), 391 ('operator of access method', '{btree,integer_ops,1}', '{integer,integer}'), 392 ('function of access method', '{btree,integer_ops,2}', '{integer,integer}'), 393 ('rule', '{addr_nsp, genview, _RETURN}', '{}'), 394 ('trigger', '{addr_nsp, gentable, t}', '{}'), 395 ('schema', '{addr_nsp}', '{}'), 396 ('text search parser', '{addr_ts_prs}', '{}'), 397 ('text search dictionary', '{addr_ts_dict}', '{}'), 398 ('text search template', '{addr_ts_temp}', '{}'), 399 ('text search configuration', '{addr_ts_conf}', '{}'), 400 ('role', '{regress_addr_user}', '{}'), 401 -- database 402 -- tablespace 403 ('foreign-data wrapper', '{addr_fdw}', '{}'), 404 ('server', '{addr_fserv}', '{}'), 405 ('user mapping', '{regress_addr_user}', '{integer}'), 406 ('default acl', '{regress_addr_user,public}', '{r}'), 407 ('default acl', '{regress_addr_user}', '{r}'), 408 -- extension 409 -- event trigger 410 ('policy', '{addr_nsp, gentable, genpol}', '{}'), 411 ('transform', '{int}', '{sql}'), 412 ('access method', '{btree}', '{}'), 413 ('publication', '{addr_pub}', '{}'), 414 ('publication relation', '{addr_nsp, gentable}', '{addr_pub}'), 415 ('subscription', '{addr_sub}', '{}'), 416 ('statistics object', '{addr_nsp, gentable_stat}', '{}') 417 ) 418SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*, 419 -- test roundtrip through pg_identify_object_as_address 420 ROW(pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)) = 421 ROW(pg_identify_object(addr2.classid, addr2.objid, addr2.objsubid)) 422 FROM objects, pg_get_object_address(type, name, args) addr1, 423 pg_identify_object_as_address(classid, objid, objsubid) ioa(typ,nms,args), 424 pg_get_object_address(typ, nms, ioa.args) as addr2 425 ORDER BY addr1.classid, addr1.objid, addr1.objsubid; 426 type | schema | name | identity | ?column? 427---------------------------+------------+-------------------+----------------------------------------------------------------------+---------- 428 default acl | | | for role regress_addr_user in schema public on tables | t 429 default acl | | | for role regress_addr_user on tables | t 430 type | pg_catalog | _int4 | integer[] | t 431 type | addr_nsp | gencomptype | addr_nsp.gencomptype | t 432 type | addr_nsp | genenum | addr_nsp.genenum | t 433 type | addr_nsp | gendomain | addr_nsp.gendomain | t 434 function | pg_catalog | | pg_catalog.pg_identify_object(pg_catalog.oid,pg_catalog.oid,integer) | t 435 aggregate | addr_nsp | | addr_nsp.genaggr(integer) | t 436 sequence | addr_nsp | gentable_a_seq | addr_nsp.gentable_a_seq | t 437 table | addr_nsp | gentable | addr_nsp.gentable | t 438 table column | addr_nsp | gentable | addr_nsp.gentable.b | t 439 index | addr_nsp | gentable_pkey | addr_nsp.gentable_pkey | t 440 view | addr_nsp | genview | addr_nsp.genview | t 441 materialized view | addr_nsp | genmatview | addr_nsp.genmatview | t 442 foreign table | addr_nsp | genftable | addr_nsp.genftable | t 443 foreign table column | addr_nsp | genftable | addr_nsp.genftable.a | t 444 role | | regress_addr_user | regress_addr_user | t 445 server | | addr_fserv | addr_fserv | t 446 user mapping | | | regress_addr_user on server integer | t 447 foreign-data wrapper | | addr_fdw | addr_fdw | t 448 access method | | btree | btree | t 449 operator of access method | | | operator 1 (integer, integer) of pg_catalog.integer_ops USING btree | t 450 function of access method | | | function 2 (integer, integer) of pg_catalog.integer_ops USING btree | t 451 default value | | | for addr_nsp.gentable.b | t 452 cast | | | (bigint AS integer) | t 453 table constraint | addr_nsp | | a_chk on addr_nsp.gentable | t 454 domain constraint | addr_nsp | | domconstr on addr_nsp.gendomain | t 455 conversion | pg_catalog | ascii_to_mic | pg_catalog.ascii_to_mic | t 456 language | | plpgsql | plpgsql | t 457 schema | | addr_nsp | addr_nsp | t 458 operator class | pg_catalog | int4_ops | pg_catalog.int4_ops USING btree | t 459 operator | pg_catalog | | pg_catalog.+(integer,integer) | t 460 rule | | | "_RETURN" on addr_nsp.genview | t 461 trigger | | | t on addr_nsp.gentable | t 462 operator family | pg_catalog | integer_ops | pg_catalog.integer_ops USING btree | t 463 policy | | | genpol on addr_nsp.gentable | t 464 statistics object | addr_nsp | gentable_stat | addr_nsp.gentable_stat | t 465 collation | pg_catalog | "default" | pg_catalog."default" | t 466 transform | | | for integer on language sql | t 467 text search dictionary | addr_nsp | addr_ts_dict | addr_nsp.addr_ts_dict | t 468 text search parser | addr_nsp | addr_ts_prs | addr_nsp.addr_ts_prs | t 469 text search configuration | addr_nsp | addr_ts_conf | addr_nsp.addr_ts_conf | t 470 text search template | addr_nsp | addr_ts_temp | addr_nsp.addr_ts_temp | t 471 subscription | | addr_sub | addr_sub | t 472 publication | | addr_pub | addr_pub | t 473 publication relation | | | addr_nsp.gentable in publication addr_pub | t 474(46 rows) 475 476--- 477--- Cleanup resources 478--- 479\set VERBOSITY terse \\ -- suppress cascade details 480DROP FOREIGN DATA WRAPPER addr_fdw CASCADE; 481NOTICE: drop cascades to 4 other objects 482DROP PUBLICATION addr_pub; 483DROP SUBSCRIPTION addr_sub; 484DROP SCHEMA addr_nsp CASCADE; 485NOTICE: drop cascades to 12 other objects 486DROP OWNED BY regress_addr_user; 487DROP USER regress_addr_user; 488