From 2da896182ce11240774af6c4d769777f90a09536 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 8 Feb 2022 15:40:08 -0500 Subject: Rename create_function_N test scripts for clarity. Rename create_function_0 to create_function_c, and create_function_3 to create_function_sql, to establish their charters more clearly. This should also reduce confusion versus our underscore-digit convention for naming variant expected-files. I separated this from the previous commit on the premise that keeping the renaming distinct might make "git blame" tracking easier. Discussion: https://postgr.es/m/1114748.1640383217@sss.pgh.pa.us --- src/test/regress/expected/create_function_0.out | 36 -- src/test/regress/expected/create_function_3.out | 726 ---------------------- src/test/regress/expected/create_function_c.out | 36 ++ src/test/regress/expected/create_function_sql.out | 726 ++++++++++++++++++++++ src/test/regress/parallel_schedule | 4 +- src/test/regress/sql/create_function_0.sql | 35 -- src/test/regress/sql/create_function_3.sql | 408 ------------ src/test/regress/sql/create_function_c.sql | 35 ++ src/test/regress/sql/create_function_sql.sql | 408 ++++++++++++ 9 files changed, 1207 insertions(+), 1207 deletions(-) delete mode 100644 src/test/regress/expected/create_function_0.out delete mode 100644 src/test/regress/expected/create_function_3.out create mode 100644 src/test/regress/expected/create_function_c.out create mode 100644 src/test/regress/expected/create_function_sql.out delete mode 100644 src/test/regress/sql/create_function_0.sql delete mode 100644 src/test/regress/sql/create_function_3.sql create mode 100644 src/test/regress/sql/create_function_c.sql create mode 100644 src/test/regress/sql/create_function_sql.sql (limited to 'src') diff --git a/src/test/regress/expected/create_function_0.out b/src/test/regress/expected/create_function_0.out deleted file mode 100644 index ed674af971c..00000000000 --- a/src/test/regress/expected/create_function_0.out +++ /dev/null @@ -1,36 +0,0 @@ --- --- CREATE_FUNCTION_0 --- --- This script used to create C functions for other scripts to use. --- But to get rid of the ordering dependencies that caused, such --- functions are now made either in test_setup.sql or in the specific --- test script that needs them. All that remains here is error cases. --- directory path and dlsuffix are passed to us in environment variables -\getenv libdir PG_LIBDIR -\getenv dlsuffix PG_DLSUFFIX -\set regresslib :libdir '/regress' :dlsuffix --- --- Check LOAD command. (The alternative of implicitly loading the library --- is checked in many other test scripts.) --- -LOAD :'regresslib'; --- Things that shouldn't work: -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C - AS 'nosuchfile'; -ERROR: could not access file "nosuchfile": No such file or directory --- To produce stable regression test output, we have to filter the name --- of the regresslib file out of the error message in this test. -\set VERBOSITY sqlstate -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C - AS :'regresslib', 'nosuchsymbol'; -ERROR: 42883 -\set VERBOSITY default -SELECT regexp_replace(:'LAST_ERROR_MESSAGE', 'file ".*"', 'file "..."'); - regexp_replace ------------------------------------------------------- - could not find function "nosuchsymbol" in file "..." -(1 row) - -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE internal - AS 'nosuch'; -ERROR: there is no built-in function named "nosuch" diff --git a/src/test/regress/expected/create_function_3.out b/src/test/regress/expected/create_function_3.out deleted file mode 100644 index 1e2dfd87d30..00000000000 --- a/src/test/regress/expected/create_function_3.out +++ /dev/null @@ -1,726 +0,0 @@ --- --- 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[1]::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) - -SELECT functest_A_1('abcd', '2020-01-01'); - functest_a_1 --------------- - t -(1 row) - -SELECT functest_A_2(ARRAY['1', '2', '3']); - functest_a_2 --------------- - 1 -(1 row) - -SELECT functest_A_3(); - functest_a_3 --------------- - f -(1 row) - --- --- 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) - --- --- SQL-standard body --- -CREATE FUNCTION functest_S_1(a text, b date) RETURNS boolean - LANGUAGE SQL - RETURN a = 'abcd' AND b > '2001-01-01'; -CREATE FUNCTION functest_S_2(a text[]) RETURNS int - RETURN a[1]::int; -CREATE FUNCTION functest_S_3() RETURNS boolean - RETURN false; -CREATE FUNCTION functest_S_3a() RETURNS boolean - BEGIN ATOMIC - ;;RETURN false;; - END; -CREATE FUNCTION functest_S_10(a text, b date) RETURNS boolean - LANGUAGE SQL - BEGIN ATOMIC - SELECT a = 'abcd' AND b > '2001-01-01'; - END; -CREATE FUNCTION functest_S_13() RETURNS boolean - BEGIN ATOMIC - SELECT 1; - SELECT false; - END; --- check display of function argments in sub-SELECT -CREATE TABLE functest1 (i int); -CREATE FUNCTION functest_S_16(a int, b int) RETURNS void - LANGUAGE SQL - BEGIN ATOMIC - INSERT INTO functest1 SELECT a + $2; - END; --- error: duplicate function body -CREATE FUNCTION functest_S_xxx(x int) RETURNS int - LANGUAGE SQL - AS $$ SELECT x * 2 $$ - RETURN x * 3; -ERROR: duplicate function body specified --- polymorphic arguments not allowed in this form -CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement - LANGUAGE SQL - RETURN x[1]; -ERROR: SQL function with unquoted function body cannot have polymorphic arguments --- check reporting of parse-analysis errors -CREATE FUNCTION functest_S_xx(x date) RETURNS boolean - LANGUAGE SQL - RETURN x > 1; -ERROR: operator does not exist: date > integer -LINE 3: RETURN x > 1; - ^ -HINT: No operator matches the given name and argument types. You might need to add explicit type casts. --- tricky parsing -CREATE FUNCTION functest_S_15(x int) RETURNS boolean -LANGUAGE SQL -BEGIN ATOMIC - select case when x % 2 = 0 then true else false end; -END; -SELECT functest_S_1('abcd', '2020-01-01'); - functest_s_1 --------------- - t -(1 row) - -SELECT functest_S_2(ARRAY['1', '2', '3']); - functest_s_2 --------------- - 1 -(1 row) - -SELECT functest_S_3(); - functest_s_3 --------------- - f -(1 row) - -SELECT functest_S_10('abcd', '2020-01-01'); - functest_s_10 ---------------- - t -(1 row) - -SELECT functest_S_13(); - functest_s_13 ---------------- - f -(1 row) - -SELECT pg_get_functiondef('functest_S_1'::regproc); - pg_get_functiondef ------------------------------------------------------------------------- - CREATE OR REPLACE FUNCTION temp_func_test.functest_s_1(a text, b date)+ - RETURNS boolean + - LANGUAGE sql + - RETURN ((a = 'abcd'::text) AND (b > '01-01-2001'::date)) + - -(1 row) - -SELECT pg_get_functiondef('functest_S_2'::regproc); - pg_get_functiondef ------------------------------------------------------------------- - CREATE OR REPLACE FUNCTION temp_func_test.functest_s_2(a text[])+ - RETURNS integer + - LANGUAGE sql + - RETURN ((a)[1])::integer + - -(1 row) - -SELECT pg_get_functiondef('functest_S_3'::regproc); - pg_get_functiondef ----------------------------------------------------------- - CREATE OR REPLACE FUNCTION temp_func_test.functest_s_3()+ - RETURNS boolean + - LANGUAGE sql + - RETURN false + - -(1 row) - -SELECT pg_get_functiondef('functest_S_3a'::regproc); - pg_get_functiondef ------------------------------------------------------------ - CREATE OR REPLACE FUNCTION temp_func_test.functest_s_3a()+ - RETURNS boolean + - LANGUAGE sql + - BEGIN ATOMIC + - RETURN false; + - END + - -(1 row) - -SELECT pg_get_functiondef('functest_S_10'::regproc); - pg_get_functiondef -------------------------------------------------------------------------- - CREATE OR REPLACE FUNCTION temp_func_test.functest_s_10(a text, b date)+ - RETURNS boolean + - LANGUAGE sql + - BEGIN ATOMIC + - SELECT ((a = 'abcd'::text) AND (b > '01-01-2001'::date)); + - END + - -(1 row) - -SELECT pg_get_functiondef('functest_S_13'::regproc); - pg_get_functiondef ------------------------------------------------------------ - CREATE OR REPLACE FUNCTION temp_func_test.functest_s_13()+ - RETURNS boolean + - LANGUAGE sql + - BEGIN ATOMIC + - SELECT 1; + - SELECT false; + - END + - -(1 row) - -SELECT pg_get_functiondef('functest_S_15'::regproc); - pg_get_functiondef --------------------------------------------------------------------- - CREATE OR REPLACE FUNCTION temp_func_test.functest_s_15(x integer)+ - RETURNS boolean + - LANGUAGE sql + - BEGIN ATOMIC + - SELECT + - CASE + - WHEN ((x % 2) = 0) THEN true + - ELSE false + - END AS "case"; + - END + - -(1 row) - -SELECT pg_get_functiondef('functest_S_16'::regproc); - pg_get_functiondef -------------------------------------------------------------------------------- - CREATE OR REPLACE FUNCTION temp_func_test.functest_s_16(a integer, b integer)+ - RETURNS void + - LANGUAGE sql + - BEGIN ATOMIC + - INSERT INTO functest1 (i) SELECT (functest_s_16.a + functest_s_16.b); + - END + - -(1 row) - -DROP TABLE functest1 CASCADE; -NOTICE: drop cascades to function functest_s_16(integer,integer) --- test with views -CREATE TABLE functest3 (a int); -INSERT INTO functest3 VALUES (1), (2); -CREATE VIEW functestv3 AS SELECT * FROM functest3; -CREATE FUNCTION functest_S_14() RETURNS bigint - RETURN (SELECT count(*) FROM functestv3); -SELECT functest_S_14(); - functest_s_14 ---------------- - 2 -(1 row) - -DROP TABLE functest3 CASCADE; -NOTICE: drop cascades to 2 other objects -DETAIL: drop cascades to view functestv3 -drop cascades to function functest_s_14() --- 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); --- routine usage views -CREATE FUNCTION functest_IS_4a() RETURNS int LANGUAGE SQL AS 'SELECT 1'; -CREATE FUNCTION functest_IS_4b(x int DEFAULT functest_IS_4a()) RETURNS int LANGUAGE SQL AS 'SELECT x'; -CREATE SEQUENCE functest1; -CREATE FUNCTION functest_IS_5(x int DEFAULT nextval('functest1')) - RETURNS int - LANGUAGE SQL - AS 'SELECT x'; -CREATE FUNCTION functest_IS_6() - RETURNS int - LANGUAGE SQL - RETURN nextval('functest1'); -CREATE TABLE functest2 (a int, b int); -CREATE FUNCTION functest_IS_7() - RETURNS int - LANGUAGE SQL - RETURN (SELECT count(a) FROM functest2); -SELECT r0.routine_name, r1.routine_name - FROM information_schema.routine_routine_usage rru - JOIN information_schema.routines r0 ON r0.specific_name = rru.specific_name - JOIN information_schema.routines r1 ON r1.specific_name = rru.routine_name - WHERE r0.routine_schema = 'temp_func_test' AND - r1.routine_schema = 'temp_func_test' - ORDER BY 1, 2; - routine_name | routine_name -----------------+---------------- - functest_is_4b | functest_is_4a -(1 row) - -SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usage - WHERE routine_schema = 'temp_func_test' - ORDER BY 1, 2; - routine_name | sequence_name ----------------+--------------- - functest_is_5 | functest1 - functest_is_6 | functest1 -(2 rows) - -SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage - WHERE routine_schema = 'temp_func_test' - ORDER BY 1, 2; - routine_name | table_name | column_name ----------------+------------+------------- - functest_is_7 | functest2 | a -(1 row) - -SELECT routine_name, table_name FROM information_schema.routine_table_usage - WHERE routine_schema = 'temp_func_test' - ORDER BY 1, 2; - routine_name | table_name ----------------+------------ - functest_is_7 | functest2 -(1 row) - -DROP FUNCTION functest_IS_4a CASCADE; -NOTICE: drop cascades to function functest_is_4b(integer) -DROP SEQUENCE functest1 CASCADE; -NOTICE: drop cascades to 2 other objects -DETAIL: drop cascades to function functest_is_5(integer) -drop cascades to function functest_is_6() -DROP TABLE functest2 CASCADE; -NOTICE: drop cascades to function functest_is_7() --- 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); --- inlining of set-returning functions -CREATE TABLE functest3 (a int); -INSERT INTO functest3 VALUES (1), (2), (3); -CREATE FUNCTION functest_sri1() RETURNS SETOF int -LANGUAGE SQL -STABLE -AS ' - SELECT * FROM functest3; -'; -SELECT * FROM functest_sri1(); - functest_sri1 ---------------- - 1 - 2 - 3 -(3 rows) - -EXPLAIN (verbose, costs off) SELECT * FROM functest_sri1(); - QUERY PLAN --------------------------------------- - Seq Scan on temp_func_test.functest3 - Output: functest3.a -(2 rows) - -CREATE FUNCTION functest_sri2() RETURNS SETOF int -LANGUAGE SQL -STABLE -BEGIN ATOMIC - SELECT * FROM functest3; -END; -SELECT * FROM functest_sri2(); - functest_sri2 ---------------- - 1 - 2 - 3 -(3 rows) - -EXPLAIN (verbose, costs off) SELECT * FROM functest_sri2(); - QUERY PLAN --------------------------------------- - Seq Scan on temp_func_test.functest3 - Output: functest3.a -(2 rows) - -DROP TABLE functest3 CASCADE; -NOTICE: drop cascades to function functest_sri2() --- 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) - --- Things that shouldn't work: -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'SELECT ''not an integer'';'; -ERROR: return type mismatch in function declared to return integer -DETAIL: Actual return type is text. -CONTEXT: SQL function "test1" -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'not even SQL'; -ERROR: syntax error at or near "not" -LINE 2: AS 'not even SQL'; - ^ -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'SELECT 1, 2, 3;'; -ERROR: return type mismatch in function declared to return integer -DETAIL: Final statement must return exactly one column. -CONTEXT: SQL function "test1" -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'SELECT $2;'; -ERROR: there is no parameter $2 -LINE 2: AS 'SELECT $2;'; - ^ -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'a', 'b'; -ERROR: only one AS item needed for language "sql" --- Cleanup -DROP SCHEMA temp_func_test CASCADE; -NOTICE: drop cascades to 29 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_s_1(text,date) -drop cascades to function functest_s_2(text[]) -drop cascades to function functest_s_3() -drop cascades to function functest_s_3a() -drop cascades to function functest_s_10(text,date) -drop cascades to function functest_s_13() -drop cascades to function functest_s_15(integer) -drop cascades to function functest_b_2(bigint) -drop cascades to function functest_sri1() -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; diff --git a/src/test/regress/expected/create_function_c.out b/src/test/regress/expected/create_function_c.out new file mode 100644 index 00000000000..2dba9d70349 --- /dev/null +++ b/src/test/regress/expected/create_function_c.out @@ -0,0 +1,36 @@ +-- +-- CREATE_FUNCTION_C +-- +-- This script used to create C functions for other scripts to use. +-- But to get rid of the ordering dependencies that caused, such +-- functions are now made either in test_setup.sql or in the specific +-- test script that needs them. All that remains here is error cases. +-- directory path and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX +\set regresslib :libdir '/regress' :dlsuffix +-- +-- Check LOAD command. (The alternative of implicitly loading the library +-- is checked in many other test scripts.) +-- +LOAD :'regresslib'; +-- Things that shouldn't work: +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C + AS 'nosuchfile'; +ERROR: could not access file "nosuchfile": No such file or directory +-- To produce stable regression test output, we have to filter the name +-- of the regresslib file out of the error message in this test. +\set VERBOSITY sqlstate +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C + AS :'regresslib', 'nosuchsymbol'; +ERROR: 42883 +\set VERBOSITY default +SELECT regexp_replace(:'LAST_ERROR_MESSAGE', 'file ".*"', 'file "..."'); + regexp_replace +------------------------------------------------------ + could not find function "nosuchsymbol" in file "..." +(1 row) + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE internal + AS 'nosuch'; +ERROR: there is no built-in function named "nosuch" diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out new file mode 100644 index 00000000000..f9569554648 --- /dev/null +++ b/src/test/regress/expected/create_function_sql.out @@ -0,0 +1,726 @@ +-- +-- CREATE_FUNCTION_SQL +-- +-- 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[1]::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) + +SELECT functest_A_1('abcd', '2020-01-01'); + functest_a_1 +-------------- + t +(1 row) + +SELECT functest_A_2(ARRAY['1', '2', '3']); + functest_a_2 +-------------- + 1 +(1 row) + +SELECT functest_A_3(); + functest_a_3 +-------------- + f +(1 row) + +-- +-- 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) + +-- +-- SQL-standard body +-- +CREATE FUNCTION functest_S_1(a text, b date) RETURNS boolean + LANGUAGE SQL + RETURN a = 'abcd' AND b > '2001-01-01'; +CREATE FUNCTION functest_S_2(a text[]) RETURNS int + RETURN a[1]::int; +CREATE FUNCTION functest_S_3() RETURNS boolean + RETURN false; +CREATE FUNCTION functest_S_3a() RETURNS boolean + BEGIN ATOMIC + ;;RETURN false;; + END; +CREATE FUNCTION functest_S_10(a text, b date) RETURNS boolean + LANGUAGE SQL + BEGIN ATOMIC + SELECT a = 'abcd' AND b > '2001-01-01'; + END; +CREATE FUNCTION functest_S_13() RETURNS boolean + BEGIN ATOMIC + SELECT 1; + SELECT false; + END; +-- check display of function argments in sub-SELECT +CREATE TABLE functest1 (i int); +CREATE FUNCTION functest_S_16(a int, b int) RETURNS void + LANGUAGE SQL + BEGIN ATOMIC + INSERT INTO functest1 SELECT a + $2; + END; +-- error: duplicate function body +CREATE FUNCTION functest_S_xxx(x int) RETURNS int + LANGUAGE SQL + AS $$ SELECT x * 2 $$ + RETURN x * 3; +ERROR: duplicate function body specified +-- polymorphic arguments not allowed in this form +CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement + LANGUAGE SQL + RETURN x[1]; +ERROR: SQL function with unquoted function body cannot have polymorphic arguments +-- check reporting of parse-analysis errors +CREATE FUNCTION functest_S_xx(x date) RETURNS boolean + LANGUAGE SQL + RETURN x > 1; +ERROR: operator does not exist: date > integer +LINE 3: RETURN x > 1; + ^ +HINT: No operator matches the given name and argument types. You might need to add explicit type casts. +-- tricky parsing +CREATE FUNCTION functest_S_15(x int) RETURNS boolean +LANGUAGE SQL +BEGIN ATOMIC + select case when x % 2 = 0 then true else false end; +END; +SELECT functest_S_1('abcd', '2020-01-01'); + functest_s_1 +-------------- + t +(1 row) + +SELECT functest_S_2(ARRAY['1', '2', '3']); + functest_s_2 +-------------- + 1 +(1 row) + +SELECT functest_S_3(); + functest_s_3 +-------------- + f +(1 row) + +SELECT functest_S_10('abcd', '2020-01-01'); + functest_s_10 +--------------- + t +(1 row) + +SELECT functest_S_13(); + functest_s_13 +--------------- + f +(1 row) + +SELECT pg_get_functiondef('functest_S_1'::regproc); + pg_get_functiondef +------------------------------------------------------------------------ + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_1(a text, b date)+ + RETURNS boolean + + LANGUAGE sql + + RETURN ((a = 'abcd'::text) AND (b > '01-01-2001'::date)) + + +(1 row) + +SELECT pg_get_functiondef('functest_S_2'::regproc); + pg_get_functiondef +------------------------------------------------------------------ + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_2(a text[])+ + RETURNS integer + + LANGUAGE sql + + RETURN ((a)[1])::integer + + +(1 row) + +SELECT pg_get_functiondef('functest_S_3'::regproc); + pg_get_functiondef +---------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_3()+ + RETURNS boolean + + LANGUAGE sql + + RETURN false + + +(1 row) + +SELECT pg_get_functiondef('functest_S_3a'::regproc); + pg_get_functiondef +----------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_3a()+ + RETURNS boolean + + LANGUAGE sql + + BEGIN ATOMIC + + RETURN false; + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_S_10'::regproc); + pg_get_functiondef +------------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_10(a text, b date)+ + RETURNS boolean + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT ((a = 'abcd'::text) AND (b > '01-01-2001'::date)); + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_S_13'::regproc); + pg_get_functiondef +----------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_13()+ + RETURNS boolean + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT 1; + + SELECT false; + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_S_15'::regproc); + pg_get_functiondef +-------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_15(x integer)+ + RETURNS boolean + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT + + CASE + + WHEN ((x % 2) = 0) THEN true + + ELSE false + + END AS "case"; + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_S_16'::regproc); + pg_get_functiondef +------------------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_16(a integer, b integer)+ + RETURNS void + + LANGUAGE sql + + BEGIN ATOMIC + + INSERT INTO functest1 (i) SELECT (functest_s_16.a + functest_s_16.b); + + END + + +(1 row) + +DROP TABLE functest1 CASCADE; +NOTICE: drop cascades to function functest_s_16(integer,integer) +-- test with views +CREATE TABLE functest3 (a int); +INSERT INTO functest3 VALUES (1), (2); +CREATE VIEW functestv3 AS SELECT * FROM functest3; +CREATE FUNCTION functest_S_14() RETURNS bigint + RETURN (SELECT count(*) FROM functestv3); +SELECT functest_S_14(); + functest_s_14 +--------------- + 2 +(1 row) + +DROP TABLE functest3 CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to view functestv3 +drop cascades to function functest_s_14() +-- 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); +-- routine usage views +CREATE FUNCTION functest_IS_4a() RETURNS int LANGUAGE SQL AS 'SELECT 1'; +CREATE FUNCTION functest_IS_4b(x int DEFAULT functest_IS_4a()) RETURNS int LANGUAGE SQL AS 'SELECT x'; +CREATE SEQUENCE functest1; +CREATE FUNCTION functest_IS_5(x int DEFAULT nextval('functest1')) + RETURNS int + LANGUAGE SQL + AS 'SELECT x'; +CREATE FUNCTION functest_IS_6() + RETURNS int + LANGUAGE SQL + RETURN nextval('functest1'); +CREATE TABLE functest2 (a int, b int); +CREATE FUNCTION functest_IS_7() + RETURNS int + LANGUAGE SQL + RETURN (SELECT count(a) FROM functest2); +SELECT r0.routine_name, r1.routine_name + FROM information_schema.routine_routine_usage rru + JOIN information_schema.routines r0 ON r0.specific_name = rru.specific_name + JOIN information_schema.routines r1 ON r1.specific_name = rru.routine_name + WHERE r0.routine_schema = 'temp_func_test' AND + r1.routine_schema = 'temp_func_test' + ORDER BY 1, 2; + routine_name | routine_name +----------------+---------------- + functest_is_4b | functest_is_4a +(1 row) + +SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usage + WHERE routine_schema = 'temp_func_test' + ORDER BY 1, 2; + routine_name | sequence_name +---------------+--------------- + functest_is_5 | functest1 + functest_is_6 | functest1 +(2 rows) + +SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage + WHERE routine_schema = 'temp_func_test' + ORDER BY 1, 2; + routine_name | table_name | column_name +---------------+------------+------------- + functest_is_7 | functest2 | a +(1 row) + +SELECT routine_name, table_name FROM information_schema.routine_table_usage + WHERE routine_schema = 'temp_func_test' + ORDER BY 1, 2; + routine_name | table_name +---------------+------------ + functest_is_7 | functest2 +(1 row) + +DROP FUNCTION functest_IS_4a CASCADE; +NOTICE: drop cascades to function functest_is_4b(integer) +DROP SEQUENCE functest1 CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to function functest_is_5(integer) +drop cascades to function functest_is_6() +DROP TABLE functest2 CASCADE; +NOTICE: drop cascades to function functest_is_7() +-- 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); +-- inlining of set-returning functions +CREATE TABLE functest3 (a int); +INSERT INTO functest3 VALUES (1), (2), (3); +CREATE FUNCTION functest_sri1() RETURNS SETOF int +LANGUAGE SQL +STABLE +AS ' + SELECT * FROM functest3; +'; +SELECT * FROM functest_sri1(); + functest_sri1 +--------------- + 1 + 2 + 3 +(3 rows) + +EXPLAIN (verbose, costs off) SELECT * FROM functest_sri1(); + QUERY PLAN +-------------------------------------- + Seq Scan on temp_func_test.functest3 + Output: functest3.a +(2 rows) + +CREATE FUNCTION functest_sri2() RETURNS SETOF int +LANGUAGE SQL +STABLE +BEGIN ATOMIC + SELECT * FROM functest3; +END; +SELECT * FROM functest_sri2(); + functest_sri2 +--------------- + 1 + 2 + 3 +(3 rows) + +EXPLAIN (verbose, costs off) SELECT * FROM functest_sri2(); + QUERY PLAN +-------------------------------------- + Seq Scan on temp_func_test.functest3 + Output: functest3.a +(2 rows) + +DROP TABLE functest3 CASCADE; +NOTICE: drop cascades to function functest_sri2() +-- 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) + +-- Things that shouldn't work: +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT ''not an integer'';'; +ERROR: return type mismatch in function declared to return integer +DETAIL: Actual return type is text. +CONTEXT: SQL function "test1" +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'not even SQL'; +ERROR: syntax error at or near "not" +LINE 2: AS 'not even SQL'; + ^ +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT 1, 2, 3;'; +ERROR: return type mismatch in function declared to return integer +DETAIL: Final statement must return exactly one column. +CONTEXT: SQL function "test1" +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT $2;'; +ERROR: there is no parameter $2 +LINE 2: AS 'SELECT $2;'; + ^ +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'a', 'b'; +ERROR: only one AS item needed for language "sql" +-- Cleanup +DROP SCHEMA temp_func_test CASCADE; +NOTICE: drop cascades to 29 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_s_1(text,date) +drop cascades to function functest_s_2(text[]) +drop cascades to function functest_s_3() +drop cascades to function functest_s_3a() +drop cascades to function functest_s_10(text,date) +drop cascades to function functest_s_13() +drop cascades to function functest_s_15(integer) +drop cascades to function functest_b_2(bigint) +drop cascades to function functest_sri1() +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; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 82feda6eb18..6d8f524ae9e 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -47,13 +47,13 @@ test: copy copyselect copydml insert insert_conflict # More groups of parallel tests # Note: many of the tests in later groups depend on create_index # ---------- -test: create_function_0 create_misc create_operator create_procedure create_table create_type +test: create_function_c create_misc create_operator create_procedure create_table create_type test: create_index create_index_spgist create_view index_including index_including_gist # ---------- # Another group of parallel tests # ---------- -test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse +test: create_aggregate create_function_sql create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse # ---------- # sanity_check does a vacuum, affecting the sort order of SELECT * diff --git a/src/test/regress/sql/create_function_0.sql b/src/test/regress/sql/create_function_0.sql deleted file mode 100644 index 54da95f2494..00000000000 --- a/src/test/regress/sql/create_function_0.sql +++ /dev/null @@ -1,35 +0,0 @@ --- --- CREATE_FUNCTION_0 --- --- This script used to create C functions for other scripts to use. --- But to get rid of the ordering dependencies that caused, such --- functions are now made either in test_setup.sql or in the specific --- test script that needs them. All that remains here is error cases. - --- directory path and dlsuffix are passed to us in environment variables -\getenv libdir PG_LIBDIR -\getenv dlsuffix PG_DLSUFFIX - -\set regresslib :libdir '/regress' :dlsuffix - --- --- Check LOAD command. (The alternative of implicitly loading the library --- is checked in many other test scripts.) --- -LOAD :'regresslib'; - --- Things that shouldn't work: - -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C - AS 'nosuchfile'; - --- To produce stable regression test output, we have to filter the name --- of the regresslib file out of the error message in this test. -\set VERBOSITY sqlstate -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C - AS :'regresslib', 'nosuchsymbol'; -\set VERBOSITY default -SELECT regexp_replace(:'LAST_ERROR_MESSAGE', 'file ".*"', 'file "..."'); - -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE internal - AS 'nosuch'; diff --git a/src/test/regress/sql/create_function_3.sql b/src/test/regress/sql/create_function_3.sql deleted file mode 100644 index 254f444d94d..00000000000 --- a/src/test/regress/sql/create_function_3.sql +++ /dev/null @@ -1,408 +0,0 @@ --- --- 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[1]::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; - -SELECT functest_A_1('abcd', '2020-01-01'); -SELECT functest_A_2(ARRAY['1', '2', '3']); -SELECT functest_A_3(); - --- --- 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; - -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; - --- --- 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; - -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; - --- --- 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; - -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; - -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; - --- 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; - -CREATE FUNCTION functest_E_3(int) RETURNS bool LANGUAGE 'sql' - LEAKPROOF AS 'SELECT $1 < 200'; -- fail - -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; - -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; - - --- pg_get_functiondef tests - -SELECT pg_get_functiondef('functest_A_1'::regproc); -SELECT pg_get_functiondef('functest_B_3'::regproc); -SELECT pg_get_functiondef('functest_C_3'::regproc); -SELECT pg_get_functiondef('functest_F_2'::regproc); - - --- --- SQL-standard body --- -CREATE FUNCTION functest_S_1(a text, b date) RETURNS boolean - LANGUAGE SQL - RETURN a = 'abcd' AND b > '2001-01-01'; -CREATE FUNCTION functest_S_2(a text[]) RETURNS int - RETURN a[1]::int; -CREATE FUNCTION functest_S_3() RETURNS boolean - RETURN false; -CREATE FUNCTION functest_S_3a() RETURNS boolean - BEGIN ATOMIC - ;;RETURN false;; - END; - -CREATE FUNCTION functest_S_10(a text, b date) RETURNS boolean - LANGUAGE SQL - BEGIN ATOMIC - SELECT a = 'abcd' AND b > '2001-01-01'; - END; - -CREATE FUNCTION functest_S_13() RETURNS boolean - BEGIN ATOMIC - SELECT 1; - SELECT false; - END; - --- check display of function argments in sub-SELECT -CREATE TABLE functest1 (i int); -CREATE FUNCTION functest_S_16(a int, b int) RETURNS void - LANGUAGE SQL - BEGIN ATOMIC - INSERT INTO functest1 SELECT a + $2; - END; - --- error: duplicate function body -CREATE FUNCTION functest_S_xxx(x int) RETURNS int - LANGUAGE SQL - AS $$ SELECT x * 2 $$ - RETURN x * 3; - --- polymorphic arguments not allowed in this form -CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement - LANGUAGE SQL - RETURN x[1]; - --- check reporting of parse-analysis errors -CREATE FUNCTION functest_S_xx(x date) RETURNS boolean - LANGUAGE SQL - RETURN x > 1; - --- tricky parsing -CREATE FUNCTION functest_S_15(x int) RETURNS boolean -LANGUAGE SQL -BEGIN ATOMIC - select case when x % 2 = 0 then true else false end; -END; - -SELECT functest_S_1('abcd', '2020-01-01'); -SELECT functest_S_2(ARRAY['1', '2', '3']); -SELECT functest_S_3(); - -SELECT functest_S_10('abcd', '2020-01-01'); -SELECT functest_S_13(); - -SELECT pg_get_functiondef('functest_S_1'::regproc); -SELECT pg_get_functiondef('functest_S_2'::regproc); -SELECT pg_get_functiondef('functest_S_3'::regproc); -SELECT pg_get_functiondef('functest_S_3a'::regproc); -SELECT pg_get_functiondef('functest_S_10'::regproc); -SELECT pg_get_functiondef('functest_S_13'::regproc); -SELECT pg_get_functiondef('functest_S_15'::regproc); -SELECT pg_get_functiondef('functest_S_16'::regproc); - -DROP TABLE functest1 CASCADE; - --- test with views -CREATE TABLE functest3 (a int); -INSERT INTO functest3 VALUES (1), (2); -CREATE VIEW functestv3 AS SELECT * FROM functest3; - -CREATE FUNCTION functest_S_14() RETURNS bigint - RETURN (SELECT count(*) FROM functestv3); - -SELECT functest_S_14(); - -DROP TABLE functest3 CASCADE; - - --- 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; - -DROP FUNCTION functest_IS_1(int, int, text), functest_IS_2(int), functest_IS_3(int); - --- routine usage views - -CREATE FUNCTION functest_IS_4a() RETURNS int LANGUAGE SQL AS 'SELECT 1'; -CREATE FUNCTION functest_IS_4b(x int DEFAULT functest_IS_4a()) RETURNS int LANGUAGE SQL AS 'SELECT x'; - -CREATE SEQUENCE functest1; -CREATE FUNCTION functest_IS_5(x int DEFAULT nextval('functest1')) - RETURNS int - LANGUAGE SQL - AS 'SELECT x'; - -CREATE FUNCTION functest_IS_6() - RETURNS int - LANGUAGE SQL - RETURN nextval('functest1'); - -CREATE TABLE functest2 (a int, b int); - -CREATE FUNCTION functest_IS_7() - RETURNS int - LANGUAGE SQL - RETURN (SELECT count(a) FROM functest2); - -SELECT r0.routine_name, r1.routine_name - FROM information_schema.routine_routine_usage rru - JOIN information_schema.routines r0 ON r0.specific_name = rru.specific_name - JOIN information_schema.routines r1 ON r1.specific_name = rru.routine_name - WHERE r0.routine_schema = 'temp_func_test' AND - r1.routine_schema = 'temp_func_test' - ORDER BY 1, 2; -SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usage - WHERE routine_schema = 'temp_func_test' - ORDER BY 1, 2; -SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage - WHERE routine_schema = 'temp_func_test' - ORDER BY 1, 2; -SELECT routine_name, table_name FROM information_schema.routine_table_usage - WHERE routine_schema = 'temp_func_test' - ORDER BY 1, 2; - -DROP FUNCTION functest_IS_4a CASCADE; -DROP SEQUENCE functest1 CASCADE; -DROP TABLE functest2 CASCADE; - - --- 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 -DROP FUNCTION functest_b_2; -- error, ambiguous - - --- 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'; -CREATE OR REPLACE PROCEDURE functest1(a int) LANGUAGE SQL AS 'SELECT $1'; -DROP FUNCTION functest1(a int); - - --- inlining of set-returning functions - -CREATE TABLE functest3 (a int); -INSERT INTO functest3 VALUES (1), (2), (3); - -CREATE FUNCTION functest_sri1() RETURNS SETOF int -LANGUAGE SQL -STABLE -AS ' - SELECT * FROM functest3; -'; - -SELECT * FROM functest_sri1(); -EXPLAIN (verbose, costs off) SELECT * FROM functest_sri1(); - -CREATE FUNCTION functest_sri2() RETURNS SETOF int -LANGUAGE SQL -STABLE -BEGIN ATOMIC - SELECT * FROM functest3; -END; - -SELECT * FROM functest_sri2(); -EXPLAIN (verbose, costs off) SELECT * FROM functest_sri2(); - -DROP TABLE functest3 CASCADE; - - --- Check behavior of VOID-returning SQL functions - -CREATE FUNCTION voidtest1(a int) RETURNS VOID LANGUAGE SQL AS -$$ SELECT a + 1 $$; -SELECT voidtest1(42); - -CREATE FUNCTION voidtest2(a int, b int) RETURNS VOID LANGUAGE SQL AS -$$ SELECT voidtest1(a + b) $$; -SELECT voidtest2(11,22); - --- currently, we can inline voidtest2 but not voidtest1 -EXPLAIN (verbose, costs off) SELECT voidtest2(11,22); - -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); - -CREATE FUNCTION voidtest4(a int) RETURNS VOID LANGUAGE SQL AS -$$ INSERT INTO sometable VALUES(a - 1) RETURNING f1 $$; -SELECT voidtest4(39); - -TABLE sometable; - -CREATE FUNCTION voidtest5(a int) RETURNS SETOF VOID LANGUAGE SQL AS -$$ SELECT generate_series(1, a) $$ STABLE; -SELECT * FROM voidtest5(3); - --- Things that shouldn't work: - -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'SELECT ''not an integer'';'; - -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'not even SQL'; - -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'SELECT 1, 2, 3;'; - -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'SELECT $2;'; - -CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL - AS 'a', 'b'; - --- Cleanup -DROP SCHEMA temp_func_test CASCADE; -DROP USER regress_unpriv_user; -RESET search_path; diff --git a/src/test/regress/sql/create_function_c.sql b/src/test/regress/sql/create_function_c.sql new file mode 100644 index 00000000000..2e5a390a561 --- /dev/null +++ b/src/test/regress/sql/create_function_c.sql @@ -0,0 +1,35 @@ +-- +-- CREATE_FUNCTION_C +-- +-- This script used to create C functions for other scripts to use. +-- But to get rid of the ordering dependencies that caused, such +-- functions are now made either in test_setup.sql or in the specific +-- test script that needs them. All that remains here is error cases. + +-- directory path and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + +-- +-- Check LOAD command. (The alternative of implicitly loading the library +-- is checked in many other test scripts.) +-- +LOAD :'regresslib'; + +-- Things that shouldn't work: + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C + AS 'nosuchfile'; + +-- To produce stable regression test output, we have to filter the name +-- of the regresslib file out of the error message in this test. +\set VERBOSITY sqlstate +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C + AS :'regresslib', 'nosuchsymbol'; +\set VERBOSITY default +SELECT regexp_replace(:'LAST_ERROR_MESSAGE', 'file ".*"', 'file "..."'); + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE internal + AS 'nosuch'; diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql new file mode 100644 index 00000000000..4512456eae2 --- /dev/null +++ b/src/test/regress/sql/create_function_sql.sql @@ -0,0 +1,408 @@ +-- +-- CREATE_FUNCTION_SQL +-- +-- 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[1]::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; + +SELECT functest_A_1('abcd', '2020-01-01'); +SELECT functest_A_2(ARRAY['1', '2', '3']); +SELECT functest_A_3(); + +-- +-- 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; + +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; + +-- +-- 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; + +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; + +-- +-- 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; + +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; + +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; + +-- 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; + +CREATE FUNCTION functest_E_3(int) RETURNS bool LANGUAGE 'sql' + LEAKPROOF AS 'SELECT $1 < 200'; -- fail + +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; + +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; + + +-- pg_get_functiondef tests + +SELECT pg_get_functiondef('functest_A_1'::regproc); +SELECT pg_get_functiondef('functest_B_3'::regproc); +SELECT pg_get_functiondef('functest_C_3'::regproc); +SELECT pg_get_functiondef('functest_F_2'::regproc); + + +-- +-- SQL-standard body +-- +CREATE FUNCTION functest_S_1(a text, b date) RETURNS boolean + LANGUAGE SQL + RETURN a = 'abcd' AND b > '2001-01-01'; +CREATE FUNCTION functest_S_2(a text[]) RETURNS int + RETURN a[1]::int; +CREATE FUNCTION functest_S_3() RETURNS boolean + RETURN false; +CREATE FUNCTION functest_S_3a() RETURNS boolean + BEGIN ATOMIC + ;;RETURN false;; + END; + +CREATE FUNCTION functest_S_10(a text, b date) RETURNS boolean + LANGUAGE SQL + BEGIN ATOMIC + SELECT a = 'abcd' AND b > '2001-01-01'; + END; + +CREATE FUNCTION functest_S_13() RETURNS boolean + BEGIN ATOMIC + SELECT 1; + SELECT false; + END; + +-- check display of function argments in sub-SELECT +CREATE TABLE functest1 (i int); +CREATE FUNCTION functest_S_16(a int, b int) RETURNS void + LANGUAGE SQL + BEGIN ATOMIC + INSERT INTO functest1 SELECT a + $2; + END; + +-- error: duplicate function body +CREATE FUNCTION functest_S_xxx(x int) RETURNS int + LANGUAGE SQL + AS $$ SELECT x * 2 $$ + RETURN x * 3; + +-- polymorphic arguments not allowed in this form +CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement + LANGUAGE SQL + RETURN x[1]; + +-- check reporting of parse-analysis errors +CREATE FUNCTION functest_S_xx(x date) RETURNS boolean + LANGUAGE SQL + RETURN x > 1; + +-- tricky parsing +CREATE FUNCTION functest_S_15(x int) RETURNS boolean +LANGUAGE SQL +BEGIN ATOMIC + select case when x % 2 = 0 then true else false end; +END; + +SELECT functest_S_1('abcd', '2020-01-01'); +SELECT functest_S_2(ARRAY['1', '2', '3']); +SELECT functest_S_3(); + +SELECT functest_S_10('abcd', '2020-01-01'); +SELECT functest_S_13(); + +SELECT pg_get_functiondef('functest_S_1'::regproc); +SELECT pg_get_functiondef('functest_S_2'::regproc); +SELECT pg_get_functiondef('functest_S_3'::regproc); +SELECT pg_get_functiondef('functest_S_3a'::regproc); +SELECT pg_get_functiondef('functest_S_10'::regproc); +SELECT pg_get_functiondef('functest_S_13'::regproc); +SELECT pg_get_functiondef('functest_S_15'::regproc); +SELECT pg_get_functiondef('functest_S_16'::regproc); + +DROP TABLE functest1 CASCADE; + +-- test with views +CREATE TABLE functest3 (a int); +INSERT INTO functest3 VALUES (1), (2); +CREATE VIEW functestv3 AS SELECT * FROM functest3; + +CREATE FUNCTION functest_S_14() RETURNS bigint + RETURN (SELECT count(*) FROM functestv3); + +SELECT functest_S_14(); + +DROP TABLE functest3 CASCADE; + + +-- 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; + +DROP FUNCTION functest_IS_1(int, int, text), functest_IS_2(int), functest_IS_3(int); + +-- routine usage views + +CREATE FUNCTION functest_IS_4a() RETURNS int LANGUAGE SQL AS 'SELECT 1'; +CREATE FUNCTION functest_IS_4b(x int DEFAULT functest_IS_4a()) RETURNS int LANGUAGE SQL AS 'SELECT x'; + +CREATE SEQUENCE functest1; +CREATE FUNCTION functest_IS_5(x int DEFAULT nextval('functest1')) + RETURNS int + LANGUAGE SQL + AS 'SELECT x'; + +CREATE FUNCTION functest_IS_6() + RETURNS int + LANGUAGE SQL + RETURN nextval('functest1'); + +CREATE TABLE functest2 (a int, b int); + +CREATE FUNCTION functest_IS_7() + RETURNS int + LANGUAGE SQL + RETURN (SELECT count(a) FROM functest2); + +SELECT r0.routine_name, r1.routine_name + FROM information_schema.routine_routine_usage rru + JOIN information_schema.routines r0 ON r0.specific_name = rru.specific_name + JOIN information_schema.routines r1 ON r1.specific_name = rru.routine_name + WHERE r0.routine_schema = 'temp_func_test' AND + r1.routine_schema = 'temp_func_test' + ORDER BY 1, 2; +SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usage + WHERE routine_schema = 'temp_func_test' + ORDER BY 1, 2; +SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage + WHERE routine_schema = 'temp_func_test' + ORDER BY 1, 2; +SELECT routine_name, table_name FROM information_schema.routine_table_usage + WHERE routine_schema = 'temp_func_test' + ORDER BY 1, 2; + +DROP FUNCTION functest_IS_4a CASCADE; +DROP SEQUENCE functest1 CASCADE; +DROP TABLE functest2 CASCADE; + + +-- 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 +DROP FUNCTION functest_b_2; -- error, ambiguous + + +-- 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'; +CREATE OR REPLACE PROCEDURE functest1(a int) LANGUAGE SQL AS 'SELECT $1'; +DROP FUNCTION functest1(a int); + + +-- inlining of set-returning functions + +CREATE TABLE functest3 (a int); +INSERT INTO functest3 VALUES (1), (2), (3); + +CREATE FUNCTION functest_sri1() RETURNS SETOF int +LANGUAGE SQL +STABLE +AS ' + SELECT * FROM functest3; +'; + +SELECT * FROM functest_sri1(); +EXPLAIN (verbose, costs off) SELECT * FROM functest_sri1(); + +CREATE FUNCTION functest_sri2() RETURNS SETOF int +LANGUAGE SQL +STABLE +BEGIN ATOMIC + SELECT * FROM functest3; +END; + +SELECT * FROM functest_sri2(); +EXPLAIN (verbose, costs off) SELECT * FROM functest_sri2(); + +DROP TABLE functest3 CASCADE; + + +-- Check behavior of VOID-returning SQL functions + +CREATE FUNCTION voidtest1(a int) RETURNS VOID LANGUAGE SQL AS +$$ SELECT a + 1 $$; +SELECT voidtest1(42); + +CREATE FUNCTION voidtest2(a int, b int) RETURNS VOID LANGUAGE SQL AS +$$ SELECT voidtest1(a + b) $$; +SELECT voidtest2(11,22); + +-- currently, we can inline voidtest2 but not voidtest1 +EXPLAIN (verbose, costs off) SELECT voidtest2(11,22); + +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); + +CREATE FUNCTION voidtest4(a int) RETURNS VOID LANGUAGE SQL AS +$$ INSERT INTO sometable VALUES(a - 1) RETURNING f1 $$; +SELECT voidtest4(39); + +TABLE sometable; + +CREATE FUNCTION voidtest5(a int) RETURNS SETOF VOID LANGUAGE SQL AS +$$ SELECT generate_series(1, a) $$ STABLE; +SELECT * FROM voidtest5(3); + +-- Things that shouldn't work: + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT ''not an integer'';'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'not even SQL'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT 1, 2, 3;'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT $2;'; + +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'a', 'b'; + +-- Cleanup +DROP SCHEMA temp_func_test CASCADE; +DROP USER regress_unpriv_user; +RESET search_path; -- cgit v1.2.3