diff options
Diffstat (limited to 'src/test/regress/expected/create_function_3.out')
| -rw-r--r-- | src/test/regress/expected/create_function_3.out | 368 |
1 files changed, 368 insertions, 0 deletions
diff --git a/src/test/regress/expected/create_function_3.out b/src/test/regress/expected/create_function_3.out new file mode 100644 index 0000000..ba260df --- /dev/null +++ b/src/test/regress/expected/create_function_3.out @@ -0,0 +1,368 @@ +-- +-- CREATE FUNCTION +-- +-- Assorted tests using SQL-language functions +-- +-- All objects made in this test are in temp_func_test schema +CREATE USER regress_unpriv_user; +CREATE SCHEMA temp_func_test; +GRANT ALL ON SCHEMA temp_func_test TO public; +SET search_path TO temp_func_test, public; +-- +-- Make sanity checks on the pg_proc entries created by CREATE FUNCTION +-- +-- +-- ARGUMENT and RETURN TYPES +-- +CREATE FUNCTION functest_A_1(text, date) RETURNS bool LANGUAGE 'sql' + AS 'SELECT $1 = ''abcd'' AND $2 > ''2001-01-01'''; +CREATE FUNCTION functest_A_2(text[]) RETURNS int LANGUAGE 'sql' + AS 'SELECT $1[0]::int'; +CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql' + AS 'SELECT false'; +SELECT proname, prorettype::regtype, proargtypes::regtype[] FROM pg_proc + WHERE oid in ('functest_A_1'::regproc, + 'functest_A_2'::regproc, + 'functest_A_3'::regproc) ORDER BY proname; + proname | prorettype | proargtypes +--------------+------------+------------------- + functest_a_1 | boolean | [0:1]={text,date} + functest_a_2 | integer | [0:0]={text[]} + functest_a_3 | boolean | {} +(3 rows) + +-- +-- IMMUTABLE | STABLE | VOLATILE +-- +CREATE FUNCTION functest_B_1(int) RETURNS bool LANGUAGE 'sql' + AS 'SELECT $1 > 0'; +CREATE FUNCTION functest_B_2(int) RETURNS bool LANGUAGE 'sql' + IMMUTABLE AS 'SELECT $1 > 0'; +CREATE FUNCTION functest_B_3(int) RETURNS bool LANGUAGE 'sql' + STABLE AS 'SELECT $1 = 0'; +CREATE FUNCTION functest_B_4(int) RETURNS bool LANGUAGE 'sql' + VOLATILE AS 'SELECT $1 < 0'; +SELECT proname, provolatile FROM pg_proc + WHERE oid in ('functest_B_1'::regproc, + 'functest_B_2'::regproc, + 'functest_B_3'::regproc, + 'functest_B_4'::regproc) ORDER BY proname; + proname | provolatile +--------------+------------- + functest_b_1 | v + functest_b_2 | i + functest_b_3 | s + functest_b_4 | v +(4 rows) + +ALTER FUNCTION functest_B_2(int) VOLATILE; +ALTER FUNCTION functest_B_3(int) COST 100; -- unrelated change, no effect +SELECT proname, provolatile FROM pg_proc + WHERE oid in ('functest_B_1'::regproc, + 'functest_B_2'::regproc, + 'functest_B_3'::regproc, + 'functest_B_4'::regproc) ORDER BY proname; + proname | provolatile +--------------+------------- + functest_b_1 | v + functest_b_2 | v + functest_b_3 | s + functest_b_4 | v +(4 rows) + +-- +-- SECURITY DEFINER | INVOKER +-- +CREATE FUNCTION functest_C_1(int) RETURNS bool LANGUAGE 'sql' + AS 'SELECT $1 > 0'; +CREATE FUNCTION functest_C_2(int) RETURNS bool LANGUAGE 'sql' + SECURITY DEFINER AS 'SELECT $1 = 0'; +CREATE FUNCTION functest_C_3(int) RETURNS bool LANGUAGE 'sql' + SECURITY INVOKER AS 'SELECT $1 < 0'; +SELECT proname, prosecdef FROM pg_proc + WHERE oid in ('functest_C_1'::regproc, + 'functest_C_2'::regproc, + 'functest_C_3'::regproc) ORDER BY proname; + proname | prosecdef +--------------+----------- + functest_c_1 | f + functest_c_2 | t + functest_c_3 | f +(3 rows) + +ALTER FUNCTION functest_C_1(int) IMMUTABLE; -- unrelated change, no effect +ALTER FUNCTION functest_C_2(int) SECURITY INVOKER; +ALTER FUNCTION functest_C_3(int) SECURITY DEFINER; +SELECT proname, prosecdef FROM pg_proc + WHERE oid in ('functest_C_1'::regproc, + 'functest_C_2'::regproc, + 'functest_C_3'::regproc) ORDER BY proname; + proname | prosecdef +--------------+----------- + functest_c_1 | f + functest_c_2 | f + functest_c_3 | t +(3 rows) + +-- +-- LEAKPROOF +-- +CREATE FUNCTION functest_E_1(int) RETURNS bool LANGUAGE 'sql' + AS 'SELECT $1 > 100'; +CREATE FUNCTION functest_E_2(int) RETURNS bool LANGUAGE 'sql' + LEAKPROOF AS 'SELECT $1 > 100'; +SELECT proname, proleakproof FROM pg_proc + WHERE oid in ('functest_E_1'::regproc, + 'functest_E_2'::regproc) ORDER BY proname; + proname | proleakproof +--------------+-------------- + functest_e_1 | f + functest_e_2 | t +(2 rows) + +ALTER FUNCTION functest_E_1(int) LEAKPROOF; +ALTER FUNCTION functest_E_2(int) STABLE; -- unrelated change, no effect +SELECT proname, proleakproof FROM pg_proc + WHERE oid in ('functest_E_1'::regproc, + 'functest_E_2'::regproc) ORDER BY proname; + proname | proleakproof +--------------+-------------- + functest_e_1 | t + functest_e_2 | t +(2 rows) + +ALTER FUNCTION functest_E_2(int) NOT LEAKPROOF; -- remove leakproof attribute +SELECT proname, proleakproof FROM pg_proc + WHERE oid in ('functest_E_1'::regproc, + 'functest_E_2'::regproc) ORDER BY proname; + proname | proleakproof +--------------+-------------- + functest_e_1 | t + functest_e_2 | f +(2 rows) + +-- it takes superuser privilege to turn on leakproof, but not to turn off +ALTER FUNCTION functest_E_1(int) OWNER TO regress_unpriv_user; +ALTER FUNCTION functest_E_2(int) OWNER TO regress_unpriv_user; +SET SESSION AUTHORIZATION regress_unpriv_user; +SET search_path TO temp_func_test, public; +ALTER FUNCTION functest_E_1(int) NOT LEAKPROOF; +ALTER FUNCTION functest_E_2(int) LEAKPROOF; +ERROR: only superuser can define a leakproof function +CREATE FUNCTION functest_E_3(int) RETURNS bool LANGUAGE 'sql' + LEAKPROOF AS 'SELECT $1 < 200'; -- fail +ERROR: only superuser can define a leakproof function +RESET SESSION AUTHORIZATION; +-- +-- CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT +-- +CREATE FUNCTION functest_F_1(int) RETURNS bool LANGUAGE 'sql' + AS 'SELECT $1 > 50'; +CREATE FUNCTION functest_F_2(int) RETURNS bool LANGUAGE 'sql' + CALLED ON NULL INPUT AS 'SELECT $1 = 50'; +CREATE FUNCTION functest_F_3(int) RETURNS bool LANGUAGE 'sql' + RETURNS NULL ON NULL INPUT AS 'SELECT $1 < 50'; +CREATE FUNCTION functest_F_4(int) RETURNS bool LANGUAGE 'sql' + STRICT AS 'SELECT $1 = 50'; +SELECT proname, proisstrict FROM pg_proc + WHERE oid in ('functest_F_1'::regproc, + 'functest_F_2'::regproc, + 'functest_F_3'::regproc, + 'functest_F_4'::regproc) ORDER BY proname; + proname | proisstrict +--------------+------------- + functest_f_1 | f + functest_f_2 | f + functest_f_3 | t + functest_f_4 | t +(4 rows) + +ALTER FUNCTION functest_F_1(int) IMMUTABLE; -- unrelated change, no effect +ALTER FUNCTION functest_F_2(int) STRICT; +ALTER FUNCTION functest_F_3(int) CALLED ON NULL INPUT; +SELECT proname, proisstrict FROM pg_proc + WHERE oid in ('functest_F_1'::regproc, + 'functest_F_2'::regproc, + 'functest_F_3'::regproc, + 'functest_F_4'::regproc) ORDER BY proname; + proname | proisstrict +--------------+------------- + functest_f_1 | f + functest_f_2 | t + functest_f_3 | f + functest_f_4 | t +(4 rows) + +-- pg_get_functiondef tests +SELECT pg_get_functiondef('functest_A_1'::regproc); + pg_get_functiondef +-------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_a_1(text, date)+ + RETURNS boolean + + LANGUAGE sql + + AS $function$SELECT $1 = 'abcd' AND $2 > '2001-01-01'$function$ + + +(1 row) + +SELECT pg_get_functiondef('functest_B_3'::regproc); + pg_get_functiondef +----------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_b_3(integer)+ + RETURNS boolean + + LANGUAGE sql + + STABLE + + AS $function$SELECT $1 = 0$function$ + + +(1 row) + +SELECT pg_get_functiondef('functest_C_3'::regproc); + pg_get_functiondef +----------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_c_3(integer)+ + RETURNS boolean + + LANGUAGE sql + + SECURITY DEFINER + + AS $function$SELECT $1 < 0$function$ + + +(1 row) + +SELECT pg_get_functiondef('functest_F_2'::regproc); + pg_get_functiondef +----------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_f_2(integer)+ + RETURNS boolean + + LANGUAGE sql + + STRICT + + AS $function$SELECT $1 = 50$function$ + + +(1 row) + +-- information_schema tests +CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo') + RETURNS int + LANGUAGE SQL + AS 'SELECT $1 + $2'; +CREATE FUNCTION functest_IS_2(out a int, b int default 1) + RETURNS int + LANGUAGE SQL + AS 'SELECT $1'; +CREATE FUNCTION functest_IS_3(a int default 1, out b int) + RETURNS int + LANGUAGE SQL + AS 'SELECT $1'; +SELECT routine_name, ordinal_position, parameter_name, parameter_default + FROM information_schema.parameters JOIN information_schema.routines USING (specific_schema, specific_name) + WHERE routine_schema = 'temp_func_test' AND routine_name ~ '^functest_is_' + ORDER BY 1, 2; + routine_name | ordinal_position | parameter_name | parameter_default +---------------+------------------+----------------+------------------- + functest_is_1 | 1 | a | + functest_is_1 | 2 | b | 1 + functest_is_1 | 3 | c | 'foo'::text + functest_is_2 | 1 | a | + functest_is_2 | 2 | b | 1 + functest_is_3 | 1 | a | 1 + functest_is_3 | 2 | b | +(7 rows) + +DROP FUNCTION functest_IS_1(int, int, text), functest_IS_2(int), functest_IS_3(int); +-- overload +CREATE FUNCTION functest_B_2(bigint) RETURNS bool LANGUAGE 'sql' + IMMUTABLE AS 'SELECT $1 > 0'; +DROP FUNCTION functest_b_1; +DROP FUNCTION functest_b_1; -- error, not found +ERROR: could not find a function named "functest_b_1" +DROP FUNCTION functest_b_2; -- error, ambiguous +ERROR: function name "functest_b_2" is not unique +HINT: Specify the argument list to select the function unambiguously. +-- CREATE OR REPLACE tests +CREATE FUNCTION functest1(a int) RETURNS int LANGUAGE SQL AS 'SELECT $1'; +CREATE OR REPLACE FUNCTION functest1(a int) RETURNS int LANGUAGE SQL WINDOW AS 'SELECT $1'; +ERROR: cannot change routine kind +DETAIL: "functest1" is a function. +CREATE OR REPLACE PROCEDURE functest1(a int) LANGUAGE SQL AS 'SELECT $1'; +ERROR: cannot change routine kind +DETAIL: "functest1" is a function. +DROP FUNCTION functest1(a int); +-- Check behavior of VOID-returning SQL functions +CREATE FUNCTION voidtest1(a int) RETURNS VOID LANGUAGE SQL AS +$$ SELECT a + 1 $$; +SELECT voidtest1(42); + voidtest1 +----------- + +(1 row) + +CREATE FUNCTION voidtest2(a int, b int) RETURNS VOID LANGUAGE SQL AS +$$ SELECT voidtest1(a + b) $$; +SELECT voidtest2(11,22); + voidtest2 +----------- + +(1 row) + +-- currently, we can inline voidtest2 but not voidtest1 +EXPLAIN (verbose, costs off) SELECT voidtest2(11,22); + QUERY PLAN +------------------------- + Result + Output: voidtest1(33) +(2 rows) + +CREATE TEMP TABLE sometable(f1 int); +CREATE FUNCTION voidtest3(a int) RETURNS VOID LANGUAGE SQL AS +$$ INSERT INTO sometable VALUES(a + 1) $$; +SELECT voidtest3(17); + voidtest3 +----------- + +(1 row) + +CREATE FUNCTION voidtest4(a int) RETURNS VOID LANGUAGE SQL AS +$$ INSERT INTO sometable VALUES(a - 1) RETURNING f1 $$; +SELECT voidtest4(39); + voidtest4 +----------- + +(1 row) + +TABLE sometable; + f1 +---- + 18 + 38 +(2 rows) + +CREATE FUNCTION voidtest5(a int) RETURNS SETOF VOID LANGUAGE SQL AS +$$ SELECT generate_series(1, a) $$ STABLE; +SELECT * FROM voidtest5(3); + voidtest5 +----------- +(0 rows) + +-- Cleanup +DROP SCHEMA temp_func_test CASCADE; +NOTICE: drop cascades to 21 other objects +DETAIL: drop cascades to function functest_a_1(text,date) +drop cascades to function functest_a_2(text[]) +drop cascades to function functest_a_3() +drop cascades to function functest_b_2(integer) +drop cascades to function functest_b_3(integer) +drop cascades to function functest_b_4(integer) +drop cascades to function functest_c_1(integer) +drop cascades to function functest_c_2(integer) +drop cascades to function functest_c_3(integer) +drop cascades to function functest_e_1(integer) +drop cascades to function functest_e_2(integer) +drop cascades to function functest_f_1(integer) +drop cascades to function functest_f_2(integer) +drop cascades to function functest_f_3(integer) +drop cascades to function functest_f_4(integer) +drop cascades to function functest_b_2(bigint) +drop cascades to function voidtest1(integer) +drop cascades to function voidtest2(integer,integer) +drop cascades to function voidtest3(integer) +drop cascades to function voidtest4(integer) +drop cascades to function voidtest5(integer) +DROP USER regress_unpriv_user; +RESET search_path; |