1-- 2-- CREATE_OPERATOR 3-- 4 5CREATE OPERATOR ## ( 6 leftarg = path, 7 rightarg = path, 8 function = path_inter, 9 commutator = ## 10); 11 12CREATE OPERATOR <% ( 13 leftarg = point, 14 rightarg = widget, 15 procedure = pt_in_widget, 16 commutator = >% , 17 negator = >=% 18); 19 20CREATE OPERATOR @#@ ( 21 rightarg = int8, -- left unary 22 procedure = numeric_fac 23); 24 25CREATE OPERATOR #@# ( 26 leftarg = int8, -- right unary 27 procedure = numeric_fac 28); 29 30CREATE OPERATOR #%# ( 31 leftarg = int8, -- right unary 32 procedure = numeric_fac 33); 34 35-- Test operator created above 36SELECT point '(1,2)' <% widget '(0,0,3)' AS t, 37 point '(1,2)' <% widget '(0,0,1)' AS f; 38 39-- Test comments 40COMMENT ON OPERATOR ###### (int4, NONE) IS 'bad right unary'; 41 42-- => is disallowed now 43CREATE OPERATOR => ( 44 leftarg = int8, -- right unary 45 procedure = numeric_fac 46); 47 48-- lexing of <=, >=, <>, != has a number of edge cases 49-- (=> is tested elsewhere) 50 51-- this is legal because ! is not allowed in sql ops 52CREATE OPERATOR !=- ( 53 leftarg = int8, -- right unary 54 procedure = numeric_fac 55); 56SELECT 2 !=-; 57-- make sure lexer returns != as <> even in edge cases 58SELECT 2 !=/**/ 1, 2 !=/**/ 2; 59SELECT 2 !=-- comment to be removed by psql 60 1; 61DO $$ -- use DO to protect -- from psql 62 declare r boolean; 63 begin 64 execute $e$ select 2 !=-- comment 65 1 $e$ into r; 66 raise info 'r = %', r; 67 end; 68$$; 69 70-- check that <= etc. followed by more operator characters are returned 71-- as the correct token with correct precedence 72SELECT true<>-1 BETWEEN 1 AND 1; -- BETWEEN has prec. above <> but below Op 73SELECT false<>/**/1 BETWEEN 1 AND 1; 74SELECT false<=-1 BETWEEN 1 AND 1; 75SELECT false>=-1 BETWEEN 1 AND 1; 76SELECT 2<=/**/3, 3>=/**/2, 2<>/**/3; 77SELECT 3<=/**/2, 2>=/**/3, 2<>/**/2; 78 79-- Should fail. CREATE OPERATOR requires USAGE on SCHEMA 80BEGIN TRANSACTION; 81CREATE ROLE regress_rol_op1; 82CREATE SCHEMA schema_op1; 83GRANT USAGE ON SCHEMA schema_op1 TO PUBLIC; 84REVOKE USAGE ON SCHEMA schema_op1 FROM regress_rol_op1; 85SET ROLE regress_rol_op1; 86CREATE OPERATOR schema_op1.#*# ( 87 leftarg = int8, -- right unary 88 procedure = numeric_fac 89); 90ROLLBACK; 91 92 93-- Should fail. SETOF type functions not allowed as argument (testing leftarg) 94BEGIN TRANSACTION; 95CREATE OPERATOR #*# ( 96 leftarg = SETOF int8, 97 procedure = numeric_fac 98); 99ROLLBACK; 100 101 102-- Should fail. SETOF type functions not allowed as argument (testing rightarg) 103BEGIN TRANSACTION; 104CREATE OPERATOR #*# ( 105 rightarg = SETOF int8, 106 procedure = numeric_fac 107); 108ROLLBACK; 109 110 111-- Should work. Sample text-book case 112BEGIN TRANSACTION; 113CREATE OR REPLACE FUNCTION fn_op2(boolean, boolean) 114RETURNS boolean AS $$ 115 SELECT NULL::BOOLEAN; 116$$ LANGUAGE sql IMMUTABLE; 117CREATE OPERATOR === ( 118 LEFTARG = boolean, 119 RIGHTARG = boolean, 120 PROCEDURE = fn_op2, 121 COMMUTATOR = ===, 122 NEGATOR = !==, 123 RESTRICT = contsel, 124 JOIN = contjoinsel, 125 SORT1, SORT2, LTCMP, GTCMP, HASHES, MERGES 126); 127ROLLBACK; 128 129-- Should fail. Invalid attribute 130CREATE OPERATOR #@%# ( 131 leftarg = int8, -- right unary 132 procedure = numeric_fac, 133 invalid_att = int8 134); 135 136-- Should fail. At least leftarg or rightarg should be mandatorily specified 137CREATE OPERATOR #@%# ( 138 procedure = numeric_fac 139); 140 141-- Should fail. Procedure should be mandatorily specified 142CREATE OPERATOR #@%# ( 143 leftarg = int8 144); 145 146-- Should fail. CREATE OPERATOR requires USAGE on TYPE 147BEGIN TRANSACTION; 148CREATE ROLE regress_rol_op3; 149CREATE TYPE type_op3 AS ENUM ('new', 'open', 'closed'); 150CREATE FUNCTION fn_op3(type_op3, int8) 151RETURNS int8 AS $$ 152 SELECT NULL::int8; 153$$ LANGUAGE sql IMMUTABLE; 154REVOKE USAGE ON TYPE type_op3 FROM regress_rol_op3; 155REVOKE USAGE ON TYPE type_op3 FROM PUBLIC; -- Need to do this so that regress_rol_op3 is not allowed USAGE via PUBLIC 156SET ROLE regress_rol_op3; 157CREATE OPERATOR #*# ( 158 leftarg = type_op3, 159 rightarg = int8, 160 procedure = fn_op3 161); 162ROLLBACK; 163 164-- Should fail. CREATE OPERATOR requires USAGE on TYPE (need to check separately for rightarg) 165BEGIN TRANSACTION; 166CREATE ROLE regress_rol_op4; 167CREATE TYPE type_op4 AS ENUM ('new', 'open', 'closed'); 168CREATE FUNCTION fn_op4(int8, type_op4) 169RETURNS int8 AS $$ 170 SELECT NULL::int8; 171$$ LANGUAGE sql IMMUTABLE; 172REVOKE USAGE ON TYPE type_op4 FROM regress_rol_op4; 173REVOKE USAGE ON TYPE type_op4 FROM PUBLIC; -- Need to do this so that regress_rol_op3 is not allowed USAGE via PUBLIC 174SET ROLE regress_rol_op4; 175CREATE OPERATOR #*# ( 176 leftarg = int8, 177 rightarg = type_op4, 178 procedure = fn_op4 179); 180ROLLBACK; 181 182-- Should fail. CREATE OPERATOR requires EXECUTE on function 183BEGIN TRANSACTION; 184CREATE ROLE regress_rol_op5; 185CREATE TYPE type_op5 AS ENUM ('new', 'open', 'closed'); 186CREATE FUNCTION fn_op5(int8, int8) 187RETURNS int8 AS $$ 188 SELECT NULL::int8; 189$$ LANGUAGE sql IMMUTABLE; 190REVOKE EXECUTE ON FUNCTION fn_op5(int8, int8) FROM regress_rol_op5; 191REVOKE EXECUTE ON FUNCTION fn_op5(int8, int8) FROM PUBLIC;-- Need to do this so that regress_rol_op3 is not allowed EXECUTE via PUBLIC 192SET ROLE regress_rol_op5; 193CREATE OPERATOR #*# ( 194 leftarg = int8, 195 rightarg = int8, 196 procedure = fn_op5 197); 198ROLLBACK; 199 200-- Should fail. CREATE OPERATOR requires USAGE on return TYPE 201BEGIN TRANSACTION; 202CREATE ROLE regress_rol_op6; 203CREATE TYPE type_op6 AS ENUM ('new', 'open', 'closed'); 204CREATE FUNCTION fn_op6(int8, int8) 205RETURNS type_op6 AS $$ 206 SELECT NULL::type_op6; 207$$ LANGUAGE sql IMMUTABLE; 208REVOKE USAGE ON TYPE type_op6 FROM regress_rol_op6; 209REVOKE USAGE ON TYPE type_op6 FROM PUBLIC; -- Need to do this so that regress_rol_op3 is not allowed USAGE via PUBLIC 210SET ROLE regress_rol_op6; 211CREATE OPERATOR #*# ( 212 leftarg = int8, 213 rightarg = int8, 214 procedure = fn_op6 215); 216ROLLBACK; 217 218-- invalid: non-lowercase quoted identifiers 219CREATE OPERATOR === 220( 221 "Leftarg" = box, 222 "Rightarg" = box, 223 "Procedure" = area_equal_function, 224 "Commutator" = ===, 225 "Negator" = !==, 226 "Restrict" = area_restriction_function, 227 "Join" = area_join_function, 228 "Hashes", 229 "Merges" 230); 231