Repository: incubator-hawq Updated Branches: refs/heads/master 9f7a3a427 -> 66f0bda1f
HAWQ-769. Migrate function, function_extensions and set_functions of user-defined function test from installcheck to new feature test framework Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/a2e8ff72 Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/a2e8ff72 Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/a2e8ff72 Branch: refs/heads/master Commit: a2e8ff72c6ab5d55aa9af62475c49d7ae07674b1 Parents: 9f7a3a4 Author: Ruilong Huo <[email protected]> Authored: Tue Jun 14 16:12:51 2016 +0800 Committer: Ruilong Huo <[email protected]> Committed: Tue Jun 14 16:12:51 2016 +0800 ---------------------------------------------------------------------- src/test/feature/udf/TestUDF.cpp | 32 + src/test/feature/udf/ans/function_basics.ans | 1076 ++++++++++++++++++ src/test/feature/udf/ans/function_extension.ans | 183 +++ .../feature/udf/ans/function_set_returning.ans | 287 +++++ src/test/feature/udf/sql/function_basics.sql | 439 +++++++ src/test/feature/udf/sql/function_extension.sql | 123 ++ .../feature/udf/sql/function_set_returning.sql | 93 ++ 7 files changed, 2233 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2e8ff72/src/test/feature/udf/TestUDF.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/udf/TestUDF.cpp b/src/test/feature/udf/TestUDF.cpp new file mode 100755 index 0000000..fcd33ea --- /dev/null +++ b/src/test/feature/udf/TestUDF.cpp @@ -0,0 +1,32 @@ +#include "gtest/gtest.h" + +#include "lib/sql_util.h" + + +class TestUDF: public ::testing::Test +{ + public: + TestUDF() {} + ~TestUDF() {} +}; + +TEST_F(TestUDF, TestUDFBasics) +{ + hawq::test::SQLUtility util; + util.execSQLFile("udf/sql/function_basics.sql", + "udf/ans/function_basics.ans"); +} + +TEST_F(TestUDF, TestUDFSetReturning) +{ + hawq::test::SQLUtility util; + util.execSQLFile("udf/sql/function_set_returning.sql", + "udf/ans/function_set_returning.ans"); +} + +TEST_F(TestUDF, TestUDFExtension) +{ + hawq::test::SQLUtility util; + util.execSQLFile("udf/sql/function_extension.sql", + "udf/ans/function_extension.ans"); +} http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2e8ff72/src/test/feature/udf/ans/function_basics.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/udf/ans/function_basics.ans b/src/test/feature/udf/ans/function_basics.ans new file mode 100755 index 0000000..7328107 --- /dev/null +++ b/src/test/feature/udf/ans/function_basics.ans @@ -0,0 +1,1076 @@ +-- start_ignore +SET SEARCH_PATH=TestUDF_TestUDFBasics; +SET +-- end_ignore +-- SETUP +DROP TABLE IF EXISTS foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:5: NOTICE: table "foo" does not exist, skipping +DROP TABLE +CREATE TABLE foo AS SELECT * FROM generate_series(1, 10) x; +SELECT 10 +CREATE FUNCTION f(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +-- DDL, CREATE FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proname FROM pg_proc WHERE proname = 'g'; + proname +--------- + g +(1 row) + +SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + proname +--------- +(0 rows) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DDL, CREATE OR REPLACE FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proname FROM pg_proc WHERE proname = 'g'; + proname +--------- + g +(1 row) + +SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + proname +--------- +(0 rows) + +CREATE OR REPLACE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN (-1) * x; +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +SELECT proname, prosrc FROM pg_proc WHERE proname = 'g'; + proname | prosrc +---------+------------------ + g | + : BEGIN + : RETURN (-1) * x; + : END + : +(1 row) + +SELECT proname, prosrc FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + proname | prosrc +---------+-------- +(0 rows) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DDL, DROP FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +DROP FUNCTION g(int); +DROP FUNCTION +SELECT oid, proname FROM pg_proc WHERE proname = 'g'; + oid | proname +-----+--------- +(0 rows) + +SELECT oid, proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + oid | proname +-----+--------- +(0 rows) + +-- DDL, DROP FUNCTION, NEGATIVE +DROP FUNCTION g(int); +psql:/tmp/TestUDF_TestUDFBasics.sql:47: ERROR: function g(integer) does not exist +-- DDL, CREATE FUNCTION, RECORD +CREATE FUNCTION foo(int) RETURNS record AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL; +CREATE FUNCTION +SELECT foo(5); + foo +----- + (5) +(1 row) + +DROP FUNCTION foo(int); +DROP FUNCTION +CREATE FUNCTION foo(int) RETURNS foo AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL; +CREATE FUNCTION +SELECT foo(5); + foo +----- + (5) +(1 row) + +DROP FUNCTION foo(int); +DROP FUNCTION +-- DDL, CREATE FUNCTION, SRF +CREATE FUNCTION g(x setof int) RETURNS INT + AS $$ SELECT 1 $$ LANGUAGE SQL; +CREATE FUNCTION +DROP FUNCTION g(setof int); +DROP FUNCTION +CREATE FUNCTION g() RETURNS setof INT + AS $$ SELECT 1 $$ LANGUAGE SQL; +CREATE FUNCTION +DROP FUNCTION g(); +DROP FUNCTION +-- DDL, CREATE FUNCTION, TABLE, NEGATIVE +CREATE FUNCTION g() RETURNS TABLE(x int) + AS $$ SELECT * FROM foo $$ LANGUAGE SQL; +CREATE FUNCTION +DROP FUNCTION g(); +DROP FUNCTION +CREATE FUNCTION g(anytable) RETURNS int + AS 'does_not_exist', 'does_not_exist' LANGUAGE C; +psql:/tmp/TestUDF_TestUDFBasics.sql:76: ERROR: TABLE functions not supported +-- DDL, CREATE FUNCTION, SECURITY DEFINER +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE SECURITY DEFINER; +CREATE FUNCTION +DROP FUNCTION g(int); +DROP FUNCTION +-- DDL, ALTER FUNCTION +-- DDL, STRICT +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT g(NULL); + g +--- + 1 +(1 row) + +ALTER FUNCTION g(int) STRICT; +ALTER FUNCTION +SELECT g(NULL); + g +--- + +(1 row) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DDL, ALTER FUNCTION, OWNER +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:97: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +SET ROLE superuser; +SET +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g'; + rolname +----------- + superuser +(1 row) + +ALTER FUNCTION g(int) OWNER TO u1; +ALTER FUNCTION +SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g'; + rolname +--------- + u1 +(1 row) + +DROP FUNCTION g(int); +DROP FUNCTION +RESET ROLE; +RESET +DROP ROLE u1; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DDL, ALTER FUNCTION, RENAME +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT g(0); + g +--- + 1 +(1 row) + +ALTER FUNCTION g(int) RENAME TO h; +ALTER FUNCTION +SELECT h(0); + h +--- + 1 +(1 row) + +DROP FUNCTION h(int); +DROP FUNCTION +-- DDL, ALTER FUNCTION, SET SCHEMA +CREATE SCHEMA bar; +CREATE SCHEMA +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT g(0); + g +--- + 1 +(1 row) + +ALTER FUNCTION g(int) SET SCHEMA bar; +ALTER FUNCTION +SELECT bar.g(0); + g +--- + 1 +(1 row) + +DROP SCHEMA bar CASCADE; +psql:/tmp/TestUDF_TestUDFBasics.sql:125: NOTICE: drop cascades to function bar.g(integer) +DROP SCHEMA +-- DDL, ALTER FUNCTION, SECURITY DEFINER +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +ALTER FUNCTION g(int) SECURITY DEFINER; +ALTER FUNCTION +DROP FUNCTION g(int); +DROP FUNCTION +-- DCL, GRANT/REVOKE +-- GRANT { EXECUTE | ALL [ PRIVILEGES ] } +-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] +-- TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] +-- REVOKE [ GRANT OPTION FOR ] +-- { EXECUTE | ALL [ PRIVILEGES ] } +-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] +-- FROM { username | GROUP groupname | PUBLIC } [, ...] +-- [ CASCADE | RESTRICT ] +-- DCL, GRANT/REVOKE, EXECUTE +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +SET ROLE superuser; +SET +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:149: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +GRANT SELECT ON TABLE foo TO u1; +GRANT +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +REVOKE +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +------------------------- + {superuser=X/superuser} +(1 row) + +SELECT g(1); + g +--- + 1 +(1 row) + +SELECT count(g(x)) FROM foo; + count +------- + 10 +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:158: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:159: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +GRANT EXECUTE ON FUNCTION g(int) TO u1; +GRANT +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +---------------------------------------- + {superuser=X/superuser,u1=X/superuser} +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:164: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:165: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +REVOKE EXECUTE ON FUNCTION g(int) FROM u1; +REVOKE +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +------------------------- + {superuser=X/superuser} +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:170: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:171: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +RESET ROLE; +RESET +DROP FUNCTION g(int); +DROP FUNCTION +REVOKE SELECT ON TABLE foo FROM u1; +REVOKE +DROP ROLE u1; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DCL, GRANT/REVOKE, PUBLIC +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +SET ROLE superuser; +SET +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:183: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +GRANT SELECT ON TABLE foo TO u1; +GRANT +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +REVOKE +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +------------------------- + {superuser=X/superuser} +(1 row) + +SELECT g(1); + g +--- + 1 +(1 row) + +SELECT count(g(x)) FROM foo; + count +------- + 10 +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:192: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:193: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +GRANT EXECUTE ON FUNCTION g(int) TO PUBLIC; +GRANT +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------------------------------------- + {superuser=X/superuser,=X/superuser} +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:198: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:199: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +REVOKE EXECUTE ON FUNCTION g(int) FROM PUBLIC; +REVOKE +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +------------------------- + {superuser=X/superuser} +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:204: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:205: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +RESET ROLE; +RESET +DROP FUNCTION g(int); +DROP FUNCTION +REVOKE SELECT ON TABLE foo FROM u1; +REVOKE +DROP ROLE u1; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DCL, GRANT/REVOKE, Groups +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +SET ROLE superuser; +SET +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:217: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +CREATE ROLE u2 IN GROUP u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:218: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +GRANT SELECT ON TABLE foo TO u1; +GRANT +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +REVOKE +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +------------------------- + {superuser=X/superuser} +(1 row) + +SELECT g(1); + g +--- + 1 +(1 row) + +SELECT count(g(x)) FROM foo; + count +------- + 10 +(1 row) + +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:227: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:228: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +GRANT EXECUTE ON FUNCTION g(int) TO u1; +GRANT +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +---------------------------------------- + {superuser=X/superuser,u1=X/superuser} +(1 row) + +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:233: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:234: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +REVOKE EXECUTE ON FUNCTION g(int) FROM u1; +REVOKE +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +------------------------- + {superuser=X/superuser} +(1 row) + +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:239: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:240: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +RESET ROLE; +RESET +DROP FUNCTION g(int); +DROP FUNCTION +REVOKE SELECT ON TABLE foo FROM u1; +REVOKE +DROP ROLE u1; +DROP ROLE +DROP ROLE u2; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DCL, GRANT/REVOKE, WITH GRANT OPTION +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +SET ROLE superuser; +SET +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:253: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +CREATE ROLE u2; +psql:/tmp/TestUDF_TestUDFBasics.sql:254: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +GRANT SELECT ON TABLE foo TO PUBLIC; +GRANT +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +REVOKE +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +------------------------- + {superuser=X/superuser} +(1 row) + +SELECT g(1); + g +--- + 1 +(1 row) + +SELECT count(g(x)) FROM foo; + count +------- + 10 +(1 row) + +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:263: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:264: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +GRANT ALL ON FUNCTION g(int) TO u1 WITH GRANT OPTION; +GRANT +SET ROLE u1; +SET +GRANT ALL ON FUNCTION g(int) TO u2; +psql:/tmp/TestUDF_TestUDFBasics.sql:268: ERROR: function g(integer) does not exist +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +----------------------------------------- + {superuser=X/superuser,u1=X*/superuser} +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:271: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:272: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:274: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:275: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +REVOKE ALL ON FUNCTION g(int) FROM u1 CASCADE; +REVOKE +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +------------------------- + {superuser=X/superuser} +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:280: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:281: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:283: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:284: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +RESET ROLE; +RESET +DROP FUNCTION g(int); +DROP FUNCTION +REVOKE SELECT ON TABLE foo FROM PUBLIC; +REVOKE +DROP ROLE u1; +DROP ROLE +DROP ROLE u2; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DML, CaseExpr +SELECT CASE WHEN x % 2 = 0 THEN f(x) ELSE 0 END FROM foo ORDER BY x; + case +------ + 0 + 2 + 0 + 4 + 0 + 6 + 0 + 8 + 0 + 10 +(10 rows) + +-- DML, OpExpr +SELECT f(x) + f(x) FROM foo ORDER BY x; + ?column? +---------- + 2 + 4 + 6 + 8 + 10 + 12 + 14 + 16 + 18 + 20 +(10 rows) + +SELECT f(x) + f(x) + f(x) FROM foo ORDER BY x; + ?column? +---------- + 3 + 6 + 9 + 12 + 15 + 18 + 21 + 24 + 27 + 30 +(10 rows) + +SELECT f(x) + f(x) - f(x) FROM foo ORDER BY x; + ?column? +---------- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, FuncExpr +CREATE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +SELECT g(f(x)) FROM foo ORDER BY x; + g +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DML, BoolExpr +SELECT x % 2 = 0 AND f(x) % 2 = 1 FROM foo ORDER BY x; + ?column? +---------- + f + f + f + f + f + f + f + f + f + f +(10 rows) + +-- DML, DistinctExpr +SELECT x IS DISTINCT FROM f(x) from foo ORDER BY x; + ?column? +---------- + f + f + f + f + f + f + f + f + f + f +(10 rows) + +-- DML, PercentileExpr +SELECT MEDIAN(f(x)) FROM foo; + median +-------- + 5.5 +(1 row) + +-- DML, Complex Expression +CREATE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +SELECT CASE + WHEN x % 2 = 0 THEN g(g(x)) + g(g(x)) + WHEN f(x) % 2 = 1 THEN g(g(x)) - g(g(x)) + END FROM foo ORDER BY x; + case +------ + 0 + 4 + 0 + 8 + 0 + 12 + 0 + 16 + 0 + 20 +(10 rows) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DML, Qual +SELECT x FROM foo WHERE f(x) % 2 = 0 ORDER BY x; + x +---- + 2 + 4 + 6 + 8 + 10 +(5 rows) + +-- DML, FROM +SELECT * FROM f(5); + f +--- + 5 +(1 row) + +-- DML, Grouping +SELECT DISTINCT f(x) FROM foo ORDER BY f(x); + f +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +SELECT f(x) FROM foo GROUP BY f(x) ORDER BY f(x); + f +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, Join +SELECT a.x FROM foo a, foo b WHERE f(a.x) = f(b.x) ORDER BY x; + x +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +SELECT a.x FROM foo a JOIN foo b ON f(a.x) = f(b.x) ORDER BY x; + x +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, Windowing +SELECT avg(x) OVER (PARTITION BY f(x)) FROM foo ORDER BY x; + avg +----- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, CTE +WITH t AS (SELECT x from foo) + SELECT f(x) from t ORDER BY x; + f +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, InitPlan +SELECT UNNEST(ARRAY(SELECT x FROM foo)) ORDER BY 1; + unnest +-------- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +SELECT UNNEST(ARRAY(SELECT f(1))); + unnest +-------- + 1 +(1 row) + +-- PROPERTIES, VOLATILITY, IMMUTABLE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; + ?column? +---------- + f +(1 row) + +DROP FUNCTION g(); +DROP FUNCTION +-- PROPERTIES, VOLATILITY, STABLE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL STABLE; +CREATE FUNCTION +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; + ?column? +---------- + f +(1 row) + +DROP FUNCTION g(); +DROP FUNCTION +-- PROPERTIES, VOLATILITY, VOLATILE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL VOLATILE; +CREATE FUNCTION +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; + ?column? +---------- + t +(1 row) + +DROP FUNCTION g(); +DROP FUNCTION +----------------- +-- NEGATIVE TESTS +----------------- +SELECT h(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:405: ERROR: function h(integer) does not exist +LINE 1: SELECT h(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +-- DML, InitPlan +SELECT UNNEST(ARRAY(SELECT f(x) from foo)); + unnest +-------- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- LANGUAGES not yet supported +-- CREATE LANGUAGE plr; +-- CREATE LANGUAGE plpython; +-- CREATE LANGUAGE pljava; +-- CREATE LANGUAGE plperl; +-- NESTED FUNCTION +CREATE FUNCTION inner(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +CREATE FUNCTION outer(x INT) RETURNS INT AS $$ +BEGIN +RETURN inner(x); +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +SELECT outer(0); + outer +------- + 1 +(1 row) + +SELECT outer(0) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:423: ERROR: function inner(integer) does not exist (seg2 localhost:40000 pid=70512) +DETAIL: PL/pgSQL function "outer" line 2 at return +DROP FUNCTION outer(int); +DROP FUNCTION +DROP FUNCTION inner(int); +DROP FUNCTION +-- TEARDOWN +DROP TABLE foo; +DROP TABLE +-- HAWQ-510 +drop table if exists testEntryDB; +psql:/tmp/TestUDF_TestUDFBasics.sql:435: NOTICE: table "testentrydb" does not exist, skipping +DROP TABLE +create table testEntryDB(key int, value int) distributed randomly; +CREATE TABLE +insert into testEntryDB values(1, 0); +INSERT 0 1 +select t2.key, t2.value +from (select key, value from testEntryDB where value = 0) as t1, + (select generate_series(1,2)::int as key, 0::int as value) as t2 +where t1.value=t2.value; + key | value +-----+------- + 1 | 0 + 2 | 0 +(2 rows) + +drop table testEntryDB; +DROP TABLE http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2e8ff72/src/test/feature/udf/ans/function_extension.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/udf/ans/function_extension.ans b/src/test/feature/udf/ans/function_extension.ans new file mode 100755 index 0000000..e1d14f4 --- /dev/null +++ b/src/test/feature/udf/ans/function_extension.ans @@ -0,0 +1,183 @@ +-- start_ignore +SET SEARCH_PATH=TestUDF_TestUDFExtension; +SET +-- end_ignore +-- ----------------------------------------------------------------- +-- Test extensions to functions (MPP-16060) +-- 1. data access indicators +-- ----------------------------------------------------------------- +-- test prodataaccess +create function func1(int, int) returns int as +$$ + select $1 + $2; +$$ language sql immutable contains sql; +CREATE FUNCTION +-- check prodataaccess column in pg_proc +select proname, prodataaccess from pg_proc where proname = 'func1'; + proname | prodataaccess +---------+--------------- + func1 | c +(1 row) + +-- check prodataaccess in pg_attribute +select relname, attname, attlen from pg_class c, pg_attribute +where attname = 'prodataaccess' and attrelid = c.oid and c.relname = 'pg_proc'; + relname | attname | attlen +---------+---------------+-------- + pg_proc | prodataaccess | 1 +(1 row) + +create function func2(a anyelement, b anyelement, flag bool) +returns anyelement as +$$ + select $1 + $2; +$$ language sql reads sql data; +CREATE FUNCTION +-- check prodataaccess column in pg_proc +select proname, prodataaccess from pg_proc where proname = 'func2'; + proname | prodataaccess +---------+--------------- + func2 | r +(1 row) + +create function func3() returns oid as +$$ + select oid from pg_class where relname = 'pg_type'; +$$ language sql modifies sql data volatile; +CREATE FUNCTION +-- check prodataaccess column in pg_proc +select proname, prodataaccess from pg_proc where proname = 'func3'; + proname | prodataaccess +---------+--------------- + func3 | m +(1 row) + +-- check default value of prodataaccess +drop function func1(int, int); +DROP FUNCTION +create function func1(int, int) returns varchar as $$ +declare + v_name varchar(20) DEFAULT 'zzzzz'; +begin + select relname from pg_class into v_name where oid=$1; + return v_name; +end; +$$ language plpgsql; +CREATE FUNCTION +select proname, proargnames, prodataaccess from pg_proc where proname = 'func1'; + proname | proargnames | prodataaccess +---------+-------------+--------------- + func1 | | n +(1 row) + +create function func4(int, int) returns int as +$$ + select $1 + $2; +$$ language sql; +CREATE FUNCTION +-- check prodataaccess column +select proname, proargnames, prodataaccess from pg_proc where proname = 'func4'; + proname | proargnames | prodataaccess +---------+-------------+--------------- + func4 | | c +(1 row) + +-- change prodataaccess option +create or replace function func4(int, int) returns int as +$$ + select $1 + $2; +$$ language sql modifies sql data; +CREATE FUNCTION +select proname, proargnames, prodataaccess from pg_proc where proname = 'func4'; + proname | proargnames | prodataaccess +---------+-------------+--------------- + func4 | | m +(1 row) + +-- upper case language name +create or replace function func5(int) returns int as +$$ + select $1; +$$ language "SQL"; +CREATE FUNCTION +-- check prodataaccess column +select proname, proargnames, prodataaccess from pg_proc where proname = 'func5'; + proname | proargnames | prodataaccess +---------+-------------+--------------- + func5 | | c +(1 row) + +-- alter function with data access +alter function func5(int) reads sql data; +ALTER FUNCTION +-- check prodataaccess column +select proname, proargnames, prodataaccess from pg_proc where proname = 'func5'; + proname | proargnames | prodataaccess +---------+-------------+--------------- + func5 | | r +(1 row) + +-- alter function with data access +alter function func5(int) modifies sql data; +ALTER FUNCTION +-- check prodataaccess column +select proname, proargnames, prodataaccess from pg_proc where proname = 'func5'; + proname | proargnames | prodataaccess +---------+-------------+--------------- + func5 | | m +(1 row) + +-- alter function with data access +alter function func5(int) no sql; +psql:/tmp/TestUDF_TestUDFExtension.sql:90: ERROR: conflicting options +HINT: A SQL function cannot specify NO SQL. +-- alter function with data access +alter function func5(int) volatile contains sql; +ALTER FUNCTION +alter function func5(int) immutable reads sql data; +psql:/tmp/TestUDF_TestUDFExtension.sql:95: ERROR: conflicting options +HINT: IMMUTABLE conflicts with READS SQL DATA. +alter function func5(int) immutable modifies sql data; +psql:/tmp/TestUDF_TestUDFExtension.sql:96: ERROR: conflicting options +HINT: IMMUTABLE conflicts with MODIFIES SQL DATA. +-- data_access indicators for plpgsql +drop function func1(int, int); +DROP FUNCTION +create or replace function func1(int, int) returns varchar as $$ +declare + v_name varchar(20) DEFAULT 'zzzzz'; +begin + select relname from pg_class into v_name where oid=$1; + return v_name; +end; +$$ language plpgsql reads sql data; +CREATE FUNCTION +select proname, proargnames, prodataaccess from pg_proc where proname = 'func1'; + proname | proargnames | prodataaccess +---------+-------------+--------------- + func1 | | r +(1 row) + +-- check conflicts +drop function func1(int, int); +DROP FUNCTION +create function func1(int, int) returns int as +$$ + select $1 + $2; +$$ language sql immutable no sql; +psql:/tmp/TestUDF_TestUDFExtension.sql:116: ERROR: conflicting options +HINT: A SQL function cannot specify NO SQL. +create function func1(int, int) returns int as +$$ + select $1 + $2; +$$ language sql immutable reads sql data; +psql:/tmp/TestUDF_TestUDFExtension.sql:121: ERROR: conflicting options +HINT: IMMUTABLE conflicts with READS SQL DATA. +drop function func2(anyelement, anyelement, bool); +DROP FUNCTION +drop function func3(); +DROP FUNCTION +drop function func4(int, int); +DROP FUNCTION +drop function func5(int); +DROP FUNCTION http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2e8ff72/src/test/feature/udf/ans/function_set_returning.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/udf/ans/function_set_returning.ans b/src/test/feature/udf/ans/function_set_returning.ans new file mode 100755 index 0000000..4544a2d --- /dev/null +++ b/src/test/feature/udf/ans/function_set_returning.ans @@ -0,0 +1,287 @@ +-- start_ignore +SET SEARCH_PATH=TestUDF_TestUDFSetReturning; +SET +-- end_ignore +DROP LANGUAGE IF EXISTS plpythonu CASCADE; +DROP LANGUAGE +CREATE LANGUAGE plpythonu; +CREATE LANGUAGE +CREATE TABLE foo2(fooid int, f2 int); +CREATE TABLE +INSERT INTO foo2 VALUES(1, 11); +INSERT 0 1 +INSERT INTO foo2 VALUES(2, 22); +INSERT 0 1 +INSERT INTO foo2 VALUES(1, 111); +INSERT 0 1 +CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL; +CREATE FUNCTION +select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2; + fooid | f2 +-------+----- + 1 | 11 + 1 | 111 +(2 rows) + +CREATE TABLE foo (fooid int, foosubid int, fooname text); +CREATE TABLE +INSERT INTO foo VALUES(1,1,'Joe'); +INSERT 0 1 +INSERT INTO foo VALUES(1,2,'Ed'); +INSERT 0 1 +INSERT INTO foo VALUES(2,1,'Mary'); +INSERT 0 1 +CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL; +CREATE FUNCTION +SELECT * FROM getfoo(1) AS t1; + t1 +---- + 1 + 1 +(2 rows) + +CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); +CREATE VIEW +SELECT * FROM vw_getfoo; + getfoo +-------- + 1 + 1 +(2 rows) + +DROP VIEW vw_getfoo; +DROP VIEW +DROP FUNCTION getfoo(int); +DROP FUNCTION +DROP FUNCTION foot(int); +DROP FUNCTION +DROP TABLE foo2; +DROP TABLE +DROP TABLE foo; +DROP TABLE +-- setof as a paramater -- +CREATE TYPE numtype as (i int, j int); +CREATE TYPE +CREATE FUNCTION g_numtype(x setof numtype) RETURNS setof numtype AS $$ select $1; $$ LANGUAGE SQL; +CREATE FUNCTION +DROP FUNCTION g_numtype(x setof numtype); +DROP FUNCTION +DROP TYPE numtype; +DROP TYPE +-- +-- Set functions samples from Madlib +-- +create function combination(s text) returns setof text[] as $$ +x = s.split(',') + +def subset(myset, N): + left = [] + right = [] + for i in range(0, len(myset)): + if ((1 << i) & N) > 0: + left.append(myset[i]) + else: + right.append(myset[i]) + return (', '.join(left), ', '.join(right)) + +for i in range(1, (1 << len(x)) - 2): + yield subset(x, i) +$$ language plpythonu strict; +CREATE FUNCTION +select x[1] || ' => ' || x[2] from combination('a,b,c,d') x; + ?column? +-------------- + a => b, c, d + b => a, c, d + a, b => c, d + c => a, b, d + a, c => b, d + b, c => a, d + a, b, c => d + d => a, b, c + a, d => b, c + b, d => a, c + a, b, d => c + c, d => a, b + a, c, d => b +(13 rows) + +CREATE TABLE rules(rule text) distributed by (rule); +CREATE TABLE +insert into rules values('a,b,c'); +INSERT 0 1 +insert into rules values('d,e'); +INSERT 0 1 +insert into rules values('f,g,h,i,j'); +INSERT 0 1 +insert into rules values('k,l,m'); +INSERT 0 1 +SELECT rule, combination(rule) from rules order by 1,2; + rule | combination +-----------+-------------------- + a,b,c | {a,"b, c"} + a,b,c | {"a, b",c} + a,b,c | {"a, c",b} + a,b,c | {b,"a, c"} + a,b,c | {c,"a, b"} + d,e | {d,e} + f,g,h,i,j | {f,"g, h, i, j"} + f,g,h,i,j | {"f, g","h, i, j"} + f,g,h,i,j | {"f, g, h","i, j"} + f,g,h,i,j | {"f, g, h, i",j} + f,g,h,i,j | {"f, g, h, j",i} + f,g,h,i,j | {"f, g, i","h, j"} + f,g,h,i,j | {"f, g, i, j",h} + f,g,h,i,j | {"f, g, j","h, i"} + f,g,h,i,j | {"f, h","g, i, j"} + f,g,h,i,j | {"f, h, i","g, j"} + f,g,h,i,j | {"f, h, i, j",g} + f,g,h,i,j | {"f, h, j","g, i"} + f,g,h,i,j | {"f, i","g, h, j"} + f,g,h,i,j | {"f, i, j","g, h"} + f,g,h,i,j | {"f, j","g, h, i"} + f,g,h,i,j | {g,"f, h, i, j"} + f,g,h,i,j | {"g, h","f, i, j"} + f,g,h,i,j | {"g, h, i","f, j"} + f,g,h,i,j | {"g, h, j","f, i"} + f,g,h,i,j | {"g, i","f, h, j"} + f,g,h,i,j | {"g, i, j","f, h"} + f,g,h,i,j | {"g, j","f, h, i"} + f,g,h,i,j | {h,"f, g, i, j"} + f,g,h,i,j | {"h, i","f, g, j"} + f,g,h,i,j | {"h, i, j","f, g"} + f,g,h,i,j | {"h, j","f, g, i"} + f,g,h,i,j | {i,"f, g, h, j"} + f,g,h,i,j | {"i, j","f, g, h"} + f,g,h,i,j | {j,"f, g, h, i"} + k,l,m | {k,"l, m"} + k,l,m | {"k, l",m} + k,l,m | {"k, m",l} + k,l,m | {l,"k, m"} + k,l,m | {m,"k, l"} +(40 rows) + +DROP TABLE IF EXISTS foo; +psql:/tmp/TestUDF_TestUDFSetReturning.sql:69: NOTICE: table "foo" does not exist, skipping +DROP TABLE +CREATE TABLE foo AS SELECT rule, combination(rule) from rules distributed by (rule); +SELECT 40 +-- UDT as argument/return type of set returning UDF +CREATE TYPE r_type as (a int, b text); +CREATE TYPE +CREATE FUNCTION f1(x r_type) returns setof text as $$ SELECT $1.b from generate_series(1, $1.a) $$ language sql; +CREATE FUNCTION +CREATE FUNCTION f2(x int) returns setof r_type as $$ SELECT i, 'hello'::text from generate_series(1, $1) i $$ language sql; +CREATE FUNCTION +CREATE FUNCTION f3(x r_type) returns setof r_type as $$ SELECT $1 from generate_series(1, $1.a) $$ language sql; +CREATE FUNCTION +SELECT f1(row(2, 'hello')); + f1 +------- + hello + hello +(2 rows) + +SELECT f2(2); + f2 +----------- + (1,hello) + (2,hello) +(2 rows) + +SELECT f3(row(2,'hello')); + f3 +----------- + (2,hello) + (2,hello) +(2 rows) + +SELECT * FROM f1(row(2,'hello')); + f1 +------- + hello + hello +(2 rows) + +SELECT * FROM f2(2); + a | b +---+------- + 1 | hello + 2 | hello +(2 rows) + +SELECT * FROM f3(row(2,'hello')); + a | b +---+------- + 2 | hello + 2 | hello +(2 rows) + +CREATE TABLE t1 as SELECT i from generate_series(1,5) i distributed by (i); +SELECT 5 +SELECT i, f1(row(i, 'hello')) from t1; + i | f1 +---+------- + 1 | hello + 3 | hello + 3 | hello + 3 | hello + 5 | hello + 5 | hello + 5 | hello + 5 | hello + 5 | hello + 2 | hello + 2 | hello + 4 | hello + 4 | hello + 4 | hello + 4 | hello +(15 rows) + +SELECT i, f2(i) from t1; + i | f2 +---+----------- + 1 | (1,hello) + 3 | (1,hello) + 3 | (2,hello) + 3 | (3,hello) + 5 | (1,hello) + 5 | (2,hello) + 5 | (3,hello) + 5 | (4,hello) + 5 | (5,hello) + 2 | (1,hello) + 2 | (2,hello) + 4 | (1,hello) + 4 | (2,hello) + 4 | (3,hello) + 4 | (4,hello) +(15 rows) + +SELECT i, f3(row(i,'hello')) from t1; + i | f3 +---+----------- + 1 | (1,hello) + 4 | (4,hello) + 4 | (4,hello) + 4 | (4,hello) + 4 | (4,hello) + 3 | (3,hello) + 3 | (3,hello) + 3 | (3,hello) + 5 | (5,hello) + 5 | (5,hello) + 5 | (5,hello) + 5 | (5,hello) + 5 | (5,hello) + 2 | (2,hello) + 2 | (2,hello) +(15 rows) + +CREATE TABLE o1 as SELECT f1(row(i, 'hello')) from t1; +SELECT 15 +CREATE TABLE o2 as SELECT f2(i) from t1; +SELECT 15 +CREATE TABLE o3 as SELECT f3(row(i,'hello')) from t1; +SELECT 15 http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2e8ff72/src/test/feature/udf/sql/function_basics.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/udf/sql/function_basics.sql b/src/test/feature/udf/sql/function_basics.sql new file mode 100755 index 0000000..a050b22 --- /dev/null +++ b/src/test/feature/udf/sql/function_basics.sql @@ -0,0 +1,439 @@ +-- SETUP +DROP TABLE IF EXISTS foo; +CREATE TABLE foo AS SELECT * FROM generate_series(1, 10) x; +CREATE FUNCTION f(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; + + + +-- DDL, CREATE FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +SELECT proname FROM pg_proc WHERE proname = 'g'; +SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; +DROP FUNCTION g(int); + + + +-- DDL, CREATE OR REPLACE FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +SELECT proname FROM pg_proc WHERE proname = 'g'; +SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; +CREATE OR REPLACE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN (-1) * x; +END +$$ LANGUAGE PLPGSQL; +SELECT proname, prosrc FROM pg_proc WHERE proname = 'g'; +SELECT proname, prosrc FROM gp_dist_random('pg_proc') WHERE proname = 'g'; +DROP FUNCTION g(int); + + + +-- DDL, DROP FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +DROP FUNCTION g(int); +SELECT oid, proname FROM pg_proc WHERE proname = 'g'; +SELECT oid, proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + + + +-- DDL, DROP FUNCTION, NEGATIVE +DROP FUNCTION g(int); + + + +-- DDL, CREATE FUNCTION, RECORD +CREATE FUNCTION foo(int) RETURNS record AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL; +SELECT foo(5); +DROP FUNCTION foo(int); +CREATE FUNCTION foo(int) RETURNS foo AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL; +SELECT foo(5); +DROP FUNCTION foo(int); + + + +-- DDL, CREATE FUNCTION, SRF +CREATE FUNCTION g(x setof int) RETURNS INT + AS $$ SELECT 1 $$ LANGUAGE SQL; +DROP FUNCTION g(setof int); +CREATE FUNCTION g() RETURNS setof INT + AS $$ SELECT 1 $$ LANGUAGE SQL; +DROP FUNCTION g(); + + + +-- DDL, CREATE FUNCTION, TABLE, NEGATIVE +CREATE FUNCTION g() RETURNS TABLE(x int) + AS $$ SELECT * FROM foo $$ LANGUAGE SQL; +DROP FUNCTION g(); +CREATE FUNCTION g(anytable) RETURNS int + AS 'does_not_exist', 'does_not_exist' LANGUAGE C; + + + +-- DDL, CREATE FUNCTION, SECURITY DEFINER +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE SECURITY DEFINER; +DROP FUNCTION g(int); + + +-- DDL, ALTER FUNCTION +-- DDL, STRICT +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +SELECT g(NULL); +ALTER FUNCTION g(int) STRICT; +SELECT g(NULL); +DROP FUNCTION g(int); + + + +-- DDL, ALTER FUNCTION, OWNER +CREATE ROLE superuser SUPERUSER; +CREATE ROLE u1; +SET ROLE superuser; +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g'; +ALTER FUNCTION g(int) OWNER TO u1; +SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g'; +DROP FUNCTION g(int); +RESET ROLE; +DROP ROLE u1; +DROP ROLE superuser; + + + +-- DDL, ALTER FUNCTION, RENAME +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +SELECT g(0); +ALTER FUNCTION g(int) RENAME TO h; +SELECT h(0); +DROP FUNCTION h(int); + + + +-- DDL, ALTER FUNCTION, SET SCHEMA +CREATE SCHEMA bar; +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +SELECT g(0); +ALTER FUNCTION g(int) SET SCHEMA bar; +SELECT bar.g(0); +DROP SCHEMA bar CASCADE; + + + +-- DDL, ALTER FUNCTION, SECURITY DEFINER +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +ALTER FUNCTION g(int) SECURITY DEFINER; +DROP FUNCTION g(int); + + + +-- DCL, GRANT/REVOKE +-- GRANT { EXECUTE | ALL [ PRIVILEGES ] } +-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] +-- TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] +-- REVOKE [ GRANT OPTION FOR ] +-- { EXECUTE | ALL [ PRIVILEGES ] } +-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] +-- FROM { username | GROUP groupname | PUBLIC } [, ...] +-- [ CASCADE | RESTRICT ] + +-- DCL, GRANT/REVOKE, EXECUTE +CREATE ROLE superuser SUPERUSER; +SET ROLE superuser; +CREATE ROLE u1; +GRANT SELECT ON TABLE foo TO u1; +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +SELECT proacl FROM pg_proc where proname = 'g'; +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +SELECT proacl FROM pg_proc where proname = 'g'; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +GRANT EXECUTE ON FUNCTION g(int) TO u1; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +REVOKE EXECUTE ON FUNCTION g(int) FROM u1; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +RESET ROLE; +DROP FUNCTION g(int); +REVOKE SELECT ON TABLE foo FROM u1; +DROP ROLE u1; +DROP ROLE superuser; + + + +-- DCL, GRANT/REVOKE, PUBLIC +CREATE ROLE superuser SUPERUSER; +SET ROLE superuser; +CREATE ROLE u1; +GRANT SELECT ON TABLE foo TO u1; +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +SELECT proacl FROM pg_proc where proname = 'g'; +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +SELECT proacl FROM pg_proc where proname = 'g'; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +GRANT EXECUTE ON FUNCTION g(int) TO PUBLIC; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +REVOKE EXECUTE ON FUNCTION g(int) FROM PUBLIC; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +RESET ROLE; +DROP FUNCTION g(int); +REVOKE SELECT ON TABLE foo FROM u1; +DROP ROLE u1; +DROP ROLE superuser; + + + +-- DCL, GRANT/REVOKE, Groups +CREATE ROLE superuser SUPERUSER; +SET ROLE superuser; +CREATE ROLE u1; +CREATE ROLE u2 IN GROUP u1; +GRANT SELECT ON TABLE foo TO u1; +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +SELECT proacl FROM pg_proc where proname = 'g'; +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +SELECT proacl FROM pg_proc where proname = 'g'; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE u2; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +GRANT EXECUTE ON FUNCTION g(int) TO u1; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u2; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +REVOKE EXECUTE ON FUNCTION g(int) FROM u1; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u2; +SELECT g(1); +SELECT count(g(x)) FROM foo; +RESET ROLE; +DROP FUNCTION g(int); +REVOKE SELECT ON TABLE foo FROM u1; +DROP ROLE u1; +DROP ROLE u2; +DROP ROLE superuser; + + + +-- DCL, GRANT/REVOKE, WITH GRANT OPTION +CREATE ROLE superuser SUPERUSER; +SET ROLE superuser; +CREATE ROLE u1; +CREATE ROLE u2; +GRANT SELECT ON TABLE foo TO PUBLIC; +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +SELECT proacl FROM pg_proc where proname = 'g'; +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +SELECT proacl FROM pg_proc where proname = 'g'; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE u2; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +GRANT ALL ON FUNCTION g(int) TO u1 WITH GRANT OPTION; +SET ROLE u1; +GRANT ALL ON FUNCTION g(int) TO u2; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE u2; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE superuser; +REVOKE ALL ON FUNCTION g(int) FROM u1 CASCADE; +SELECT proacl FROM pg_proc where proname = 'g'; +SET ROLE u1; +SELECT g(1); +SELECT count(g(x)) FROM foo; +SET ROLE u2; +SELECT g(1); +SELECT count(g(x)) FROM foo; +RESET ROLE; +DROP FUNCTION g(int); +REVOKE SELECT ON TABLE foo FROM PUBLIC; +DROP ROLE u1; +DROP ROLE u2; +DROP ROLE superuser; + + + +-- DML, CaseExpr +SELECT CASE WHEN x % 2 = 0 THEN f(x) ELSE 0 END FROM foo ORDER BY x; + + + +-- DML, OpExpr +SELECT f(x) + f(x) FROM foo ORDER BY x; +SELECT f(x) + f(x) + f(x) FROM foo ORDER BY x; +SELECT f(x) + f(x) - f(x) FROM foo ORDER BY x; + + + +-- DML, FuncExpr +CREATE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +SELECT g(f(x)) FROM foo ORDER BY x; +DROP FUNCTION g(int); + +-- DML, BoolExpr +SELECT x % 2 = 0 AND f(x) % 2 = 1 FROM foo ORDER BY x; + + + +-- DML, DistinctExpr +SELECT x IS DISTINCT FROM f(x) from foo ORDER BY x; + + + +-- DML, PercentileExpr +SELECT MEDIAN(f(x)) FROM foo; + + + +-- DML, Complex Expression +CREATE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +SELECT CASE + WHEN x % 2 = 0 THEN g(g(x)) + g(g(x)) + WHEN f(x) % 2 = 1 THEN g(g(x)) - g(g(x)) + END FROM foo ORDER BY x; +DROP FUNCTION g(int); + + + +-- DML, Qual +SELECT x FROM foo WHERE f(x) % 2 = 0 ORDER BY x; + + + +-- DML, FROM +SELECT * FROM f(5); + + + +-- DML, Grouping +SELECT DISTINCT f(x) FROM foo ORDER BY f(x); +SELECT f(x) FROM foo GROUP BY f(x) ORDER BY f(x); + + + +-- DML, Join +SELECT a.x FROM foo a, foo b WHERE f(a.x) = f(b.x) ORDER BY x; +SELECT a.x FROM foo a JOIN foo b ON f(a.x) = f(b.x) ORDER BY x; + + + +-- DML, Windowing +SELECT avg(x) OVER (PARTITION BY f(x)) FROM foo ORDER BY x; + + + +-- DML, CTE +WITH t AS (SELECT x from foo) + SELECT f(x) from t ORDER BY x; + + + +-- DML, InitPlan +SELECT UNNEST(ARRAY(SELECT x FROM foo)) ORDER BY 1; +SELECT UNNEST(ARRAY(SELECT f(1))); + + + +-- PROPERTIES, VOLATILITY, IMMUTABLE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL IMMUTABLE; +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; +DROP FUNCTION g(); + + + +-- PROPERTIES, VOLATILITY, STABLE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL STABLE; +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; +DROP FUNCTION g(); + + + +-- PROPERTIES, VOLATILITY, VOLATILE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL VOLATILE; +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; +DROP FUNCTION g(); + +----------------- +-- NEGATIVE TESTS +----------------- +SELECT h(1); +-- DML, InitPlan +SELECT UNNEST(ARRAY(SELECT f(x) from foo)); + +-- LANGUAGES not yet supported +-- CREATE LANGUAGE plr; +-- CREATE LANGUAGE plpython; +-- CREATE LANGUAGE pljava; +-- CREATE LANGUAGE plperl; + +-- NESTED FUNCTION +CREATE FUNCTION inner(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION outer(x INT) RETURNS INT AS $$ +BEGIN +RETURN inner(x); +END +$$ LANGUAGE PLPGSQL; +SELECT outer(0); +SELECT outer(0) FROM foo; +DROP FUNCTION outer(int); +DROP FUNCTION inner(int); + + + +-- TEARDOWN +DROP TABLE foo; + + + +-- HAWQ-510 +drop table if exists testEntryDB; +create table testEntryDB(key int, value int) distributed randomly; +insert into testEntryDB values(1, 0); +select t2.key, t2.value +from (select key, value from testEntryDB where value = 0) as t1, + (select generate_series(1,2)::int as key, 0::int as value) as t2 +where t1.value=t2.value; +drop table testEntryDB; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2e8ff72/src/test/feature/udf/sql/function_extension.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/udf/sql/function_extension.sql b/src/test/feature/udf/sql/function_extension.sql new file mode 100755 index 0000000..d3d2abc --- /dev/null +++ b/src/test/feature/udf/sql/function_extension.sql @@ -0,0 +1,123 @@ +-- ----------------------------------------------------------------- +-- Test extensions to functions (MPP-16060) +-- 1. data access indicators +-- ----------------------------------------------------------------- + +-- test prodataaccess +create function func1(int, int) returns int as +$$ + select $1 + $2; +$$ language sql immutable contains sql; + +-- check prodataaccess column in pg_proc +select proname, prodataaccess from pg_proc where proname = 'func1'; + +-- check prodataaccess in pg_attribute +select relname, attname, attlen from pg_class c, pg_attribute +where attname = 'prodataaccess' and attrelid = c.oid and c.relname = 'pg_proc'; + +create function func2(a anyelement, b anyelement, flag bool) +returns anyelement as +$$ + select $1 + $2; +$$ language sql reads sql data; + +-- check prodataaccess column in pg_proc +select proname, prodataaccess from pg_proc where proname = 'func2'; + +create function func3() returns oid as +$$ + select oid from pg_class where relname = 'pg_type'; +$$ language sql modifies sql data volatile; + +-- check prodataaccess column in pg_proc +select proname, prodataaccess from pg_proc where proname = 'func3'; + +-- check default value of prodataaccess +drop function func1(int, int); +create function func1(int, int) returns varchar as $$ +declare + v_name varchar(20) DEFAULT 'zzzzz'; +begin + select relname from pg_class into v_name where oid=$1; + return v_name; +end; +$$ language plpgsql; + +select proname, proargnames, prodataaccess from pg_proc where proname = 'func1'; + +create function func4(int, int) returns int as +$$ + select $1 + $2; +$$ language sql; + +-- check prodataaccess column +select proname, proargnames, prodataaccess from pg_proc where proname = 'func4'; + +-- change prodataaccess option +create or replace function func4(int, int) returns int as +$$ + select $1 + $2; +$$ language sql modifies sql data; + +select proname, proargnames, prodataaccess from pg_proc where proname = 'func4'; + +-- upper case language name +create or replace function func5(int) returns int as +$$ + select $1; +$$ language "SQL"; + +-- check prodataaccess column +select proname, proargnames, prodataaccess from pg_proc where proname = 'func5'; + +-- alter function with data access +alter function func5(int) reads sql data; + +-- check prodataaccess column +select proname, proargnames, prodataaccess from pg_proc where proname = 'func5'; + +-- alter function with data access +alter function func5(int) modifies sql data; + +-- check prodataaccess column +select proname, proargnames, prodataaccess from pg_proc where proname = 'func5'; + +-- alter function with data access +alter function func5(int) no sql; + +-- alter function with data access +alter function func5(int) volatile contains sql; + +alter function func5(int) immutable reads sql data; +alter function func5(int) immutable modifies sql data; + +-- data_access indicators for plpgsql +drop function func1(int, int); +create or replace function func1(int, int) returns varchar as $$ +declare + v_name varchar(20) DEFAULT 'zzzzz'; +begin + select relname from pg_class into v_name where oid=$1; + return v_name; +end; +$$ language plpgsql reads sql data; + +select proname, proargnames, prodataaccess from pg_proc where proname = 'func1'; + +-- check conflicts +drop function func1(int, int); +create function func1(int, int) returns int as +$$ + select $1 + $2; +$$ language sql immutable no sql; + +create function func1(int, int) returns int as +$$ + select $1 + $2; +$$ language sql immutable reads sql data; + +drop function func2(anyelement, anyelement, bool); +drop function func3(); +drop function func4(int, int); +drop function func5(int); http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/a2e8ff72/src/test/feature/udf/sql/function_set_returning.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/udf/sql/function_set_returning.sql b/src/test/feature/udf/sql/function_set_returning.sql new file mode 100755 index 0000000..3beb31f --- /dev/null +++ b/src/test/feature/udf/sql/function_set_returning.sql @@ -0,0 +1,93 @@ +DROP LANGUAGE IF EXISTS plpythonu CASCADE; +CREATE LANGUAGE plpythonu; + +CREATE TABLE foo2(fooid int, f2 int); +INSERT INTO foo2 VALUES(1, 11); +INSERT INTO foo2 VALUES(2, 22); +INSERT INTO foo2 VALUES(1, 111); + +CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL; +select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2; + +CREATE TABLE foo (fooid int, foosubid int, fooname text); +INSERT INTO foo VALUES(1,1,'Joe'); +INSERT INTO foo VALUES(1,2,'Ed'); +INSERT INTO foo VALUES(2,1,'Mary'); + +CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL; +SELECT * FROM getfoo(1) AS t1; +CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); +SELECT * FROM vw_getfoo; + +DROP VIEW vw_getfoo; +DROP FUNCTION getfoo(int); +DROP FUNCTION foot(int); +DROP TABLE foo2; +DROP TABLE foo; + +-- setof as a paramater -- +CREATE TYPE numtype as (i int, j int); + +CREATE FUNCTION g_numtype(x setof numtype) RETURNS setof numtype AS $$ select $1; $$ LANGUAGE SQL; + +DROP FUNCTION g_numtype(x setof numtype); +DROP TYPE numtype; + +-- +-- Set functions samples from Madlib +-- +create function combination(s text) returns setof text[] as $$ +x = s.split(',') + +def subset(myset, N): + left = [] + right = [] + for i in range(0, len(myset)): + if ((1 << i) & N) > 0: + left.append(myset[i]) + else: + right.append(myset[i]) + return (', '.join(left), ', '.join(right)) + +for i in range(1, (1 << len(x)) - 2): + yield subset(x, i) +$$ language plpythonu strict; + +select x[1] || ' => ' || x[2] from combination('a,b,c,d') x; + +CREATE TABLE rules(rule text) distributed by (rule); +insert into rules values('a,b,c'); +insert into rules values('d,e'); +insert into rules values('f,g,h,i,j'); +insert into rules values('k,l,m'); + +SELECT rule, combination(rule) from rules order by 1,2; + +DROP TABLE IF EXISTS foo; +CREATE TABLE foo AS SELECT rule, combination(rule) from rules distributed by (rule); + + +-- UDT as argument/return type of set returning UDF +CREATE TYPE r_type as (a int, b text); + +CREATE FUNCTION f1(x r_type) returns setof text as $$ SELECT $1.b from generate_series(1, $1.a) $$ language sql; +CREATE FUNCTION f2(x int) returns setof r_type as $$ SELECT i, 'hello'::text from generate_series(1, $1) i $$ language sql; +CREATE FUNCTION f3(x r_type) returns setof r_type as $$ SELECT $1 from generate_series(1, $1.a) $$ language sql; + +SELECT f1(row(2, 'hello')); +SELECT f2(2); +SELECT f3(row(2,'hello')); + +SELECT * FROM f1(row(2,'hello')); +SELECT * FROM f2(2); +SELECT * FROM f3(row(2,'hello')); + +CREATE TABLE t1 as SELECT i from generate_series(1,5) i distributed by (i); + +SELECT i, f1(row(i, 'hello')) from t1; +SELECT i, f2(i) from t1; +SELECT i, f3(row(i,'hello')) from t1; + +CREATE TABLE o1 as SELECT f1(row(i, 'hello')) from t1; +CREATE TABLE o2 as SELECT f2(i) from t1; +CREATE TABLE o3 as SELECT f3(row(i,'hello')) from t1;
