| -- |
| -- 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 arguments 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 |
| -------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: functest3.a |
| -> Seq Scan on temp_func_test.functest3 |
| Output: functest3.a |
| Optimizer: Postgres query optimizer |
| (5 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 |
| -------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: functest3.a |
| -> Seq Scan on temp_func_test.functest3 |
| Output: functest3.a |
| Optimizer: Postgres query optimizer |
| (5 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) |
| |
| -- Regression tests for bugs: |
| -- Check that arguments that are R/W expanded datums aren't corrupted by |
| -- multiple uses. This test knows that array_append() returns a R/W datum |
| -- and will modify a R/W array input in-place. We use SETOF to prevent |
| -- inlining of the SQL function. |
| CREATE FUNCTION double_append(anyarray, anyelement) RETURNS SETOF anyarray |
| LANGUAGE SQL IMMUTABLE AS |
| $$ SELECT array_append($1, $2) || array_append($1, $2) $$; |
| SELECT double_append(array_append(ARRAY[q1], q2), q3) |
| FROM (VALUES(1,2,3), (4,5,6)) v(q1,q2,q3); |
| double_append |
| --------------- |
| {1,2,3,1,2,3} |
| {4,5,6,4,5,6} |
| (2 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 30 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 cascades to function double_append(anyarray,anyelement) |
| DROP USER regress_unpriv_user; |
| RESET search_path; |
