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