1-- 2-- CREATE FUNCTION 3-- 4-- sanity check of pg_proc catalog to the given parameters 5-- 6CREATE USER regress_unpriv_user; 7 8CREATE SCHEMA temp_func_test; 9GRANT ALL ON SCHEMA temp_func_test TO public; 10 11SET search_path TO temp_func_test, public; 12 13-- 14-- ARGUMENT and RETURN TYPES 15-- 16CREATE FUNCTION functest_A_1(text, date) RETURNS bool LANGUAGE 'sql' 17 AS 'SELECT $1 = ''abcd'' AND $2 > ''2001-01-01'''; 18CREATE FUNCTION functest_A_2(text[]) RETURNS int LANGUAGE 'sql' 19 AS 'SELECT $1[0]::int'; 20CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql' 21 AS 'SELECT false'; 22SELECT proname, prorettype::regtype, proargtypes::regtype[] FROM pg_proc 23 WHERE oid in ('functest_A_1'::regproc, 24 'functest_A_2'::regproc, 25 'functest_A_3'::regproc) ORDER BY proname; 26 27-- 28-- IMMUTABLE | STABLE | VOLATILE 29-- 30CREATE FUNCTION functest_B_1(int) RETURNS bool LANGUAGE 'sql' 31 AS 'SELECT $1 > 0'; 32CREATE FUNCTION functest_B_2(int) RETURNS bool LANGUAGE 'sql' 33 IMMUTABLE AS 'SELECT $1 > 0'; 34CREATE FUNCTION functest_B_3(int) RETURNS bool LANGUAGE 'sql' 35 STABLE AS 'SELECT $1 = 0'; 36CREATE FUNCTION functest_B_4(int) RETURNS bool LANGUAGE 'sql' 37 VOLATILE AS 'SELECT $1 < 0'; 38SELECT proname, provolatile FROM pg_proc 39 WHERE oid in ('functest_B_1'::regproc, 40 'functest_B_2'::regproc, 41 'functest_B_3'::regproc, 42 'functest_B_4'::regproc) ORDER BY proname; 43 44ALTER FUNCTION functest_B_2(int) VOLATILE; 45ALTER FUNCTION functest_B_3(int) COST 100; -- unrelated change, no effect 46SELECT proname, provolatile FROM pg_proc 47 WHERE oid in ('functest_B_1'::regproc, 48 'functest_B_2'::regproc, 49 'functest_B_3'::regproc, 50 'functest_B_4'::regproc) ORDER BY proname; 51 52-- 53-- SECURITY DEFINER | INVOKER 54-- 55CREATE FUNCTION functext_C_1(int) RETURNS bool LANGUAGE 'sql' 56 AS 'SELECT $1 > 0'; 57CREATE FUNCTION functext_C_2(int) RETURNS bool LANGUAGE 'sql' 58 SECURITY DEFINER AS 'SELECT $1 = 0'; 59CREATE FUNCTION functext_C_3(int) RETURNS bool LANGUAGE 'sql' 60 SECURITY INVOKER AS 'SELECT $1 < 0'; 61SELECT proname, prosecdef FROM pg_proc 62 WHERE oid in ('functext_C_1'::regproc, 63 'functext_C_2'::regproc, 64 'functext_C_3'::regproc) ORDER BY proname; 65 66ALTER FUNCTION functext_C_1(int) IMMUTABLE; -- unrelated change, no effect 67ALTER FUNCTION functext_C_2(int) SECURITY INVOKER; 68ALTER FUNCTION functext_C_3(int) SECURITY DEFINER; 69SELECT proname, prosecdef FROM pg_proc 70 WHERE oid in ('functext_C_1'::regproc, 71 'functext_C_2'::regproc, 72 'functext_C_3'::regproc) ORDER BY proname; 73 74-- 75-- LEAKPROOF 76-- 77CREATE FUNCTION functext_E_1(int) RETURNS bool LANGUAGE 'sql' 78 AS 'SELECT $1 > 100'; 79CREATE FUNCTION functext_E_2(int) RETURNS bool LANGUAGE 'sql' 80 LEAKPROOF AS 'SELECT $1 > 100'; 81SELECT proname, proleakproof FROM pg_proc 82 WHERE oid in ('functext_E_1'::regproc, 83 'functext_E_2'::regproc) ORDER BY proname; 84 85ALTER FUNCTION functext_E_1(int) LEAKPROOF; 86ALTER FUNCTION functext_E_2(int) STABLE; -- unrelated change, no effect 87SELECT proname, proleakproof FROM pg_proc 88 WHERE oid in ('functext_E_1'::regproc, 89 'functext_E_2'::regproc) ORDER BY proname; 90 91ALTER FUNCTION functext_E_2(int) NOT LEAKPROOF; -- remove leakproog attribute 92SELECT proname, proleakproof FROM pg_proc 93 WHERE oid in ('functext_E_1'::regproc, 94 'functext_E_2'::regproc) ORDER BY proname; 95 96-- it takes superuser privilege to turn on leakproof, but not for turn off 97ALTER FUNCTION functext_E_1(int) OWNER TO regress_unpriv_user; 98ALTER FUNCTION functext_E_2(int) OWNER TO regress_unpriv_user; 99 100SET SESSION AUTHORIZATION regress_unpriv_user; 101SET search_path TO temp_func_test, public; 102ALTER FUNCTION functext_E_1(int) NOT LEAKPROOF; 103ALTER FUNCTION functext_E_2(int) LEAKPROOF; 104 105CREATE FUNCTION functext_E_3(int) RETURNS bool LANGUAGE 'sql' 106 LEAKPROOF AS 'SELECT $1 < 200'; -- failed 107 108RESET SESSION AUTHORIZATION; 109 110-- 111-- CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT 112-- 113CREATE FUNCTION functext_F_1(int) RETURNS bool LANGUAGE 'sql' 114 AS 'SELECT $1 > 50'; 115CREATE FUNCTION functext_F_2(int) RETURNS bool LANGUAGE 'sql' 116 CALLED ON NULL INPUT AS 'SELECT $1 = 50'; 117CREATE FUNCTION functext_F_3(int) RETURNS bool LANGUAGE 'sql' 118 RETURNS NULL ON NULL INPUT AS 'SELECT $1 < 50'; 119CREATE FUNCTION functext_F_4(int) RETURNS bool LANGUAGE 'sql' 120 STRICT AS 'SELECT $1 = 50'; 121SELECT proname, proisstrict FROM pg_proc 122 WHERE oid in ('functext_F_1'::regproc, 123 'functext_F_2'::regproc, 124 'functext_F_3'::regproc, 125 'functext_F_4'::regproc) ORDER BY proname; 126 127ALTER FUNCTION functext_F_1(int) IMMUTABLE; -- unrelated change, no effect 128ALTER FUNCTION functext_F_2(int) STRICT; 129ALTER FUNCTION functext_F_3(int) CALLED ON NULL INPUT; 130SELECT proname, proisstrict FROM pg_proc 131 WHERE oid in ('functext_F_1'::regproc, 132 'functext_F_2'::regproc, 133 'functext_F_3'::regproc, 134 'functext_F_4'::regproc) ORDER BY proname; 135 136 137-- information_schema tests 138 139CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo') 140 RETURNS int 141 LANGUAGE SQL 142 AS 'SELECT $1 + $2'; 143 144CREATE FUNCTION functest_IS_2(out a int, b int default 1) 145 RETURNS int 146 LANGUAGE SQL 147 AS 'SELECT $1'; 148 149CREATE FUNCTION functest_IS_3(a int default 1, out b int) 150 RETURNS int 151 LANGUAGE SQL 152 AS 'SELECT $1'; 153 154SELECT routine_name, ordinal_position, parameter_name, parameter_default 155 FROM information_schema.parameters JOIN information_schema.routines USING (specific_schema, specific_name) 156 WHERE routine_schema = 'temp_func_test' AND routine_name ~ '^functest_is_' 157 ORDER BY 1, 2; 158 159 160-- Cleanups 161DROP SCHEMA temp_func_test CASCADE; 162DROP USER regress_unpriv_user; 163RESET search_path; 164