Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitada3e77

Browse files
committed
Additional regression tests for CREATE OPERATOR.
Robins Tharakan, reviewed by Szymon Guz
1 parent7cd9b13 commitada3e77

File tree

2 files changed

+287
-0
lines changed

2 files changed

+287
-0
lines changed

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

Lines changed: 142 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,3 +29,145 @@ CREATE OPERATOR #%# (
2929
-- Test comments
3030
COMMENT ON OPERATOR ###### (int4, NONE) IS 'bad right unary';
3131
ERROR: operator does not exist: integer ######
32+
-- Show deprecated message. => is deprecated now
33+
CREATE OPERATOR => (
34+
leftarg = int8,-- right unary
35+
procedure = numeric_fac
36+
);
37+
WARNING: => is deprecated as an operator name
38+
DETAIL: This name may be disallowed altogether in future versions of PostgreSQL.
39+
-- Should fail. CREATE OPERATOR requires USAGE on SCHEMA
40+
BEGIN TRANSACTION;
41+
CREATE ROLE regress_rol_op1;
42+
CREATE SCHEMA schema_op1;
43+
GRANT USAGE ON SCHEMA schema_op1 TO PUBLIC;
44+
REVOKE USAGE ON SCHEMA schema_op1 FROM regress_rol_op1;
45+
SET ROLE regress_rol_op1;
46+
CREATE OPERATOR schema_op1.#*# (
47+
leftarg = int8,-- right unary
48+
procedure = numeric_fac
49+
);
50+
ERROR: permission denied for schema schema_op1
51+
ROLLBACK;
52+
-- Should fail. SETOF type functions not allowed as argument (testing leftarg)
53+
BEGIN TRANSACTION;
54+
CREATE OPERATOR #*# (
55+
leftarg = SETOF int8,
56+
procedure = numeric_fac
57+
);
58+
ERROR: SETOF type not allowed for operator argument
59+
ROLLBACK;
60+
-- Should fail. SETOF type functions not allowed as argument (testing rightarg)
61+
BEGIN TRANSACTION;
62+
CREATE OPERATOR #*# (
63+
rightarg = SETOF int8,
64+
procedure = numeric_fac
65+
);
66+
ERROR: SETOF type not allowed for operator argument
67+
ROLLBACK;
68+
-- Should work. Sample text-book case
69+
BEGIN TRANSACTION;
70+
CREATE OR REPLACE FUNCTION fn_op2(boolean, boolean)
71+
RETURNS boolean AS $$
72+
SELECT NULL::BOOLEAN;
73+
$$ LANGUAGE sql IMMUTABLE;
74+
CREATE OPERATOR === (
75+
LEFTARG = boolean,
76+
RIGHTARG = boolean,
77+
PROCEDURE = fn_op2,
78+
COMMUTATOR = ===,
79+
NEGATOR = !==,
80+
RESTRICT = contsel,
81+
JOIN = contjoinsel,
82+
SORT1, SORT2, LTCMP, GTCMP, HASHES, MERGES
83+
);
84+
ROLLBACK;
85+
-- Should fail. Invalid attribute
86+
CREATE OPERATOR #@%# (
87+
leftarg = int8,-- right unary
88+
procedure = numeric_fac,
89+
invalid_att = int8
90+
);
91+
WARNING: operator attribute "invalid_att" not recognized
92+
-- Should fail. At least leftarg or rightarg should be mandatorily specified
93+
CREATE OPERATOR #@%# (
94+
procedure = numeric_fac
95+
);
96+
ERROR: at least one of leftarg or rightarg must be specified
97+
-- Should fail. Procedure should be mandatorily specified
98+
CREATE OPERATOR #@%# (
99+
leftarg = int8
100+
);
101+
ERROR: operator procedure must be specified
102+
-- Should fail. CREATE OPERATOR requires USAGE on TYPE
103+
BEGIN TRANSACTION;
104+
CREATE ROLE regress_rol_op3;
105+
CREATE TYPE type_op3 AS ENUM ('new', 'open', 'closed');
106+
CREATE FUNCTION fn_op3(type_op3, int8)
107+
RETURNS int8 AS $$
108+
SELECT NULL::int8;
109+
$$ LANGUAGE sql IMMUTABLE;
110+
REVOKE USAGE ON TYPE type_op3 FROM regress_rol_op3;
111+
REVOKE USAGE ON TYPE type_op3 FROM PUBLIC; -- Need to do this so that regress_rol_op3 is not allowed USAGE via PUBLIC
112+
SET ROLE regress_rol_op3;
113+
CREATE OPERATOR #*# (
114+
leftarg = type_op3,
115+
rightarg = int8,
116+
procedure = fn_op3
117+
);
118+
ERROR: permission denied for type type_op3
119+
ROLLBACK;
120+
-- Should fail. CREATE OPERATOR requires USAGE on TYPE (need to check separately for rightarg)
121+
BEGIN TRANSACTION;
122+
CREATE ROLE regress_rol_op4;
123+
CREATE TYPE type_op4 AS ENUM ('new', 'open', 'closed');
124+
CREATE FUNCTION fn_op4(int8, type_op4)
125+
RETURNS int8 AS $$
126+
SELECT NULL::int8;
127+
$$ LANGUAGE sql IMMUTABLE;
128+
REVOKE USAGE ON TYPE type_op4 FROM regress_rol_op4;
129+
REVOKE USAGE ON TYPE type_op4 FROM PUBLIC; -- Need to do this so that regress_rol_op3 is not allowed USAGE via PUBLIC
130+
SET ROLE regress_rol_op4;
131+
CREATE OPERATOR #*# (
132+
leftarg = int8,
133+
rightarg = type_op4,
134+
procedure = fn_op4
135+
);
136+
ERROR: permission denied for type type_op4
137+
ROLLBACK;
138+
-- Should fail. CREATE OPERATOR requires EXECUTE on function
139+
BEGIN TRANSACTION;
140+
CREATE ROLE regress_rol_op5;
141+
CREATE TYPE type_op5 AS ENUM ('new', 'open', 'closed');
142+
CREATE FUNCTION fn_op5(int8, int8)
143+
RETURNS int8 AS $$
144+
SELECT NULL::int8;
145+
$$ LANGUAGE sql IMMUTABLE;
146+
REVOKE EXECUTE ON FUNCTION fn_op5(int8, int8) FROM regress_rol_op5;
147+
REVOKE EXECUTE ON FUNCTION fn_op5(int8, int8) FROM PUBLIC;-- Need to do this so that regress_rol_op3 is not allowed EXECUTE via PUBLIC
148+
SET ROLE regress_rol_op5;
149+
CREATE OPERATOR #*# (
150+
leftarg = int8,
151+
rightarg = int8,
152+
procedure = fn_op5
153+
);
154+
ERROR: permission denied for function fn_op5
155+
ROLLBACK;
156+
-- Should fail. CREATE OPERATOR requires USAGE on return TYPE
157+
BEGIN TRANSACTION;
158+
CREATE ROLE regress_rol_op6;
159+
CREATE TYPE type_op6 AS ENUM ('new', 'open', 'closed');
160+
CREATE FUNCTION fn_op6(int8, int8)
161+
RETURNS type_op6 AS $$
162+
SELECT NULL::type_op6;
163+
$$ LANGUAGE sql IMMUTABLE;
164+
REVOKE USAGE ON TYPE type_op6 FROM regress_rol_op6;
165+
REVOKE USAGE ON TYPE type_op6 FROM PUBLIC; -- Need to do this so that regress_rol_op3 is not allowed USAGE via PUBLIC
166+
SET ROLE regress_rol_op6;
167+
CREATE OPERATOR #*# (
168+
leftarg = int8,
169+
rightarg = int8,
170+
procedure = fn_op6
171+
);
172+
ERROR: permission denied for type type_op6
173+
ROLLBACK;

