1-- 2-- Test for ALTER some_object {RENAME TO, OWNER TO, SET SCHEMA} 3-- 4 5-- Clean up in case a prior regression run failed 6SET client_min_messages TO 'warning'; 7 8DROP ROLE IF EXISTS regress_alter_user1; 9DROP ROLE IF EXISTS regress_alter_user2; 10DROP ROLE IF EXISTS regress_alter_user3; 11 12RESET client_min_messages; 13 14CREATE USER regress_alter_user3; 15CREATE USER regress_alter_user2; 16CREATE USER regress_alter_user1 IN ROLE regress_alter_user3; 17 18CREATE SCHEMA alt_nsp1; 19CREATE SCHEMA alt_nsp2; 20 21GRANT ALL ON SCHEMA alt_nsp1, alt_nsp2 TO public; 22 23SET search_path = alt_nsp1, public; 24 25-- 26-- Function and Aggregate 27-- 28SET SESSION AUTHORIZATION regress_alter_user1; 29CREATE FUNCTION alt_func1(int) RETURNS int LANGUAGE sql 30 AS 'SELECT $1 + 1'; 31CREATE FUNCTION alt_func2(int) RETURNS int LANGUAGE sql 32 AS 'SELECT $1 - 1'; 33CREATE AGGREGATE alt_agg1 ( 34 sfunc1 = int4pl, basetype = int4, stype1 = int4, initcond = 0 35); 36CREATE AGGREGATE alt_agg2 ( 37 sfunc1 = int4mi, basetype = int4, stype1 = int4, initcond = 0 38); 39ALTER AGGREGATE alt_func1(int) RENAME TO alt_func3; -- failed (not aggregate) 40ALTER AGGREGATE alt_func1(int) OWNER TO regress_alter_user3; -- failed (not aggregate) 41ALTER AGGREGATE alt_func1(int) SET SCHEMA alt_nsp2; -- failed (not aggregate) 42 43ALTER FUNCTION alt_func1(int) RENAME TO alt_func2; -- failed (name conflict) 44ALTER FUNCTION alt_func1(int) RENAME TO alt_func3; -- OK 45ALTER FUNCTION alt_func2(int) OWNER TO regress_alter_user2; -- failed (no role membership) 46ALTER FUNCTION alt_func2(int) OWNER TO regress_alter_user3; -- OK 47ALTER FUNCTION alt_func2(int) SET SCHEMA alt_nsp1; -- OK, already there 48ALTER FUNCTION alt_func2(int) SET SCHEMA alt_nsp2; -- OK 49 50ALTER AGGREGATE alt_agg1(int) RENAME TO alt_agg2; -- failed (name conflict) 51ALTER AGGREGATE alt_agg1(int) RENAME TO alt_agg3; -- OK 52ALTER AGGREGATE alt_agg2(int) OWNER TO regress_alter_user2; -- failed (no role membership) 53ALTER AGGREGATE alt_agg2(int) OWNER TO regress_alter_user3; -- OK 54ALTER AGGREGATE alt_agg2(int) SET SCHEMA alt_nsp2; -- OK 55 56SET SESSION AUTHORIZATION regress_alter_user2; 57CREATE FUNCTION alt_func1(int) RETURNS int LANGUAGE sql 58 AS 'SELECT $1 + 2'; 59CREATE FUNCTION alt_func2(int) RETURNS int LANGUAGE sql 60 AS 'SELECT $1 - 2'; 61CREATE AGGREGATE alt_agg1 ( 62 sfunc1 = int4pl, basetype = int4, stype1 = int4, initcond = 100 63); 64CREATE AGGREGATE alt_agg2 ( 65 sfunc1 = int4mi, basetype = int4, stype1 = int4, initcond = -100 66); 67 68ALTER FUNCTION alt_func3(int) RENAME TO alt_func4; -- failed (not owner) 69ALTER FUNCTION alt_func1(int) RENAME TO alt_func4; -- OK 70ALTER FUNCTION alt_func3(int) OWNER TO regress_alter_user2; -- failed (not owner) 71ALTER FUNCTION alt_func2(int) OWNER TO regress_alter_user3; -- failed (no role membership) 72ALTER FUNCTION alt_func3(int) SET SCHEMA alt_nsp2; -- failed (not owner) 73ALTER FUNCTION alt_func2(int) SET SCHEMA alt_nsp2; -- failed (name conflicts) 74 75ALTER AGGREGATE alt_agg3(int) RENAME TO alt_agg4; -- failed (not owner) 76ALTER AGGREGATE alt_agg1(int) RENAME TO alt_agg4; -- OK 77ALTER AGGREGATE alt_agg3(int) OWNER TO regress_alter_user2; -- failed (not owner) 78ALTER AGGREGATE alt_agg2(int) OWNER TO regress_alter_user3; -- failed (no role membership) 79ALTER AGGREGATE alt_agg3(int) SET SCHEMA alt_nsp2; -- failed (not owner) 80ALTER AGGREGATE alt_agg2(int) SET SCHEMA alt_nsp2; -- failed (name conflict) 81 82RESET SESSION AUTHORIZATION; 83 84SELECT n.nspname, proname, prorettype::regtype, proisagg, a.rolname 85 FROM pg_proc p, pg_namespace n, pg_authid a 86 WHERE p.pronamespace = n.oid AND p.proowner = a.oid 87 AND n.nspname IN ('alt_nsp1', 'alt_nsp2') 88 ORDER BY nspname, proname; 89 90-- 91-- We would test collations here, but it's not possible because the error 92-- messages tend to be nonportable. 93-- 94 95-- 96-- Conversion 97-- 98SET SESSION AUTHORIZATION regress_alter_user1; 99CREATE CONVERSION alt_conv1 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; 100CREATE CONVERSION alt_conv2 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; 101 102ALTER CONVERSION alt_conv1 RENAME TO alt_conv2; -- failed (name conflict) 103ALTER CONVERSION alt_conv1 RENAME TO alt_conv3; -- OK 104ALTER CONVERSION alt_conv2 OWNER TO regress_alter_user2; -- failed (no role membership) 105ALTER CONVERSION alt_conv2 OWNER TO regress_alter_user3; -- OK 106ALTER CONVERSION alt_conv2 SET SCHEMA alt_nsp2; -- OK 107 108SET SESSION AUTHORIZATION regress_alter_user2; 109CREATE CONVERSION alt_conv1 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; 110CREATE CONVERSION alt_conv2 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; 111 112ALTER CONVERSION alt_conv3 RENAME TO alt_conv4; -- failed (not owner) 113ALTER CONVERSION alt_conv1 RENAME TO alt_conv4; -- OK 114ALTER CONVERSION alt_conv3 OWNER TO regress_alter_user2; -- failed (not owner) 115ALTER CONVERSION alt_conv2 OWNER TO regress_alter_user3; -- failed (no role membership) 116ALTER CONVERSION alt_conv3 SET SCHEMA alt_nsp2; -- failed (not owner) 117ALTER CONVERSION alt_conv2 SET SCHEMA alt_nsp2; -- failed (name conflict) 118 119RESET SESSION AUTHORIZATION; 120 121SELECT n.nspname, c.conname, a.rolname 122 FROM pg_conversion c, pg_namespace n, pg_authid a 123 WHERE c.connamespace = n.oid AND c.conowner = a.oid 124 AND n.nspname IN ('alt_nsp1', 'alt_nsp2') 125 ORDER BY nspname, conname; 126 127-- 128-- Foreign Data Wrapper and Foreign Server 129-- 130CREATE FOREIGN DATA WRAPPER alt_fdw1; 131CREATE FOREIGN DATA WRAPPER alt_fdw2; 132 133CREATE SERVER alt_fserv1 FOREIGN DATA WRAPPER alt_fdw1; 134CREATE SERVER alt_fserv2 FOREIGN DATA WRAPPER alt_fdw2; 135 136ALTER FOREIGN DATA WRAPPER alt_fdw1 RENAME TO alt_fdw2; -- failed (name conflict) 137ALTER FOREIGN DATA WRAPPER alt_fdw1 RENAME TO alt_fdw3; -- OK 138 139ALTER SERVER alt_fserv1 RENAME TO alt_fserv2; -- failed (name conflict) 140ALTER SERVER alt_fserv1 RENAME TO alt_fserv3; -- OK 141 142SELECT fdwname FROM pg_foreign_data_wrapper WHERE fdwname like 'alt_fdw%'; 143SELECT srvname FROM pg_foreign_server WHERE srvname like 'alt_fserv%'; 144 145-- 146-- Procedural Language 147-- 148CREATE LANGUAGE alt_lang1 HANDLER plpgsql_call_handler; 149CREATE LANGUAGE alt_lang2 HANDLER plpgsql_call_handler; 150 151ALTER LANGUAGE alt_lang1 OWNER TO regress_alter_user1; -- OK 152ALTER LANGUAGE alt_lang2 OWNER TO regress_alter_user2; -- OK 153 154SET SESSION AUTHORIZATION regress_alter_user1; 155ALTER LANGUAGE alt_lang1 RENAME TO alt_lang2; -- failed (name conflict) 156ALTER LANGUAGE alt_lang2 RENAME TO alt_lang3; -- failed (not owner) 157ALTER LANGUAGE alt_lang1 RENAME TO alt_lang3; -- OK 158 159ALTER LANGUAGE alt_lang2 OWNER TO regress_alter_user3; -- failed (not owner) 160ALTER LANGUAGE alt_lang3 OWNER TO regress_alter_user2; -- failed (no role membership) 161ALTER LANGUAGE alt_lang3 OWNER TO regress_alter_user3; -- OK 162 163RESET SESSION AUTHORIZATION; 164SELECT lanname, a.rolname 165 FROM pg_language l, pg_authid a 166 WHERE l.lanowner = a.oid AND l.lanname like 'alt_lang%' 167 ORDER BY lanname; 168 169-- 170-- Operator 171-- 172SET SESSION AUTHORIZATION regress_alter_user1; 173 174CREATE OPERATOR @-@ ( leftarg = int4, rightarg = int4, procedure = int4mi ); 175CREATE OPERATOR @+@ ( leftarg = int4, rightarg = int4, procedure = int4pl ); 176 177ALTER OPERATOR @+@(int4, int4) OWNER TO regress_alter_user2; -- failed (no role membership) 178ALTER OPERATOR @+@(int4, int4) OWNER TO regress_alter_user3; -- OK 179ALTER OPERATOR @-@(int4, int4) SET SCHEMA alt_nsp2; -- OK 180 181SET SESSION AUTHORIZATION regress_alter_user2; 182 183CREATE OPERATOR @-@ ( leftarg = int4, rightarg = int4, procedure = int4mi ); 184 185ALTER OPERATOR @+@(int4, int4) OWNER TO regress_alter_user2; -- failed (not owner) 186ALTER OPERATOR @-@(int4, int4) OWNER TO regress_alter_user3; -- failed (no role membership) 187ALTER OPERATOR @+@(int4, int4) SET SCHEMA alt_nsp2; -- failed (not owner) 188-- can't test this: the error message includes the raw oid of namespace 189-- ALTER OPERATOR @-@(int4, int4) SET SCHEMA alt_nsp2; -- failed (name conflict) 190 191RESET SESSION AUTHORIZATION; 192 193SELECT n.nspname, oprname, a.rolname, 194 oprleft::regtype, oprright::regtype, oprcode::regproc 195 FROM pg_operator o, pg_namespace n, pg_authid a 196 WHERE o.oprnamespace = n.oid AND o.oprowner = a.oid 197 AND n.nspname IN ('alt_nsp1', 'alt_nsp2') 198 ORDER BY nspname, oprname; 199 200-- 201-- OpFamily and OpClass 202-- 203CREATE OPERATOR FAMILY alt_opf1 USING hash; 204CREATE OPERATOR FAMILY alt_opf2 USING hash; 205ALTER OPERATOR FAMILY alt_opf1 USING hash OWNER TO regress_alter_user1; 206ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_user1; 207 208CREATE OPERATOR CLASS alt_opc1 FOR TYPE uuid USING hash AS STORAGE uuid; 209CREATE OPERATOR CLASS alt_opc2 FOR TYPE uuid USING hash AS STORAGE uuid; 210ALTER OPERATOR CLASS alt_opc1 USING hash OWNER TO regress_alter_user1; 211ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_user1; 212 213SET SESSION AUTHORIZATION regress_alter_user1; 214 215ALTER OPERATOR FAMILY alt_opf1 USING hash RENAME TO alt_opf2; -- failed (name conflict) 216ALTER OPERATOR FAMILY alt_opf1 USING hash RENAME TO alt_opf3; -- OK 217ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_user2; -- failed (no role membership) 218ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_user3; -- OK 219ALTER OPERATOR FAMILY alt_opf2 USING hash SET SCHEMA alt_nsp2; -- OK 220 221ALTER OPERATOR CLASS alt_opc1 USING hash RENAME TO alt_opc2; -- failed (name conflict) 222ALTER OPERATOR CLASS alt_opc1 USING hash RENAME TO alt_opc3; -- OK 223ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_user2; -- failed (no role membership) 224ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_user3; -- OK 225ALTER OPERATOR CLASS alt_opc2 USING hash SET SCHEMA alt_nsp2; -- OK 226 227RESET SESSION AUTHORIZATION; 228 229CREATE OPERATOR FAMILY alt_opf1 USING hash; 230CREATE OPERATOR FAMILY alt_opf2 USING hash; 231ALTER OPERATOR FAMILY alt_opf1 USING hash OWNER TO regress_alter_user2; 232ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_user2; 233 234CREATE OPERATOR CLASS alt_opc1 FOR TYPE macaddr USING hash AS STORAGE macaddr; 235CREATE OPERATOR CLASS alt_opc2 FOR TYPE macaddr USING hash AS STORAGE macaddr; 236ALTER OPERATOR CLASS alt_opc1 USING hash OWNER TO regress_alter_user2; 237ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_user2; 238 239SET SESSION AUTHORIZATION regress_alter_user2; 240 241ALTER OPERATOR FAMILY alt_opf3 USING hash RENAME TO alt_opf4; -- failed (not owner) 242ALTER OPERATOR FAMILY alt_opf1 USING hash RENAME TO alt_opf4; -- OK 243ALTER OPERATOR FAMILY alt_opf3 USING hash OWNER TO regress_alter_user2; -- failed (not owner) 244ALTER OPERATOR FAMILY alt_opf2 USING hash OWNER TO regress_alter_user3; -- failed (no role membership) 245ALTER OPERATOR FAMILY alt_opf3 USING hash SET SCHEMA alt_nsp2; -- failed (not owner) 246ALTER OPERATOR FAMILY alt_opf2 USING hash SET SCHEMA alt_nsp2; -- failed (name conflict) 247 248ALTER OPERATOR CLASS alt_opc3 USING hash RENAME TO alt_opc4; -- failed (not owner) 249ALTER OPERATOR CLASS alt_opc1 USING hash RENAME TO alt_opc4; -- OK 250ALTER OPERATOR CLASS alt_opc3 USING hash OWNER TO regress_alter_user2; -- failed (not owner) 251ALTER OPERATOR CLASS alt_opc2 USING hash OWNER TO regress_alter_user3; -- failed (no role membership) 252ALTER OPERATOR CLASS alt_opc3 USING hash SET SCHEMA alt_nsp2; -- failed (not owner) 253ALTER OPERATOR CLASS alt_opc2 USING hash SET SCHEMA alt_nsp2; -- failed (name conflict) 254 255RESET SESSION AUTHORIZATION; 256 257SELECT nspname, opfname, amname, rolname 258 FROM pg_opfamily o, pg_am m, pg_namespace n, pg_authid a 259 WHERE o.opfmethod = m.oid AND o.opfnamespace = n.oid AND o.opfowner = a.oid 260 AND n.nspname IN ('alt_nsp1', 'alt_nsp2') 261 AND NOT opfname LIKE 'alt_opc%' 262 ORDER BY nspname, opfname; 263 264SELECT nspname, opcname, amname, rolname 265 FROM pg_opclass o, pg_am m, pg_namespace n, pg_authid a 266 WHERE o.opcmethod = m.oid AND o.opcnamespace = n.oid AND o.opcowner = a.oid 267 AND n.nspname IN ('alt_nsp1', 'alt_nsp2') 268 ORDER BY nspname, opcname; 269 270-- ALTER OPERATOR FAMILY ... ADD/DROP 271 272-- Should work. Textbook case of CREATE / ALTER ADD / ALTER DROP / DROP 273BEGIN TRANSACTION; 274CREATE OPERATOR FAMILY alt_opf4 USING btree; 275ALTER OPERATOR FAMILY alt_opf4 USING btree ADD 276 -- int4 vs int2 277 OPERATOR 1 < (int4, int2) , 278 OPERATOR 2 <= (int4, int2) , 279 OPERATOR 3 = (int4, int2) , 280 OPERATOR 4 >= (int4, int2) , 281 OPERATOR 5 > (int4, int2) , 282 FUNCTION 1 btint42cmp(int4, int2); 283 284ALTER OPERATOR FAMILY alt_opf4 USING btree DROP 285 -- int4 vs int2 286 OPERATOR 1 (int4, int2) , 287 OPERATOR 2 (int4, int2) , 288 OPERATOR 3 (int4, int2) , 289 OPERATOR 4 (int4, int2) , 290 OPERATOR 5 (int4, int2) , 291 FUNCTION 1 (int4, int2) ; 292DROP OPERATOR FAMILY alt_opf4 USING btree; 293ROLLBACK; 294 295-- Should fail. Invalid values for ALTER OPERATOR FAMILY .. ADD / DROP 296CREATE OPERATOR FAMILY alt_opf4 USING btree; 297ALTER OPERATOR FAMILY alt_opf4 USING invalid_index_method ADD OPERATOR 1 < (int4, int2); -- invalid indexing_method 298ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 6 < (int4, int2); -- operator number should be between 1 and 5 299ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 0 < (int4, int2); -- operator number should be between 1 and 5 300ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 1 < ; -- operator without argument types 301ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 0 btint42cmp(int4, int2); -- function number should be between 1 and 5 302ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 6 btint42cmp(int4, int2); -- function number should be between 1 and 5 303ALTER OPERATOR FAMILY alt_opf4 USING btree ADD STORAGE invalid_storage; -- Ensure STORAGE is not a part of ALTER OPERATOR FAMILY 304DROP OPERATOR FAMILY alt_opf4 USING btree; 305 306-- Should fail. Need to be SUPERUSER to do ALTER OPERATOR FAMILY .. ADD / DROP 307BEGIN TRANSACTION; 308CREATE ROLE regress_alter_user5 NOSUPERUSER; 309CREATE OPERATOR FAMILY alt_opf5 USING btree; 310SET ROLE regress_alter_user5; 311ALTER OPERATOR FAMILY alt_opf5 USING btree ADD OPERATOR 1 < (int4, int2), FUNCTION 1 btint42cmp(int4, int2); 312RESET ROLE; 313DROP OPERATOR FAMILY alt_opf5 USING btree; 314ROLLBACK; 315 316-- Should fail. Need rights to namespace for ALTER OPERATOR FAMILY .. ADD / DROP 317BEGIN TRANSACTION; 318CREATE ROLE regress_alter_user6; 319CREATE SCHEMA alt_nsp6; 320REVOKE ALL ON SCHEMA alt_nsp6 FROM regress_alter_user6; 321CREATE OPERATOR FAMILY alt_nsp6.alt_opf6 USING btree; 322SET ROLE regress_alter_user6; 323ALTER OPERATOR FAMILY alt_nsp6.alt_opf6 USING btree ADD OPERATOR 1 < (int4, int2); 324ROLLBACK; 325 326-- Should fail. Only two arguments required for ALTER OPERATOR FAMILY ... DROP OPERATOR 327CREATE OPERATOR FAMILY alt_opf7 USING btree; 328ALTER OPERATOR FAMILY alt_opf7 USING btree ADD OPERATOR 1 < (int4, int2); 329ALTER OPERATOR FAMILY alt_opf7 USING btree DROP OPERATOR 1 (int4, int2, int8); 330DROP OPERATOR FAMILY alt_opf7 USING btree; 331 332-- Should work. During ALTER OPERATOR FAMILY ... DROP OPERATOR 333-- when left type is the same as right type, a DROP with only one argument type should work 334CREATE OPERATOR FAMILY alt_opf8 USING btree; 335ALTER OPERATOR FAMILY alt_opf8 USING btree ADD OPERATOR 1 < (int4, int4); 336DROP OPERATOR FAMILY alt_opf8 USING btree; 337 338-- Should work. Textbook case of ALTER OPERATOR FAMILY ... ADD OPERATOR with FOR ORDER BY 339CREATE OPERATOR FAMILY alt_opf9 USING gist; 340ALTER OPERATOR FAMILY alt_opf9 USING gist ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops; 341DROP OPERATOR FAMILY alt_opf9 USING gist; 342 343-- Should fail. Ensure correct ordering methods in ALTER OPERATOR FAMILY ... ADD OPERATOR .. FOR ORDER BY 344CREATE OPERATOR FAMILY alt_opf10 USING btree; 345ALTER OPERATOR FAMILY alt_opf10 USING btree ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops; 346DROP OPERATOR FAMILY alt_opf10 USING btree; 347 348-- Should work. Textbook case of ALTER OPERATOR FAMILY ... ADD OPERATOR with FOR ORDER BY 349CREATE OPERATOR FAMILY alt_opf11 USING gist; 350ALTER OPERATOR FAMILY alt_opf11 USING gist ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops; 351ALTER OPERATOR FAMILY alt_opf11 USING gist DROP OPERATOR 1 (int4, int4); 352DROP OPERATOR FAMILY alt_opf11 USING gist; 353 354-- Should fail. btree comparison functions should return INTEGER in ALTER OPERATOR FAMILY ... ADD FUNCTION 355BEGIN TRANSACTION; 356CREATE OPERATOR FAMILY alt_opf12 USING btree; 357CREATE FUNCTION fn_opf12 (int4, int2) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL; 358ALTER OPERATOR FAMILY alt_opf12 USING btree ADD FUNCTION 1 fn_opf12(int4, int2); 359DROP OPERATOR FAMILY alt_opf12 USING btree; 360ROLLBACK; 361 362-- Should fail. hash comparison functions should return INTEGER in ALTER OPERATOR FAMILY ... ADD FUNCTION 363BEGIN TRANSACTION; 364CREATE OPERATOR FAMILY alt_opf13 USING hash; 365CREATE FUNCTION fn_opf13 (int4) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL; 366ALTER OPERATOR FAMILY alt_opf13 USING hash ADD FUNCTION 1 fn_opf13(int4); 367DROP OPERATOR FAMILY alt_opf13 USING hash; 368ROLLBACK; 369 370-- Should fail. btree comparison functions should have two arguments in ALTER OPERATOR FAMILY ... ADD FUNCTION 371BEGIN TRANSACTION; 372CREATE OPERATOR FAMILY alt_opf14 USING btree; 373CREATE FUNCTION fn_opf14 (int4) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL; 374ALTER OPERATOR FAMILY alt_opf14 USING btree ADD FUNCTION 1 fn_opf14(int4); 375DROP OPERATOR FAMILY alt_opf14 USING btree; 376ROLLBACK; 377 378-- Should fail. hash comparison functions should have one argument in ALTER OPERATOR FAMILY ... ADD FUNCTION 379BEGIN TRANSACTION; 380CREATE OPERATOR FAMILY alt_opf15 USING hash; 381CREATE FUNCTION fn_opf15 (int4, int2) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL; 382ALTER OPERATOR FAMILY alt_opf15 USING hash ADD FUNCTION 1 fn_opf15(int4, int2); 383DROP OPERATOR FAMILY alt_opf15 USING hash; 384ROLLBACK; 385 386-- Should fail. In gist throw an error when giving different data types for function argument 387-- without defining left / right type in ALTER OPERATOR FAMILY ... ADD FUNCTION 388CREATE OPERATOR FAMILY alt_opf16 USING gist; 389ALTER OPERATOR FAMILY alt_opf16 USING gist ADD FUNCTION 1 btint42cmp(int4, int2); 390DROP OPERATOR FAMILY alt_opf16 USING gist; 391 392-- Should fail. duplicate operator number / function number in ALTER OPERATOR FAMILY ... ADD FUNCTION 393CREATE OPERATOR FAMILY alt_opf17 USING btree; 394ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4), OPERATOR 1 < (int4, int4); -- operator # appears twice in same statement 395ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4); -- operator 1 requested first-time 396ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4); -- operator 1 requested again in separate statement 397ALTER OPERATOR FAMILY alt_opf17 USING btree ADD 398 OPERATOR 1 < (int4, int2) , 399 OPERATOR 2 <= (int4, int2) , 400 OPERATOR 3 = (int4, int2) , 401 OPERATOR 4 >= (int4, int2) , 402 OPERATOR 5 > (int4, int2) , 403 FUNCTION 1 btint42cmp(int4, int2) , 404 FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 appears twice in same statement 405ALTER OPERATOR FAMILY alt_opf17 USING btree ADD 406 OPERATOR 1 < (int4, int2) , 407 OPERATOR 2 <= (int4, int2) , 408 OPERATOR 3 = (int4, int2) , 409 OPERATOR 4 >= (int4, int2) , 410 OPERATOR 5 > (int4, int2) , 411 FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 appears first time 412ALTER OPERATOR FAMILY alt_opf17 USING btree ADD 413 OPERATOR 1 < (int4, int2) , 414 OPERATOR 2 <= (int4, int2) , 415 OPERATOR 3 = (int4, int2) , 416 OPERATOR 4 >= (int4, int2) , 417 OPERATOR 5 > (int4, int2) , 418 FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 requested again in separate statement 419DROP OPERATOR FAMILY alt_opf17 USING btree; 420 421 422-- Should fail. Ensure that DROP requests for missing OPERATOR / FUNCTIONS 423-- return appropriate message in ALTER OPERATOR FAMILY ... DROP OPERATOR / FUNCTION 424CREATE OPERATOR FAMILY alt_opf18 USING btree; 425ALTER OPERATOR FAMILY alt_opf18 USING btree DROP OPERATOR 1 (int4, int4); 426ALTER OPERATOR FAMILY alt_opf18 USING btree ADD 427 OPERATOR 1 < (int4, int2) , 428 OPERATOR 2 <= (int4, int2) , 429 OPERATOR 3 = (int4, int2) , 430 OPERATOR 4 >= (int4, int2) , 431 OPERATOR 5 > (int4, int2) , 432 FUNCTION 1 btint42cmp(int4, int2); 433ALTER OPERATOR FAMILY alt_opf18 USING btree DROP FUNCTION 2 (int4, int4); 434DROP OPERATOR FAMILY alt_opf18 USING btree; 435 436 437-- 438-- Text Search Dictionary 439-- 440SET SESSION AUTHORIZATION regress_alter_user1; 441CREATE TEXT SEARCH DICTIONARY alt_ts_dict1 (template=simple); 442CREATE TEXT SEARCH DICTIONARY alt_ts_dict2 (template=simple); 443 444ALTER TEXT SEARCH DICTIONARY alt_ts_dict1 RENAME TO alt_ts_dict2; -- failed (name conflict) 445ALTER TEXT SEARCH DICTIONARY alt_ts_dict1 RENAME TO alt_ts_dict3; -- OK 446ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 OWNER TO regress_alter_user2; -- failed (no role membership) 447ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 OWNER TO regress_alter_user3; -- OK 448ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 SET SCHEMA alt_nsp2; -- OK 449 450SET SESSION AUTHORIZATION regress_alter_user2; 451CREATE TEXT SEARCH DICTIONARY alt_ts_dict1 (template=simple); 452CREATE TEXT SEARCH DICTIONARY alt_ts_dict2 (template=simple); 453 454ALTER TEXT SEARCH DICTIONARY alt_ts_dict3 RENAME TO alt_ts_dict4; -- failed (not owner) 455ALTER TEXT SEARCH DICTIONARY alt_ts_dict1 RENAME TO alt_ts_dict4; -- OK 456ALTER TEXT SEARCH DICTIONARY alt_ts_dict3 OWNER TO regress_alter_user2; -- failed (not owner) 457ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 OWNER TO regress_alter_user3; -- failed (no role membership) 458ALTER TEXT SEARCH DICTIONARY alt_ts_dict3 SET SCHEMA alt_nsp2; -- failed (not owner) 459ALTER TEXT SEARCH DICTIONARY alt_ts_dict2 SET SCHEMA alt_nsp2; -- failed (name conflict) 460 461RESET SESSION AUTHORIZATION; 462 463SELECT nspname, dictname, rolname 464 FROM pg_ts_dict t, pg_namespace n, pg_authid a 465 WHERE t.dictnamespace = n.oid AND t.dictowner = a.oid 466 AND n.nspname in ('alt_nsp1', 'alt_nsp2') 467 ORDER BY nspname, dictname; 468 469-- 470-- Text Search Configuration 471-- 472SET SESSION AUTHORIZATION regress_alter_user1; 473CREATE TEXT SEARCH CONFIGURATION alt_ts_conf1 (copy=english); 474CREATE TEXT SEARCH CONFIGURATION alt_ts_conf2 (copy=english); 475 476ALTER TEXT SEARCH CONFIGURATION alt_ts_conf1 RENAME TO alt_ts_conf2; -- failed (name conflict) 477ALTER TEXT SEARCH CONFIGURATION alt_ts_conf1 RENAME TO alt_ts_conf3; -- OK 478ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 OWNER TO regress_alter_user2; -- failed (no role membership) 479ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 OWNER TO regress_alter_user3; -- OK 480ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 SET SCHEMA alt_nsp2; -- OK 481 482SET SESSION AUTHORIZATION regress_alter_user2; 483CREATE TEXT SEARCH CONFIGURATION alt_ts_conf1 (copy=english); 484CREATE TEXT SEARCH CONFIGURATION alt_ts_conf2 (copy=english); 485 486ALTER TEXT SEARCH CONFIGURATION alt_ts_conf3 RENAME TO alt_ts_conf4; -- failed (not owner) 487ALTER TEXT SEARCH CONFIGURATION alt_ts_conf1 RENAME TO alt_ts_conf4; -- OK 488ALTER TEXT SEARCH CONFIGURATION alt_ts_conf3 OWNER TO regress_alter_user2; -- failed (not owner) 489ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 OWNER TO regress_alter_user3; -- failed (no role membership) 490ALTER TEXT SEARCH CONFIGURATION alt_ts_conf3 SET SCHEMA alt_nsp2; -- failed (not owner) 491ALTER TEXT SEARCH CONFIGURATION alt_ts_conf2 SET SCHEMA alt_nsp2; -- failed (name conflict) 492 493RESET SESSION AUTHORIZATION; 494 495SELECT nspname, cfgname, rolname 496 FROM pg_ts_config t, pg_namespace n, pg_authid a 497 WHERE t.cfgnamespace = n.oid AND t.cfgowner = a.oid 498 AND n.nspname in ('alt_nsp1', 'alt_nsp2') 499 ORDER BY nspname, cfgname; 500 501-- 502-- Text Search Template 503-- 504CREATE TEXT SEARCH TEMPLATE alt_ts_temp1 (lexize=dsimple_lexize); 505CREATE TEXT SEARCH TEMPLATE alt_ts_temp2 (lexize=dsimple_lexize); 506 507ALTER TEXT SEARCH TEMPLATE alt_ts_temp1 RENAME TO alt_ts_temp2; -- failed (name conflict) 508ALTER TEXT SEARCH TEMPLATE alt_ts_temp1 RENAME TO alt_ts_temp3; -- OK 509ALTER TEXT SEARCH TEMPLATE alt_ts_temp2 SET SCHEMA alt_nsp2; -- OK 510 511CREATE TEXT SEARCH TEMPLATE alt_ts_temp2 (lexize=dsimple_lexize); 512ALTER TEXT SEARCH TEMPLATE alt_ts_temp2 SET SCHEMA alt_nsp2; -- failed (name conflict) 513 514SELECT nspname, tmplname 515 FROM pg_ts_template t, pg_namespace n 516 WHERE t.tmplnamespace = n.oid AND nspname like 'alt_nsp%' 517 ORDER BY nspname, tmplname; 518 519-- 520-- Text Search Parser 521-- 522 523CREATE TEXT SEARCH PARSER alt_ts_prs1 524 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); 525CREATE TEXT SEARCH PARSER alt_ts_prs2 526 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); 527 528ALTER TEXT SEARCH PARSER alt_ts_prs1 RENAME TO alt_ts_prs2; -- failed (name conflict) 529ALTER TEXT SEARCH PARSER alt_ts_prs1 RENAME TO alt_ts_prs3; -- OK 530ALTER TEXT SEARCH PARSER alt_ts_prs2 SET SCHEMA alt_nsp2; -- OK 531 532CREATE TEXT SEARCH PARSER alt_ts_prs2 533 (start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype); 534ALTER TEXT SEARCH PARSER alt_ts_prs2 SET SCHEMA alt_nsp2; -- failed (name conflict) 535 536SELECT nspname, prsname 537 FROM pg_ts_parser t, pg_namespace n 538 WHERE t.prsnamespace = n.oid AND nspname like 'alt_nsp%' 539 ORDER BY nspname, prsname; 540 541--- 542--- Cleanup resources 543--- 544DROP FOREIGN DATA WRAPPER alt_fdw2 CASCADE; 545DROP FOREIGN DATA WRAPPER alt_fdw3 CASCADE; 546 547DROP LANGUAGE alt_lang2 CASCADE; 548DROP LANGUAGE alt_lang3 CASCADE; 549DROP LANGUAGE alt_lang4 CASCADE; 550 551DROP SCHEMA alt_nsp1 CASCADE; 552DROP SCHEMA alt_nsp2 CASCADE; 553 554DROP USER regress_alter_user1; 555DROP USER regress_alter_user2; 556DROP USER regress_alter_user3; 557