1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
CREATE FUNCTION alter_op_test_fn(boolean, boolean)
RETURNS boolean AS $$ SELECT NULL::BOOLEAN; $$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION customcontsel(internal, oid, internal, integer)
RETURNS float8 AS 'contsel' LANGUAGE internal STABLE STRICT;
CREATE OPERATOR === (
LEFTARG = boolean,
RIGHTARG = boolean,
PROCEDURE = alter_op_test_fn,
COMMUTATOR = ===,
NEGATOR = !==,
RESTRICT = customcontsel,
JOIN = contjoinsel,
HASHES, MERGES
);
SELECT pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype
FROM pg_depend
WHERE classid = 'pg_operator'::regclass AND
objid = '===(bool,bool)'::regoperator
ORDER BY 1;
ref | deptype
-------------------------------------------------------+---------
function alter_op_test_fn(boolean,boolean) | n
function customcontsel(internal,oid,internal,integer) | n
schema public | n
(3 rows)
--
-- Reset and set params
--
ALTER OPERATOR === (boolean, boolean) SET (RESTRICT = NONE);
ALTER OPERATOR === (boolean, boolean) SET (JOIN = NONE);
SELECT oprrest, oprjoin FROM pg_operator WHERE oprname = '==='
AND oprleft = 'boolean'::regtype AND oprright = 'boolean'::regtype;
oprrest | oprjoin
---------+---------
- | -
(1 row)
SELECT pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype
FROM pg_depend
WHERE classid = 'pg_operator'::regclass AND
objid = '===(bool,bool)'::regoperator
ORDER BY 1;
ref | deptype
--------------------------------------------+---------
function alter_op_test_fn(boolean,boolean) | n
schema public | n
(2 rows)
ALTER OPERATOR === (boolean, boolean) SET (RESTRICT = contsel);
ALTER OPERATOR === (boolean, boolean) SET (JOIN = contjoinsel);
SELECT oprrest, oprjoin FROM pg_operator WHERE oprname = '==='
AND oprleft = 'boolean'::regtype AND oprright = 'boolean'::regtype;
oprrest | oprjoin
---------+-------------
contsel | contjoinsel
(1 row)
SELECT pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype
FROM pg_depend
WHERE classid = 'pg_operator'::regclass AND
objid = '===(bool,bool)'::regoperator
ORDER BY 1;
ref | deptype
--------------------------------------------+---------
function alter_op_test_fn(boolean,boolean) | n
schema public | n
(2 rows)
ALTER OPERATOR === (boolean, boolean) SET (RESTRICT = NONE, JOIN = NONE);
SELECT oprrest, oprjoin FROM pg_operator WHERE oprname = '==='
AND oprleft = 'boolean'::regtype AND oprright = 'boolean'::regtype;
oprrest | oprjoin
---------+---------
- | -
(1 row)
SELECT pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype
FROM pg_depend
WHERE classid = 'pg_operator'::regclass AND
objid = '===(bool,bool)'::regoperator
ORDER BY 1;
ref | deptype
--------------------------------------------+---------
function alter_op_test_fn(boolean,boolean) | n
schema public | n
(2 rows)
ALTER OPERATOR === (boolean, boolean) SET (RESTRICT = customcontsel, JOIN = contjoinsel);
SELECT oprrest, oprjoin FROM pg_operator WHERE oprname = '==='
AND oprleft = 'boolean'::regtype AND oprright = 'boolean'::regtype;
oprrest | oprjoin
---------------+-------------
customcontsel | contjoinsel
(1 row)
SELECT pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype
FROM pg_depend
WHERE classid = 'pg_operator'::regclass AND
objid = '===(bool,bool)'::regoperator
ORDER BY 1;
ref | deptype
-------------------------------------------------------+---------
function alter_op_test_fn(boolean,boolean) | n
function customcontsel(internal,oid,internal,integer) | n
schema public | n
(3 rows)
--
-- Test invalid options.
--
ALTER OPERATOR === (boolean, boolean) SET (COMMUTATOR = ====);
ERROR: operator attribute "commutator" cannot be changed
ALTER OPERATOR === (boolean, boolean) SET (NEGATOR = ====);
ERROR: operator attribute "negator" cannot be changed
ALTER OPERATOR === (boolean, boolean) SET (RESTRICT = non_existent_func);
ERROR: function non_existent_func(internal, oid, internal, integer) does not exist
ALTER OPERATOR === (boolean, boolean) SET (JOIN = non_existent_func);
ERROR: function non_existent_func(internal, oid, internal, smallint, internal) does not exist
ALTER OPERATOR === (boolean, boolean) SET (COMMUTATOR = !==);
ERROR: operator attribute "commutator" cannot be changed
ALTER OPERATOR === (boolean, boolean) SET (NEGATOR = !==);
ERROR: operator attribute "negator" cannot be changed
-- invalid: non-lowercase quoted identifiers
ALTER OPERATOR & (bit, bit) SET ("Restrict" = _int_contsel, "Join" = _int_contjoinsel);
ERROR: operator attribute "Restrict" not recognized
--
-- Test permission check. Must be owner to ALTER OPERATOR.
--
CREATE USER regress_alter_op_user;
SET SESSION AUTHORIZATION regress_alter_op_user;
ALTER OPERATOR === (boolean, boolean) SET (RESTRICT = NONE);
ERROR: must be owner of operator ===
-- Clean up
RESET SESSION AUTHORIZATION;
DROP USER regress_alter_op_user;
DROP OPERATOR === (boolean, boolean);
DROP FUNCTION customcontsel(internal, oid, internal, integer);
DROP FUNCTION alter_op_test_fn(boolean, boolean);
|