‎src/test/regress/sql/create_operator.sql

Lines changed: 145 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,3 +34,148 @@ CREATE OPERATOR #%# (
3434

3535
-- Test comments
3636
COMMENT ON OPERATOR ###### (int4, NONE) IS'bad right unary';
37+
38+
-- Show deprecated message. => is deprecated now
39+
CREATE OPERATOR=> (
40+
leftarg= int8,-- right unary
41+
procedure= numeric_fac
42+
);
43+
44+
-- Should fail. CREATE OPERATOR requires USAGE on SCHEMA
45+
BEGIN TRANSACTION;
46+
CREATE ROLE regress_rol_op1;
47+
CREATESCHEMAschema_op1;
48+
GRANT USAGEON SCHEMA schema_op1 TO PUBLIC;
49+
REVOKE USAGEON SCHEMA schema_op1FROM regress_rol_op1;
50+
SET ROLE regress_rol_op1;
51+
CREATEOPERATORschema_op1.#*# (
52+
leftarg= int8,-- right unary
53+
procedure= numeric_fac
54+
);
55+
ROLLBACK;
56+
57+
58+
-- Should fail. SETOF type functions not allowed as argument (testing leftarg)
59+
BEGIN TRANSACTION;
60+
CREATE OPERATOR#*# (
61+
leftarg= SETOF int8,
62+
procedure= numeric_fac
63+
);
64+
ROLLBACK;
65+
66+
67+
-- Should fail. SETOF type functions not allowed as argument (testing rightarg)
68+
BEGIN TRANSACTION;
69+
CREATE OPERATOR#*# (
70+
rightarg= SETOF int8,
71+
procedure= numeric_fac
72+
);
73+
ROLLBACK;
74+
75+
76+
-- Should work. Sample text-book case
77+
BEGIN TRANSACTION;
78+
CREATE OR REPLACEFUNCTIONfn_op2(boolean,boolean)
79+
RETURNSbooleanAS $$
80+
SELECTNULL::BOOLEAN;
81+
$$ LANGUAGE sql IMMUTABLE;
82+
CREATE OPERATOR=== (
83+
LEFTARG=boolean,
84+
RIGHTARG=boolean,
85+
PROCEDURE= fn_op2,
86+
COMMUTATOR====,
87+
NEGATOR=!==,
88+
RESTRICT= contsel,
89+
JOIN= contjoinsel,
90+
SORT1, SORT2, LTCMP, GTCMP, HASHES, MERGES
91+
);
92+
ROLLBACK;
93+
94+
-- Should fail. Invalid attribute
95+
CREATE OPERATOR#@%# (
96+
leftarg= int8,-- right unary
97+
procedure= numeric_fac,
98+
invalid_att= int8
99+
);
100+
101+
-- Should fail. At least leftarg or rightarg should be mandatorily specified
102+
CREATE OPERATOR#@%# (
103+
procedure= numeric_fac
104+
);
105+
106+
-- Should fail. Procedure should be mandatorily specified
107+
CREATE OPERATOR#@%# (
108+
leftarg= int8
109+
);
110+
111+
-- Should fail. CREATE OPERATOR requires USAGE on TYPE
112+
BEGIN TRANSACTION;
113+
CREATE ROLE regress_rol_op3;
114+
CREATETYPEtype_op3AS ENUM ('new','open','closed');
115+
CREATEFUNCTIONfn_op3(type_op3, int8)
116+
RETURNS int8AS $$
117+
SELECTNULL::int8;
118+
$$ LANGUAGE sql IMMUTABLE;
119+
REVOKE USAGEON TYPE type_op3FROM regress_rol_op3;
120+
REVOKE USAGEON TYPE type_op3FROM PUBLIC;-- Need to do this so that regress_rol_op3 is not allowed USAGE via PUBLIC
121+
SET ROLE regress_rol_op3;
122+
CREATE OPERATOR#*# (
123+
leftarg= type_op3,
124+
rightarg= int8,
125+
procedure= fn_op3
126+
);
127+
ROLLBACK;
128+
129+
-- Should fail. CREATE OPERATOR requires USAGE on TYPE (need to check separately for rightarg)
130+
BEGIN TRANSACTION;
131+
CREATE ROLE regress_rol_op4;
132+
CREATETYPEtype_op4AS ENUM ('new','open','closed');
133+
CREATEFUNCTIONfn_op4(int8, type_op4)
134+
RETURNS int8AS $$
135+
SELECTNULL::int8;
136+
$$ LANGUAGE sql IMMUTABLE;
137+
REVOKE USAGEON TYPE type_op4FROM regress_rol_op4;
138+
REVOKE USAGEON TYPE type_op4FROM PUBLIC;-- Need to do this so that regress_rol_op3 is not allowed USAGE via PUBLIC
139+
SET ROLE regress_rol_op4;
140+
CREATE OPERATOR#*# (
141+
leftarg= int8,
142+
rightarg= type_op4,
143+
procedure= fn_op4
144+
);
145+
ROLLBACK;
146+
147+
-- Should fail. CREATE OPERATOR requires EXECUTE on function
148+
BEGIN TRANSACTION;
149+
CREATE ROLE regress_rol_op5;
150+
CREATETYPEtype_op5AS ENUM ('new','open','closed');
151+
CREATEFUNCTIONfn_op5(int8, int8)
152+
RETURNS int8AS $$
153+
SELECTNULL::int8;
154+
$$ LANGUAGE sql IMMUTABLE;
155+
REVOKE EXECUTEON FUNCTION fn_op5(int8, int8)FROM regress_rol_op5;
156+
REVOKE EXECUTEON FUNCTION fn_op5(int8, int8)FROM PUBLIC;-- Need to do this so that regress_rol_op3 is not allowed EXECUTE via PUBLIC
157+
SET ROLE regress_rol_op5;
158+
CREATE OPERATOR#*# (
159+
leftarg= int8,
160+
rightarg= int8,
161+
procedure= fn_op5
162+
);
163+
ROLLBACK;
164+
165+
-- Should fail. CREATE OPERATOR requires USAGE on return TYPE
166+
BEGIN TRANSACTION;
167+
CREATE ROLE regress_rol_op6;
168+
CREATETYPEtype_op6AS ENUM ('new','open','closed');
169+
CREATEFUNCTIONfn_op6(int8, int8)
170+
RETURNS type_op6AS $$
171+
SELECTNULL::type_op6;
172+
$$ LANGUAGE sql IMMUTABLE;
173+
REVOKE USAGEON TYPE type_op6FROM regress_rol_op6;
174+
REVOKE USAGEON TYPE type_op6FROM PUBLIC;-- Need to do this so that regress_rol_op3 is not allowed USAGE via PUBLIC
175+
SET ROLE regress_rol_op6;
176+
CREATE OPERATOR#*# (
177+
leftarg= int8,
178+
rightarg= int8,
179+
procedure= fn_op6
180+
);
181+
ROLLBACK;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp