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