Skip to content

Commit e605506

Browse files
robertmhaasrobertmhaas
committed
Additional regression tests for ALTER OPERATOR FAMILY.
Robins Tharakan, reviewed by Szymon Guz
1 parent 9feeef9 commit e605506

File tree

2 files changed

+342
-0
lines changed

2 files changed

+342
-0
lines changed

‎src/test/regress/expected/alter_generic.out

Lines changed: 175 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -320,6 +320,181 @@ SELECT nspname, opcname, amname, rolname
320320
alt_nsp2 | alt_opc2 | hash | regtest_alter_user3
321321
(4 rows)
322322

323+
-- ALTER OPERATOR FAMILY ... ADD/DROP
324+
-- Should work. Textbook case of CREATE / ALTER ADD / ALTER DROP / DROP
325+
BEGIN TRANSACTION;
326+
CREATE OPERATOR FAMILY alt_opf4 USING btree;
327+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD
328+
-- int4 vs int2
329+
OPERATOR 1 < (int4, int2) ,
330+
OPERATOR 2 <= (int4, int2) ,
331+
OPERATOR 3 = (int4, int2) ,
332+
OPERATOR 4 >= (int4, int2) ,
333+
OPERATOR 5 > (int4, int2) ,
334+
FUNCTION 1 btint42cmp(int4, int2);
335+
ALTER OPERATOR FAMILY alt_opf4 USING btree DROP
336+
-- int4 vs int2
337+
OPERATOR 1 (int4, int2) ,
338+
OPERATOR 2 (int4, int2) ,
339+
OPERATOR 3 (int4, int2) ,
340+
OPERATOR 4 (int4, int2) ,
341+
OPERATOR 5 (int4, int2) ,
342+
FUNCTION 1 (int4, int2) ;
343+
DROP OPERATOR FAMILY alt_opf4 USING btree;
344+
ROLLBACK;
345+
-- Should fail. Invalid values for ALTER OPERATOR FAMILY .. ADD / DROP
346+
CREATE OPERATOR FAMILY alt_opf4 USING btree;
347+
ALTER OPERATOR FAMILY alt_opf4 USING invalid_index_method ADD OPERATOR 1 < (int4, int2); -- invalid indexing_method
348+
ERROR: access method "invalid_index_method" does not exist
349+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 6 < (int4, int2); -- operator number should be between 1 and 5
350+
ERROR: invalid operator number 6, must be between 1 and 5
351+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 0 < (int4, int2); -- operator number should be between 1 and 5
352+
ERROR: invalid operator number 0, must be between 1 and 5
353+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 1 < ; -- operator without argument types
354+
ERROR: operator argument types must be specified in ALTER OPERATOR FAMILY
355+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 0 btint42cmp(int4, int2); -- function number should be between 1 and 5
356+
ERROR: invalid procedure number 0, must be between 1 and 2
357+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 6 btint42cmp(int4, int2); -- function number should be between 1 and 5
358+
ERROR: invalid procedure number 6, must be between 1 and 2
359+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD STORAGE invalid_storage; -- Ensure STORAGE is not a part of ALTER OPERATOR FAMILY
360+
ERROR: STORAGE cannot be specified in ALTER OPERATOR FAMILY
361+
DROP OPERATOR FAMILY alt_opf4 USING btree;
362+
-- Should fail. Need to be SUPERUSER to do ALTER OPERATOR FAMILY .. ADD / DROP
363+
BEGIN TRANSACTION;
364+
CREATE ROLE regtest_alter_user5 NOSUPERUSER;
365+
CREATE OPERATOR FAMILY alt_opf5 USING btree;
366+
SET ROLE regtest_alter_user5;
367+
ALTER OPERATOR FAMILY alt_opf5 USING btree ADD OPERATOR 1 < (int4, int2), FUNCTION 1 btint42cmp(int4, int2);
368+
ERROR: must be superuser to alter an operator family
369+
RESET ROLE;
370+
ERROR: current transaction is aborted, commands ignored until end of transaction block
371+
DROP OPERATOR FAMILY alt_opf5 USING btree;
372+
ERROR: current transaction is aborted, commands ignored until end of transaction block
373+
ROLLBACK;
374+
-- Should fail. Need rights to namespace for ALTER OPERATOR FAMILY .. ADD / DROP
375+
BEGIN TRANSACTION;
376+
CREATE ROLE regtest_alter_user6;
377+
CREATE SCHEMA alt_nsp6;
378+
REVOKE ALL ON SCHEMA alt_nsp6 FROM regtest_alter_user6;
379+
CREATE OPERATOR FAMILY alt_nsp6.alt_opf6 USING btree;
380+
SET ROLE regtest_alter_user6;
381+
ALTER OPERATOR FAMILY alt_nsp6.alt_opf6 USING btree ADD OPERATOR 1 < (int4, int2);
382+
ERROR: permission denied for schema alt_nsp6
383+
ROLLBACK;
384+
-- Should fail. Only two arguments required for ALTER OPERATOR FAMILY ... DROP OPERATOR
385+
CREATE OPERATOR FAMILY alt_opf7 USING btree;
386+
ALTER OPERATOR FAMILY alt_opf7 USING btree ADD OPERATOR 1 < (int4, int2);
387+
ALTER OPERATOR FAMILY alt_opf7 USING btree DROP OPERATOR 1 (int4, int2, int8);
388+
ERROR: one or two argument types must be specified
389+
DROP OPERATOR FAMILY alt_opf7 USING btree;
390+
-- Should work. During ALTER OPERATOR FAMILY ... DROP OPERATOR
391+
-- when left type is the same as right type, a DROP with only one argument type should work
392+
CREATE OPERATOR FAMILY alt_opf8 USING btree;
393+
ALTER OPERATOR FAMILY alt_opf8 USING btree ADD OPERATOR 1 < (int4, int4);
394+
DROP OPERATOR FAMILY alt_opf8 USING btree;
395+
-- Should work. Textbook case of ALTER OPERATOR FAMILY ... ADD OPERATOR with FOR ORDER BY
396+
CREATE OPERATOR FAMILY alt_opf9 USING gist;
397+
ALTER OPERATOR FAMILY alt_opf9 USING gist ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops;
398+
DROP OPERATOR FAMILY alt_opf9 USING gist;
399+
-- Should fail. Ensure correct ordering methods in ALTER OPERATOR FAMILY ... ADD OPERATOR .. FOR ORDER BY
400+
CREATE OPERATOR FAMILY alt_opf10 USING btree;
401+
ALTER OPERATOR FAMILY alt_opf10 USING btree ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops;
402+
ERROR: access method "btree" does not support ordering operators
403+
DROP OPERATOR FAMILY alt_opf10 USING btree;
404+
-- Should work. Textbook case of ALTER OPERATOR FAMILY ... ADD OPERATOR with FOR ORDER BY
405+
CREATE OPERATOR FAMILY alt_opf11 USING gist;
406+
ALTER OPERATOR FAMILY alt_opf11 USING gist ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops;
407+
ALTER OPERATOR FAMILY alt_opf11 USING gist DROP OPERATOR 1 (int4, int4);
408+
DROP OPERATOR FAMILY alt_opf11 USING gist;
409+
-- Should fail. btree comparison functions should return INTEGER in ALTER OPERATOR FAMILY ... ADD FUNCTION
410+
BEGIN TRANSACTION;
411+
CREATE OPERATOR FAMILY alt_opf12 USING btree;
412+
CREATE FUNCTION fn_opf12 (int4, int2) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL;
413+
ALTER OPERATOR FAMILY alt_opf12 USING btree ADD FUNCTION 1 fn_opf12(int4, int2);
414+
ERROR: btree comparison procedures must return integer
415+
DROP OPERATOR FAMILY alt_opf12 USING btree;
416+
ERROR: current transaction is aborted, commands ignored until end of transaction block
417+
ROLLBACK;
418+
-- Should fail. hash comparison functions should return INTEGER in ALTER OPERATOR FAMILY ... ADD FUNCTION
419+
BEGIN TRANSACTION;
420+
CREATE OPERATOR FAMILY alt_opf13 USING hash;
421+
CREATE FUNCTION fn_opf13 (int4) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL;
422+
ALTER OPERATOR FAMILY alt_opf13 USING hash ADD FUNCTION 1 fn_opf13(int4);
423+
ERROR: hash procedures must return integer
424+
DROP OPERATOR FAMILY alt_opf13 USING hash;
425+
ERROR: current transaction is aborted, commands ignored until end of transaction block
426+
ROLLBACK;
427+
-- Should fail. btree comparison functions should have two arguments in ALTER OPERATOR FAMILY ... ADD FUNCTION
428+
BEGIN TRANSACTION;
429+
CREATE OPERATOR FAMILY alt_opf14 USING btree;
430+
CREATE FUNCTION fn_opf14 (int4) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL;
431+
ALTER OPERATOR FAMILY alt_opf14 USING btree ADD FUNCTION 1 fn_opf14(int4);
432+
ERROR: btree comparison procedures must have two arguments
433+
DROP OPERATOR FAMILY alt_opf14 USING btree;
434+
ERROR: current transaction is aborted, commands ignored until end of transaction block
435+
ROLLBACK;
436+
-- Should fail. hash comparison functions should have one argument in ALTER OPERATOR FAMILY ... ADD FUNCTION
437+
BEGIN TRANSACTION;
438+
CREATE OPERATOR FAMILY alt_opf15 USING hash;
439+
CREATE FUNCTION fn_opf15 (int4, int2) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL;
440+
ALTER OPERATOR FAMILY alt_opf15 USING hash ADD FUNCTION 1 fn_opf15(int4, int2);
441+
ERROR: hash procedures must have one argument
442+
DROP OPERATOR FAMILY alt_opf15 USING hash;
443+
ERROR: current transaction is aborted, commands ignored until end of transaction block
444+
ROLLBACK;
445+
-- Should fail. In gist throw an error when giving different data types for function argument
446+
-- without defining left / right type in ALTER OPERATOR FAMILY ... ADD FUNCTION
447+
CREATE OPERATOR FAMILY alt_opf16 USING gist;
448+
ALTER OPERATOR FAMILY alt_opf16 USING gist ADD FUNCTION 1 btint42cmp(int4, int2);
449+
ERROR: associated data types must be specified for index support procedure
450+
DROP OPERATOR FAMILY alt_opf16 USING gist;
451+
-- Should fail. duplicate operator number / function number in ALTER OPERATOR FAMILY ... ADD FUNCTION
452+
CREATE OPERATOR FAMILY alt_opf17 USING btree;
453+
ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4), OPERATOR 1 < (int4, int4); -- operator # appears twice in same statment
454+
ERROR: operator number 1 for (integer,integer) appears more than once
455+
ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4); -- operator 1 requested first-time
456+
ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4); -- operator 1 requested again in separate statement
457+
ERROR: operator 1(integer,integer) already exists in operator family "alt_opf17"
458+
ALTER OPERATOR FAMILY alt_opf17 USING btree ADD
459+
OPERATOR 1 < (int4, int2) ,
460+
OPERATOR 2 <= (int4, int2) ,
461+
OPERATOR 3 = (int4, int2) ,
462+
OPERATOR 4 >= (int4, int2) ,
463+
OPERATOR 5 > (int4, int2) ,
464+
FUNCTION 1 btint42cmp(int4, int2) ,
465+
FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 appears twice in same statement
466+
ERROR: procedure number 1 for (integer,smallint) appears more than once
467+
ALTER OPERATOR FAMILY alt_opf17 USING btree ADD
468+
OPERATOR 1 < (int4, int2) ,
469+
OPERATOR 2 <= (int4, int2) ,
470+
OPERATOR 3 = (int4, int2) ,
471+
OPERATOR 4 >= (int4, int2) ,
472+
OPERATOR 5 > (int4, int2) ,
473+
FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 appears first time
474+
ALTER OPERATOR FAMILY alt_opf17 USING btree ADD
475+
OPERATOR 1 < (int4, int2) ,
476+
OPERATOR 2 <= (int4, int2) ,
477+
OPERATOR 3 = (int4, int2) ,
478+
OPERATOR 4 >= (int4, int2) ,
479+
OPERATOR 5 > (int4, int2) ,
480+
FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 requested again in separate statement
481+
ERROR: operator 1(integer,smallint) already exists in operator family "alt_opf17"
482+
DROP OPERATOR FAMILY alt_opf17 USING btree;
483+
-- Should fail. Ensure that DROP requests for missing OPERATOR / FUNCTIONS
484+
-- return appropriate message in ALTER OPERATOR FAMILY ... DROP OPERATOR / FUNCTION
485+
CREATE OPERATOR FAMILY alt_opf18 USING btree;
486+
ALTER OPERATOR FAMILY alt_opf18 USING btree DROP OPERATOR 1 (int4, int4);
487+
ERROR: operator 1(integer,integer) does not exist in operator family "alt_opf18"
488+
ALTER OPERATOR FAMILY alt_opf18 USING btree ADD
489+
OPERATOR 1 < (int4, int2) ,
490+
OPERATOR 2 <= (int4, int2) ,
491+
OPERATOR 3 = (int4, int2) ,
492+
OPERATOR 4 >= (int4, int2) ,
493+
OPERATOR 5 > (int4, int2) ,
494+
FUNCTION 1 btint42cmp(int4, int2);
495+
ALTER OPERATOR FAMILY alt_opf18 USING btree DROP FUNCTION 2 (int4, int4);
496+
ERROR: function 2(integer,integer) does not exist in operator family "alt_opf18"
497+
DROP OPERATOR FAMILY alt_opf18 USING btree;
323498
--
324499
-- Text Search Dictionary
325500
--

