This is an automated email from the ASF dual-hosted git repository. chenjinbao1989 pushed a commit to branch cbdb-postgres-merge in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit e71bd0b9cf56e48044ac4fc2057ab6e091a450a8 Author: Jinbao Chen <[email protected]> AuthorDate: Wed Mar 25 10:04:32 2026 -0400 Add some cases --- .../test/regress/expected/create_function_sql.out | 749 +++++++++++++++++++++ .../src/test/regress/sql/create_function_sql.sql | 421 ++++++++++++ .../src/test/regress/sql/test_setup.sql | 301 +++++++++ 3 files changed, 1471 insertions(+) diff --git a/contrib/pax_storage/src/test/regress/expected/create_function_sql.out b/contrib/pax_storage/src/test/regress/expected/create_function_sql.out new file mode 100644 index 00000000000..5cae4ecbc1e --- /dev/null +++ b/contrib/pax_storage/src/test/regress/expected/create_function_sql.out @@ -0,0 +1,749 @@ +-- +-- 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; diff --git a/contrib/pax_storage/src/test/regress/sql/create_function_sql.sql b/contrib/pax_storage/src/test/regress/sql/create_function_sql.sql new file mode 100644 index 00000000000..89e9af3a499 --- /dev/null +++ b/contrib/pax_storage/src/test/regress/sql/create_function_sql.sql @@ -0,0 +1,421 @@ +-- +-- 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 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; + +-- 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); + +-- 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); + +-- 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/contrib/pax_storage/src/test/regress/sql/test_setup.sql b/contrib/pax_storage/src/test/regress/sql/test_setup.sql new file mode 100644 index 00000000000..1b2d434683b --- /dev/null +++ b/contrib/pax_storage/src/test/regress/sql/test_setup.sql @@ -0,0 +1,301 @@ +-- +-- TEST_SETUP --- prepare environment expected by regression test scripts +-- + +-- directory paths and dlsuffix are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + +-- +-- synchronous_commit=off delays when hint bits may be set. Some plans change +-- depending on the number of all-visible pages, which in turn can be +-- influenced by the delayed hint bits. Force synchronous_commit=on to avoid +-- that source of variability. +-- +SET synchronous_commit = on; + +-- +-- Postgres formerly made the public schema read/write by default, +-- and most of the core regression tests still expect that. +-- +GRANT ALL ON SCHEMA public TO public; + +-- Create a tablespace we can use in tests. +SET allow_in_place_tablespaces = true; +CREATE TABLESPACE regress_tblspace LOCATION ''; + +-- +-- These tables have traditionally been referenced by many tests, +-- so create and populate them. Insert only non-error values here. +-- (Some subsequent tests try to insert erroneous values. That's okay +-- because the table won't actually change. Do not change the contents +-- of these tables in later tests, as it may affect other tests.) +-- + +CREATE TABLE CHAR_TBL(f1 char(4)); + +INSERT INTO CHAR_TBL (f1) VALUES + ('a'), + ('ab'), + ('abcd'), + ('abcd '); +VACUUM CHAR_TBL; + +CREATE TABLE FLOAT8_TBL(f1 float8); + +INSERT INTO FLOAT8_TBL(f1) VALUES + ('0.0'), + ('-34.84'), + ('-1004.30'), + ('-1.2345678901234e+200'), + ('-1.2345678901234e-200'); +VACUUM FLOAT8_TBL; + +CREATE TABLE INT2_TBL(f1 int2); + +INSERT INTO INT2_TBL(f1) VALUES + ('0 '), + (' 1234 '), + (' -1234'), + ('32767'), -- largest and smallest values + ('-32767'); +VACUUM INT2_TBL; + +CREATE TABLE INT4_TBL(f1 int4); + +INSERT INTO INT4_TBL(f1) VALUES + (' 0 '), + ('123456 '), + (' -123456'), + ('2147483647'), -- largest and smallest values + ('-2147483647'); +VACUUM INT4_TBL; + +CREATE TABLE INT8_TBL(q1 int8, q2 int8); + +INSERT INTO INT8_TBL VALUES + (' 123 ',' 456'), + ('123 ','4567890123456789'), + ('4567890123456789','123'), + (+4567890123456789,'4567890123456789'), + ('+4567890123456789','-4567890123456789'); +VACUUM INT8_TBL; + +CREATE TABLE POINT_TBL(f1 point); + +INSERT INTO POINT_TBL(f1) VALUES + ('(0.0,0.0)'), + ('(-10.0,0.0)'), + ('(-3.0,4.0)'), + ('(5.1, 34.5)'), + ('(-5.0,-12.0)'), + ('(1e-300,-1e-300)'), -- To underflow + ('(1e+300,Inf)'), -- To overflow + ('(Inf,1e+300)'), -- Transposed + (' ( Nan , NaN ) '), + ('10.0,10.0'); +-- We intentionally don't vacuum point_tbl here; geometry depends on that + +CREATE TABLE TEXT_TBL (f1 text); + +INSERT INTO TEXT_TBL VALUES + ('doh!'), + ('hi de ho neighbor'); +VACUUM TEXT_TBL; + +CREATE TABLE VARCHAR_TBL(f1 varchar(4)); + +INSERT INTO VARCHAR_TBL (f1) VALUES + ('a'), + ('ab'), + ('abcd'), + ('abcd '); +VACUUM VARCHAR_TBL; + +CREATE TABLE onek ( + unique1 int4, + unique2 int4, + two int4, + four int4, + ten int4, + twenty int4, + hundred int4, + thousand int4, + twothousand int4, + fivethous int4, + tenthous int4, + odd int4, + even int4, + stringu1 name, + stringu2 name, + string4 name +); + +\set filename :abs_srcdir '/data/onek.data' +COPY onek FROM :'filename'; +VACUUM ANALYZE onek; + +CREATE TABLE onek2 AS SELECT * FROM onek; +VACUUM ANALYZE onek2; + +CREATE TABLE tenk1 ( + unique1 int4, + unique2 int4, + two int4, + four int4, + ten int4, + twenty int4, + hundred int4, + thousand int4, + twothousand int4, + fivethous int4, + tenthous int4, + odd int4, + even int4, + stringu1 name, + stringu2 name, + string4 name +); + +\set filename :abs_srcdir '/data/tenk.data' +COPY tenk1 FROM :'filename'; +VACUUM ANALYZE tenk1; + +CREATE TABLE tenk2 AS SELECT * FROM tenk1; +VACUUM ANALYZE tenk2; + +CREATE TABLE person ( + name text, + age int4, + location point +); + +\set filename :abs_srcdir '/data/person.data' +COPY person FROM :'filename'; +VACUUM ANALYZE person; + +CREATE TABLE emp ( + salary int4, + manager name +) INHERITS (person); + +\set filename :abs_srcdir '/data/emp.data' +COPY emp FROM :'filename'; +VACUUM ANALYZE emp; + +CREATE TABLE student ( + gpa float8 +) INHERITS (person); + +\set filename :abs_srcdir '/data/student.data' +COPY student FROM :'filename'; +VACUUM ANALYZE student; + +CREATE TABLE stud_emp ( + percent int4 +) INHERITS (emp, student); + +\set filename :abs_srcdir '/data/stud_emp.data' +COPY stud_emp FROM :'filename'; +VACUUM ANALYZE stud_emp; + +CREATE TABLE road ( + name text, + thepath path +); + +\set filename :abs_srcdir '/data/streets.data' +COPY road FROM :'filename'; +VACUUM ANALYZE road; + +CREATE TABLE ihighway () INHERITS (road); + +INSERT INTO ihighway + SELECT * + FROM ONLY road + WHERE name ~ 'I- .*'; +VACUUM ANALYZE ihighway; + +CREATE TABLE shighway ( + surface text +) INHERITS (road); + +INSERT INTO shighway + SELECT *, 'asphalt' + FROM ONLY road + WHERE name ~ 'State Hwy.*'; +VACUUM ANALYZE shighway; + +-- +-- We must have some enum type in the database for opr_sanity and type_sanity. +-- + +create type stoplight as enum ('red', 'yellow', 'green'); + +-- +-- Also create some non-built-in range types. +-- + +create type float8range as range (subtype = float8, subtype_diff = float8mi); + +create type textrange as range (subtype = text, collation = "C"); + +-- +-- Create some C functions that will be used by various tests. +-- + +CREATE FUNCTION binary_coercible(oid, oid) + RETURNS bool + AS :'regresslib', 'binary_coercible' + LANGUAGE C STRICT STABLE PARALLEL SAFE; + +CREATE FUNCTION ttdummy () + RETURNS trigger + AS :'regresslib' + LANGUAGE C; + +CREATE FUNCTION get_columns_length(oid[]) + RETURNS int + AS :'regresslib' + LANGUAGE C STRICT STABLE PARALLEL SAFE; + +-- Use hand-rolled hash functions and operator classes to get predictable +-- result on different machines. The hash function for int4 simply returns +-- the sum of the values passed to it and the one for text returns the length +-- of the non-empty string value passed to it or 0. + +create function part_hashint4_noop(value int4, seed int8) + returns int8 as $$ + select value + seed; + $$ language sql strict immutable parallel safe; + +create operator class part_test_int4_ops for type int4 using hash as + operator 1 =, + function 2 part_hashint4_noop(int4, int8); + +create function part_hashtext_length(value text, seed int8) + returns int8 as $$ + select length(coalesce(value, ''))::int8 + $$ language sql strict immutable parallel safe; + +create operator class part_test_text_ops for type text using hash as + operator 1 =, + function 2 part_hashtext_length(text, int8); + +-- +-- These functions are used in tests that used to use md5(), which we now +-- mostly avoid so that the tests will pass in FIPS mode. +-- + +create function fipshash(bytea) + returns text + strict immutable parallel safe leakproof + return substr(encode(sha256($1), 'hex'), 1, 32); + +create function fipshash(text) + returns text + strict immutable parallel safe leakproof + return substr(encode(sha256($1::bytea), 'hex'), 1, 32); --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
