1-- 2-- CREATE FUNCTION 3-- 4-- Assorted tests using SQL-language functions 5-- 6 7-- All objects made in this test are in temp_func_test schema 8 9CREATE USER regress_unpriv_user; 10 11CREATE SCHEMA temp_func_test; 12GRANT ALL ON SCHEMA temp_func_test TO public; 13 14SET search_path TO temp_func_test, public; 15 16-- 17-- Make sanity checks on the pg_proc entries created by CREATE FUNCTION 18-- 19 20-- 21-- ARGUMENT and RETURN TYPES 22-- 23CREATE FUNCTION functest_A_1(text, date) RETURNS bool LANGUAGE 'sql' 24 AS 'SELECT $1 = ''abcd'' AND $2 > ''2001-01-01'''; 25CREATE FUNCTION functest_A_2(text[]) RETURNS int LANGUAGE 'sql' 26 AS 'SELECT $1[1]::int'; 27CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql' 28 AS 'SELECT false'; 29SELECT proname, prorettype::regtype, proargtypes::regtype[] FROM pg_proc 30 WHERE oid in ('functest_A_1'::regproc, 31 'functest_A_2'::regproc, 32 'functest_A_3'::regproc) ORDER BY proname; 33 34SELECT functest_A_1('abcd', '2020-01-01'); 35SELECT functest_A_2(ARRAY['1', '2', '3']); 36SELECT functest_A_3(); 37 38-- 39-- IMMUTABLE | STABLE | VOLATILE 40-- 41CREATE FUNCTION functest_B_1(int) RETURNS bool LANGUAGE 'sql' 42 AS 'SELECT $1 > 0'; 43CREATE FUNCTION functest_B_2(int) RETURNS bool LANGUAGE 'sql' 44 IMMUTABLE AS 'SELECT $1 > 0'; 45CREATE FUNCTION functest_B_3(int) RETURNS bool LANGUAGE 'sql' 46 STABLE AS 'SELECT $1 = 0'; 47CREATE FUNCTION functest_B_4(int) RETURNS bool LANGUAGE 'sql' 48 VOLATILE AS 'SELECT $1 < 0'; 49SELECT proname, provolatile FROM pg_proc 50 WHERE oid in ('functest_B_1'::regproc, 51 'functest_B_2'::regproc, 52 'functest_B_3'::regproc, 53 'functest_B_4'::regproc) ORDER BY proname; 54 55ALTER FUNCTION functest_B_2(int) VOLATILE; 56ALTER FUNCTION functest_B_3(int) COST 100; -- unrelated change, no effect 57SELECT proname, provolatile FROM pg_proc 58 WHERE oid in ('functest_B_1'::regproc, 59 'functest_B_2'::regproc, 60 'functest_B_3'::regproc, 61 'functest_B_4'::regproc) ORDER BY proname; 62 63-- 64-- SECURITY DEFINER | INVOKER 65-- 66CREATE FUNCTION functest_C_1(int) RETURNS bool LANGUAGE 'sql' 67 AS 'SELECT $1 > 0'; 68CREATE FUNCTION functest_C_2(int) RETURNS bool LANGUAGE 'sql' 69 SECURITY DEFINER AS 'SELECT $1 = 0'; 70CREATE FUNCTION functest_C_3(int) RETURNS bool LANGUAGE 'sql' 71 SECURITY INVOKER AS 'SELECT $1 < 0'; 72SELECT proname, prosecdef FROM pg_proc 73 WHERE oid in ('functest_C_1'::regproc, 74 'functest_C_2'::regproc, 75 'functest_C_3'::regproc) ORDER BY proname; 76 77ALTER FUNCTION functest_C_1(int) IMMUTABLE; -- unrelated change, no effect 78ALTER FUNCTION functest_C_2(int) SECURITY INVOKER; 79ALTER FUNCTION functest_C_3(int) SECURITY DEFINER; 80SELECT proname, prosecdef FROM pg_proc 81 WHERE oid in ('functest_C_1'::regproc, 82 'functest_C_2'::regproc, 83 'functest_C_3'::regproc) ORDER BY proname; 84 85-- 86-- LEAKPROOF 87-- 88CREATE FUNCTION functest_E_1(int) RETURNS bool LANGUAGE 'sql' 89 AS 'SELECT $1 > 100'; 90CREATE FUNCTION functest_E_2(int) RETURNS bool LANGUAGE 'sql' 91 LEAKPROOF AS 'SELECT $1 > 100'; 92SELECT proname, proleakproof FROM pg_proc 93 WHERE oid in ('functest_E_1'::regproc, 94 'functest_E_2'::regproc) ORDER BY proname; 95 96ALTER FUNCTION functest_E_1(int) LEAKPROOF; 97ALTER FUNCTION functest_E_2(int) STABLE; -- unrelated change, no effect 98SELECT proname, proleakproof FROM pg_proc 99 WHERE oid in ('functest_E_1'::regproc, 100 'functest_E_2'::regproc) ORDER BY proname; 101 102ALTER FUNCTION functest_E_2(int) NOT LEAKPROOF; -- remove leakproof attribute 103SELECT proname, proleakproof FROM pg_proc 104 WHERE oid in ('functest_E_1'::regproc, 105 'functest_E_2'::regproc) ORDER BY proname; 106 107-- it takes superuser privilege to turn on leakproof, but not to turn off 108ALTER FUNCTION functest_E_1(int) OWNER TO regress_unpriv_user; 109ALTER FUNCTION functest_E_2(int) OWNER TO regress_unpriv_user; 110 111SET SESSION AUTHORIZATION regress_unpriv_user; 112SET search_path TO temp_func_test, public; 113ALTER FUNCTION functest_E_1(int) NOT LEAKPROOF; 114ALTER FUNCTION functest_E_2(int) LEAKPROOF; 115 116CREATE FUNCTION functest_E_3(int) RETURNS bool LANGUAGE 'sql' 117 LEAKPROOF AS 'SELECT $1 < 200'; -- fail 118 119RESET SESSION AUTHORIZATION; 120 121-- 122-- CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT 123-- 124CREATE FUNCTION functest_F_1(int) RETURNS bool LANGUAGE 'sql' 125 AS 'SELECT $1 > 50'; 126CREATE FUNCTION functest_F_2(int) RETURNS bool LANGUAGE 'sql' 127 CALLED ON NULL INPUT AS 'SELECT $1 = 50'; 128CREATE FUNCTION functest_F_3(int) RETURNS bool LANGUAGE 'sql' 129 RETURNS NULL ON NULL INPUT AS 'SELECT $1 < 50'; 130CREATE FUNCTION functest_F_4(int) RETURNS bool LANGUAGE 'sql' 131 STRICT AS 'SELECT $1 = 50'; 132SELECT proname, proisstrict FROM pg_proc 133 WHERE oid in ('functest_F_1'::regproc, 134 'functest_F_2'::regproc, 135 'functest_F_3'::regproc, 136 'functest_F_4'::regproc) ORDER BY proname; 137 138ALTER FUNCTION functest_F_1(int) IMMUTABLE; -- unrelated change, no effect 139ALTER FUNCTION functest_F_2(int) STRICT; 140ALTER FUNCTION functest_F_3(int) CALLED ON NULL INPUT; 141SELECT proname, proisstrict FROM pg_proc 142 WHERE oid in ('functest_F_1'::regproc, 143 'functest_F_2'::regproc, 144 'functest_F_3'::regproc, 145 'functest_F_4'::regproc) ORDER BY proname; 146 147 148-- pg_get_functiondef tests 149 150SELECT pg_get_functiondef('functest_A_1'::regproc); 151SELECT pg_get_functiondef('functest_B_3'::regproc); 152SELECT pg_get_functiondef('functest_C_3'::regproc); 153SELECT pg_get_functiondef('functest_F_2'::regproc); 154 155 156-- 157-- SQL-standard body 158-- 159CREATE FUNCTION functest_S_1(a text, b date) RETURNS boolean 160 LANGUAGE SQL 161 RETURN a = 'abcd' AND b > '2001-01-01'; 162CREATE FUNCTION functest_S_2(a text[]) RETURNS int 163 RETURN a[1]::int; 164CREATE FUNCTION functest_S_3() RETURNS boolean 165 RETURN false; 166CREATE FUNCTION functest_S_3a() RETURNS boolean 167 BEGIN ATOMIC 168 ;;RETURN false;; 169 END; 170 171CREATE FUNCTION functest_S_10(a text, b date) RETURNS boolean 172 LANGUAGE SQL 173 BEGIN ATOMIC 174 SELECT a = 'abcd' AND b > '2001-01-01'; 175 END; 176 177CREATE FUNCTION functest_S_13() RETURNS boolean 178 BEGIN ATOMIC 179 SELECT 1; 180 SELECT false; 181 END; 182 183-- error: duplicate function body 184CREATE FUNCTION functest_S_xxx(x int) RETURNS int 185 LANGUAGE SQL 186 AS $$ SELECT x * 2 $$ 187 RETURN x * 3; 188 189-- polymorphic arguments not allowed in this form 190CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement 191 LANGUAGE SQL 192 RETURN x[1]; 193 194-- check reporting of parse-analysis errors 195CREATE FUNCTION functest_S_xx(x date) RETURNS boolean 196 LANGUAGE SQL 197 RETURN x > 1; 198 199-- tricky parsing 200CREATE FUNCTION functest_S_15(x int) RETURNS boolean 201LANGUAGE SQL 202BEGIN ATOMIC 203 select case when x % 2 = 0 then true else false end; 204END; 205 206SELECT functest_S_1('abcd', '2020-01-01'); 207SELECT functest_S_2(ARRAY['1', '2', '3']); 208SELECT functest_S_3(); 209 210SELECT functest_S_10('abcd', '2020-01-01'); 211SELECT functest_S_13(); 212 213SELECT pg_get_functiondef('functest_S_1'::regproc); 214SELECT pg_get_functiondef('functest_S_2'::regproc); 215SELECT pg_get_functiondef('functest_S_3'::regproc); 216SELECT pg_get_functiondef('functest_S_3a'::regproc); 217SELECT pg_get_functiondef('functest_S_10'::regproc); 218SELECT pg_get_functiondef('functest_S_13'::regproc); 219SELECT pg_get_functiondef('functest_S_15'::regproc); 220 221-- test with views 222CREATE TABLE functest3 (a int); 223INSERT INTO functest3 VALUES (1), (2); 224CREATE VIEW functestv3 AS SELECT * FROM functest3; 225 226CREATE FUNCTION functest_S_14() RETURNS bigint 227 RETURN (SELECT count(*) FROM functestv3); 228 229SELECT functest_S_14(); 230 231DROP TABLE functest3 CASCADE; 232 233 234-- information_schema tests 235 236CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo') 237 RETURNS int 238 LANGUAGE SQL 239 AS 'SELECT $1 + $2'; 240 241CREATE FUNCTION functest_IS_2(out a int, b int default 1) 242 RETURNS int 243 LANGUAGE SQL 244 AS 'SELECT $1'; 245 246CREATE FUNCTION functest_IS_3(a int default 1, out b int) 247 RETURNS int 248 LANGUAGE SQL 249 AS 'SELECT $1'; 250 251SELECT routine_name, ordinal_position, parameter_name, parameter_default 252 FROM information_schema.parameters JOIN information_schema.routines USING (specific_schema, specific_name) 253 WHERE routine_schema = 'temp_func_test' AND routine_name ~ '^functest_is_' 254 ORDER BY 1, 2; 255 256DROP FUNCTION functest_IS_1(int, int, text), functest_IS_2(int), functest_IS_3(int); 257 258-- routine usage views 259 260CREATE FUNCTION functest_IS_4a() RETURNS int LANGUAGE SQL AS 'SELECT 1'; 261CREATE FUNCTION functest_IS_4b(x int DEFAULT functest_IS_4a()) RETURNS int LANGUAGE SQL AS 'SELECT x'; 262 263CREATE SEQUENCE functest1; 264CREATE FUNCTION functest_IS_5(x int DEFAULT nextval('functest1')) 265 RETURNS int 266 LANGUAGE SQL 267 AS 'SELECT x'; 268 269CREATE FUNCTION functest_IS_6() 270 RETURNS int 271 LANGUAGE SQL 272 RETURN nextval('functest1'); 273 274CREATE TABLE functest2 (a int, b int); 275 276CREATE FUNCTION functest_IS_7() 277 RETURNS int 278 LANGUAGE SQL 279 RETURN (SELECT count(a) FROM functest2); 280 281SELECT r0.routine_name, r1.routine_name 282 FROM information_schema.routine_routine_usage rru 283 JOIN information_schema.routines r0 ON r0.specific_name = rru.specific_name 284 JOIN information_schema.routines r1 ON r1.specific_name = rru.routine_name 285 WHERE r0.routine_schema = 'temp_func_test' AND 286 r1.routine_schema = 'temp_func_test' 287 ORDER BY 1, 2; 288SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usage 289 WHERE routine_schema = 'temp_func_test' 290 ORDER BY 1, 2; 291SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage 292 WHERE routine_schema = 'temp_func_test' 293 ORDER BY 1, 2; 294SELECT routine_name, table_name FROM information_schema.routine_table_usage 295 WHERE routine_schema = 'temp_func_test' 296 ORDER BY 1, 2; 297 298DROP FUNCTION functest_IS_4a CASCADE; 299DROP SEQUENCE functest1 CASCADE; 300DROP TABLE functest2 CASCADE; 301 302 303-- overload 304CREATE FUNCTION functest_B_2(bigint) RETURNS bool LANGUAGE 'sql' 305 IMMUTABLE AS 'SELECT $1 > 0'; 306 307DROP FUNCTION functest_b_1; 308DROP FUNCTION functest_b_1; -- error, not found 309DROP FUNCTION functest_b_2; -- error, ambiguous 310 311 312-- CREATE OR REPLACE tests 313 314CREATE FUNCTION functest1(a int) RETURNS int LANGUAGE SQL AS 'SELECT $1'; 315CREATE OR REPLACE FUNCTION functest1(a int) RETURNS int LANGUAGE SQL WINDOW AS 'SELECT $1'; 316CREATE OR REPLACE PROCEDURE functest1(a int) LANGUAGE SQL AS 'SELECT $1'; 317DROP FUNCTION functest1(a int); 318 319 320-- inlining of set-returning functions 321 322CREATE TABLE functest3 (a int); 323INSERT INTO functest3 VALUES (1), (2), (3); 324 325CREATE FUNCTION functest_sri1() RETURNS SETOF int 326LANGUAGE SQL 327STABLE 328AS ' 329 SELECT * FROM functest3; 330'; 331 332SELECT * FROM functest_sri1(); 333EXPLAIN (verbose, costs off) SELECT * FROM functest_sri1(); 334 335CREATE FUNCTION functest_sri2() RETURNS SETOF int 336LANGUAGE SQL 337STABLE 338BEGIN ATOMIC 339 SELECT * FROM functest3; 340END; 341 342SELECT * FROM functest_sri2(); 343EXPLAIN (verbose, costs off) SELECT * FROM functest_sri2(); 344 345DROP TABLE functest3 CASCADE; 346 347 348-- Check behavior of VOID-returning SQL functions 349 350CREATE FUNCTION voidtest1(a int) RETURNS VOID LANGUAGE SQL AS 351$$ SELECT a + 1 $$; 352SELECT voidtest1(42); 353 354CREATE FUNCTION voidtest2(a int, b int) RETURNS VOID LANGUAGE SQL AS 355$$ SELECT voidtest1(a + b) $$; 356SELECT voidtest2(11,22); 357 358-- currently, we can inline voidtest2 but not voidtest1 359EXPLAIN (verbose, costs off) SELECT voidtest2(11,22); 360 361CREATE TEMP TABLE sometable(f1 int); 362 363CREATE FUNCTION voidtest3(a int) RETURNS VOID LANGUAGE SQL AS 364$$ INSERT INTO sometable VALUES(a + 1) $$; 365SELECT voidtest3(17); 366 367CREATE FUNCTION voidtest4(a int) RETURNS VOID LANGUAGE SQL AS 368$$ INSERT INTO sometable VALUES(a - 1) RETURNING f1 $$; 369SELECT voidtest4(39); 370 371TABLE sometable; 372 373CREATE FUNCTION voidtest5(a int) RETURNS SETOF VOID LANGUAGE SQL AS 374$$ SELECT generate_series(1, a) $$ STABLE; 375SELECT * FROM voidtest5(3); 376 377-- Cleanup 378DROP SCHEMA temp_func_test CASCADE; 379DROP USER regress_unpriv_user; 380RESET search_path; 381