‎src/test/regress/sql/alter_generic.sql

Lines changed: 167 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -266,6 +266,173 @@ SELECT nspname, opcname, amname, rolname
266266
AND n.nspname IN ('alt_nsp1', 'alt_nsp2')
267267
ORDER BY nspname, opcname;
268268

269+
-- ALTER OPERATOR FAMILY ... ADD/DROP
270+
271+
-- Should work. Textbook case of CREATE / ALTER ADD / ALTER DROP / DROP
272+
BEGIN TRANSACTION;
273+
CREATE OPERATOR FAMILY alt_opf4 USING btree;
274+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD
275+
-- int4 vs int2
276+
OPERATOR 1 < (int4, int2) ,
277+
OPERATOR 2 <= (int4, int2) ,
278+
OPERATOR 3 = (int4, int2) ,
279+
OPERATOR 4 >= (int4, int2) ,
280+
OPERATOR 5 > (int4, int2) ,
281+
FUNCTION 1 btint42cmp(int4, int2);
282+
283+
ALTER OPERATOR FAMILY alt_opf4 USING btree DROP
284+
-- int4 vs int2
285+
OPERATOR 1 (int4, int2) ,
286+
OPERATOR 2 (int4, int2) ,
287+
OPERATOR 3 (int4, int2) ,
288+
OPERATOR 4 (int4, int2) ,
289+
OPERATOR 5 (int4, int2) ,
290+
FUNCTION 1 (int4, int2) ;
291+
DROP OPERATOR FAMILY alt_opf4 USING btree;
292+
ROLLBACK;
293+
294+
-- Should fail. Invalid values for ALTER OPERATOR FAMILY .. ADD / DROP
295+
CREATE OPERATOR FAMILY alt_opf4 USING btree;
296+
ALTER OPERATOR FAMILY alt_opf4 USING invalid_index_method ADD OPERATOR 1 < (int4, int2); -- invalid indexing_method
297+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 6 < (int4, int2); -- operator number should be between 1 and 5
298+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 0 < (int4, int2); -- operator number should be between 1 and 5
299+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD OPERATOR 1 < ; -- operator without argument types
300+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 0 btint42cmp(int4, int2); -- function number should be between 1 and 5
301+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD FUNCTION 6 btint42cmp(int4, int2); -- function number should be between 1 and 5
302+
ALTER OPERATOR FAMILY alt_opf4 USING btree ADD STORAGE invalid_storage; -- Ensure STORAGE is not a part of ALTER OPERATOR FAMILY
303+
DROP OPERATOR FAMILY alt_opf4 USING btree;
304+
305+
-- Should fail. Need to be SUPERUSER to do ALTER OPERATOR FAMILY .. ADD / DROP
306+
BEGIN TRANSACTION;
307+
CREATE ROLE regtest_alter_user5 NOSUPERUSER;
308+
CREATE OPERATOR FAMILY alt_opf5 USING btree;
309+
SET ROLE regtest_alter_user5;
310+
ALTER OPERATOR FAMILY alt_opf5 USING btree ADD OPERATOR 1 < (int4, int2), FUNCTION 1 btint42cmp(int4, int2);
311+
RESET ROLE;
312+
DROP OPERATOR FAMILY alt_opf5 USING btree;
313+
ROLLBACK;
314+
315+
-- Should fail. Need rights to namespace for ALTER OPERATOR FAMILY .. ADD / DROP
316+
BEGIN TRANSACTION;
317+
CREATE ROLE regtest_alter_user6;
318+
CREATE SCHEMA alt_nsp6;
319+
REVOKE ALL ON SCHEMA alt_nsp6 FROM regtest_alter_user6;
320+
CREATE OPERATOR FAMILY alt_nsp6.alt_opf6 USING btree;
321+
SET ROLE regtest_alter_user6;
322+
ALTER OPERATOR FAMILY alt_nsp6.alt_opf6 USING btree ADD OPERATOR 1 < (int4, int2);
323+
ROLLBACK;
324+
325+
-- Should fail. Only two arguments required for ALTER OPERATOR FAMILY ... DROP OPERATOR
326+
CREATE OPERATOR FAMILY alt_opf7 USING btree;
327+
ALTER OPERATOR FAMILY alt_opf7 USING btree ADD OPERATOR 1 < (int4, int2);
328+
ALTER OPERATOR FAMILY alt_opf7 USING btree DROP OPERATOR 1 (int4, int2, int8);
329+
DROP OPERATOR FAMILY alt_opf7 USING btree;
330+
331+
-- Should work. During ALTER OPERATOR FAMILY ... DROP OPERATOR
332+
-- when left type is the same as right type, a DROP with only one argument type should work
333+
CREATE OPERATOR FAMILY alt_opf8 USING btree;
334+
ALTER OPERATOR FAMILY alt_opf8 USING btree ADD OPERATOR 1 < (int4, int4);
335+
DROP OPERATOR FAMILY alt_opf8 USING btree;
336+
337+
-- Should work. Textbook case of ALTER OPERATOR FAMILY ... ADD OPERATOR with FOR ORDER BY
338+
CREATE OPERATOR FAMILY alt_opf9 USING gist;
339+
ALTER OPERATOR FAMILY alt_opf9 USING gist ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops;
340+
DROP OPERATOR FAMILY alt_opf9 USING gist;
341+
342+
-- Should fail. Ensure correct ordering methods in ALTER OPERATOR FAMILY ... ADD OPERATOR .. FOR ORDER BY
343+
CREATE OPERATOR FAMILY alt_opf10 USING btree;
344+
ALTER OPERATOR FAMILY alt_opf10 USING btree ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops;
345+
DROP OPERATOR FAMILY alt_opf10 USING btree;
346+
347+
-- Should work. Textbook case of ALTER OPERATOR FAMILY ... ADD OPERATOR with FOR ORDER BY
348+
CREATE OPERATOR FAMILY alt_opf11 USING gist;
349+
ALTER OPERATOR FAMILY alt_opf11 USING gist ADD OPERATOR 1 < (int4, int4) FOR ORDER BY float_ops;
350+
ALTER OPERATOR FAMILY alt_opf11 USING gist DROP OPERATOR 1 (int4, int4);
351+
DROP OPERATOR FAMILY alt_opf11 USING gist;
352+
353+
-- Should fail. btree comparison functions should return INTEGER in ALTER OPERATOR FAMILY ... ADD FUNCTION
354+
BEGIN TRANSACTION;
355+
CREATE OPERATOR FAMILY alt_opf12 USING btree;
356+
CREATE FUNCTION fn_opf12 (int4, int2) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL;
357+
ALTER OPERATOR FAMILY alt_opf12 USING btree ADD FUNCTION 1 fn_opf12(int4, int2);
358+
DROP OPERATOR FAMILY alt_opf12 USING btree;
359+
ROLLBACK;
360+
361+
-- Should fail. hash comparison functions should return INTEGER in ALTER OPERATOR FAMILY ... ADD FUNCTION
362+
BEGIN TRANSACTION;
363+
CREATE OPERATOR FAMILY alt_opf13 USING hash;
364+
CREATE FUNCTION fn_opf13 (int4) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL;
365+
ALTER OPERATOR FAMILY alt_opf13 USING hash ADD FUNCTION 1 fn_opf13(int4);
366+
DROP OPERATOR FAMILY alt_opf13 USING hash;
367+
ROLLBACK;
368+
369+
-- Should fail. btree comparison functions should have two arguments in ALTER OPERATOR FAMILY ... ADD FUNCTION
370+
BEGIN TRANSACTION;
371+
CREATE OPERATOR FAMILY alt_opf14 USING btree;
372+
CREATE FUNCTION fn_opf14 (int4) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL;
373+
ALTER OPERATOR FAMILY alt_opf14 USING btree ADD FUNCTION 1 fn_opf14(int4);
374+
DROP OPERATOR FAMILY alt_opf14 USING btree;
375+
ROLLBACK;
376+
377+
-- Should fail. hash comparison functions should have one argument in ALTER OPERATOR FAMILY ... ADD FUNCTION
378+
BEGIN TRANSACTION;
379+
CREATE OPERATOR FAMILY alt_opf15 USING hash;
380+
CREATE FUNCTION fn_opf15 (int4, int2) RETURNS BIGINT AS 'SELECT NULL::BIGINT;' LANGUAGE SQL;
381+
ALTER OPERATOR FAMILY alt_opf15 USING hash ADD FUNCTION 1 fn_opf15(int4, int2);
382+
DROP OPERATOR FAMILY alt_opf15 USING hash;
383+
ROLLBACK;
384+
385+
-- Should fail. In gist throw an error when giving different data types for function argument
386+
-- without defining left / right type in ALTER OPERATOR FAMILY ... ADD FUNCTION
387+
CREATE OPERATOR FAMILY alt_opf16 USING gist;
388+
ALTER OPERATOR FAMILY alt_opf16 USING gist ADD FUNCTION 1 btint42cmp(int4, int2);
389+
DROP OPERATOR FAMILY alt_opf16 USING gist;
390+
391+
-- Should fail. duplicate operator number / function number in ALTER OPERATOR FAMILY ... ADD FUNCTION
392+
CREATE OPERATOR FAMILY alt_opf17 USING btree;
393+
ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4), OPERATOR 1 < (int4, int4); -- operator # appears twice in same statment
394+
ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4); -- operator 1 requested first-time
395+
ALTER OPERATOR FAMILY alt_opf17 USING btree ADD OPERATOR 1 < (int4, int4); -- operator 1 requested again in separate statement
396+
ALTER OPERATOR FAMILY alt_opf17 USING btree ADD
397+
OPERATOR 1 < (int4, int2) ,
398+
OPERATOR 2 <= (int4, int2) ,
399+
OPERATOR 3 = (int4, int2) ,
400+
OPERATOR 4 >= (int4, int2) ,
401+
OPERATOR 5 > (int4, int2) ,
402+
FUNCTION 1 btint42cmp(int4, int2) ,
403+
FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 appears twice in same statement
404+
ALTER OPERATOR FAMILY alt_opf17 USING btree ADD
405+
OPERATOR 1 < (int4, int2) ,
406+
OPERATOR 2 <= (int4, int2) ,
407+
OPERATOR 3 = (int4, int2) ,
408+
OPERATOR 4 >= (int4, int2) ,
409+
OPERATOR 5 > (int4, int2) ,
410+
FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 appears first time
411+
ALTER OPERATOR FAMILY alt_opf17 USING btree ADD
412+
OPERATOR 1 < (int4, int2) ,
413+
OPERATOR 2 <= (int4, int2) ,
414+
OPERATOR 3 = (int4, int2) ,
415+
OPERATOR 4 >= (int4, int2) ,
416+
OPERATOR 5 > (int4, int2) ,
417+
FUNCTION 1 btint42cmp(int4, int2); -- procedure 1 requested again in separate statement
418+
DROP OPERATOR FAMILY alt_opf17 USING btree;
419+
420+
421+
-- Should fail. Ensure that DROP requests for missing OPERATOR / FUNCTIONS
422+
-- return appropriate message in ALTER OPERATOR FAMILY ... DROP OPERATOR / FUNCTION
423+
CREATE OPERATOR FAMILY alt_opf18 USING btree;
424+
ALTER OPERATOR FAMILY alt_opf18 USING btree DROP OPERATOR 1 (int4, int4);
425+
ALTER OPERATOR FAMILY alt_opf18 USING btree ADD
426+
OPERATOR 1 < (int4, int2) ,
427+
OPERATOR 2 <= (int4, int2) ,
428+
OPERATOR 3 = (int4, int2) ,
429+
OPERATOR 4 >= (int4, int2) ,
430+
OPERATOR 5 > (int4, int2) ,
431+
FUNCTION 1 btint42cmp(int4, int2);
432+
ALTER OPERATOR FAMILY alt_opf18 USING btree DROP FUNCTION 2 (int4, int4);
433+
DROP OPERATOR FAMILY alt_opf18 USING btree;
434+
435+
269436
--
270437
-- Text Search Dictionary
271438
--

0 commit comments

Comments
 (0)