HAWQ-814. Enhance user-defined function by migrating create_function_1 of UDF 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/49fd529a Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/49fd529a Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/49fd529a Branch: refs/heads/master Commit: 49fd529aa01d54199465639060c01577bca06981 Parents: c66cfba Author: Ruilong Huo <r...@pivotal.io> Authored: Tue Jun 28 09:49:05 2016 +0800 Committer: Ruilong Huo <r...@pivotal.io> Committed: Tue Jul 5 09:40:03 2016 +0800 ---------------------------------------------------------------------- src/test/feature/Makefile | 12 +- src/test/feature/UDF/TestUDF.cpp | 63 + .../feature/UDF/ans/function_basics.ans.orca | 1088 +++++++++++++++ .../feature/UDF/ans/function_basics.ans.planner | 1076 +++++++++++++++ .../UDF/ans/function_creation.ans.source | 96 ++ src/test/feature/UDF/ans/function_extension.ans | 183 +++ .../feature/UDF/ans/function_set_returning.ans | 287 ++++ src/test/feature/UDF/lib/Makefile | 43 + src/test/feature/UDF/lib/function.c | 1245 ++++++++++++++++++ src/test/feature/UDF/sql/function_basics.sql | 439 ++++++ .../UDF/sql/function_creation.sql.source | 79 ++ src/test/feature/UDF/sql/function_extension.sql | 123 ++ .../feature/UDF/sql/function_set_returning.sql | 93 ++ src/test/feature/udf/TestUDF.cpp | 32 - src/test/feature/udf/ans/function_basics.ans | 1088 --------------- 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 -- 20 files changed, 4824 insertions(+), 2248 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/Makefile ---------------------------------------------------------------------- diff --git a/src/test/feature/Makefile b/src/test/feature/Makefile index 82a3dc3..e97716c 100644 --- a/src/test/feature/Makefile +++ b/src/test/feature/Makefile @@ -15,16 +15,22 @@ override LDFLAGS += -L/usr/local/lib -L/usr/lib -L$(abs_top_srcdir)/src/test/fea PROG = test_main.cpp $(wildcard */*.cpp) RM = rm -rf -.PHONY: all distclean clean doc +.PHONY: all sharelib sharelibclean distclean clean doc -all: +all: sharelib $(MAKE) -C lib all $(CXX) $(CPPFLAGS) $(CXXFLAGS) $(PROG) $(LDFLAGS) $(LIBS) -o feature-test +sharelib: + cd UDF/lib || exit 1; $(MAKE) || exit 2; $(MAKE) clean || exit 3 + +sharelibclean: + cd UDF/lib || exit 1; $(RM) *.o *.so || exit 2 + doc: doxygen doxygen_template -clean distclean: +clean distclean: sharelibclean $(RM) feature-test $(RM) feature-test.dSYM $(RM) doc http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/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..ce7f158 --- /dev/null +++ b/src/test/feature/UDF/TestUDF.cpp @@ -0,0 +1,63 @@ +#include "gtest/gtest.h" + +#include "lib/sql_util.h" +#include "lib/file_replace.h" + + +class TestUDF: public ::testing::Test +{ + public: + TestUDF() {} + ~TestUDF() {} +}; + +TEST_F(TestUDF, TestUDFBasics) +{ + hawq::test::SQLUtility util; + if (util.getGUCValue("optimizer") == "on") + { + util.execSQLFile("UDF/sql/function_basics.sql", + "UDF/ans/function_basics.ans.orca"); + } + else + { + util.execSQLFile("UDF/sql/function_basics.sql", + "UDF/ans/function_basics.ans.planner"); + } +} + +TEST_F(TestUDF, TestUDFCreation) +{ + // preprocess source files to get sql/ans files + hawq::test::SQLUtility util; + std::string d_feature_test_root(util.getTestRootPath()); + std::string f_sql_tpl(d_feature_test_root + "/UDF/sql/function_creation.sql.source"); + std::string f_ans_tpl(d_feature_test_root + "/UDF/ans/function_creation.ans.source"); + std::string f_sql(d_feature_test_root + "/UDF/sql/function_creation.sql"); + std::string f_ans(d_feature_test_root + "/UDF/ans/function_creation.ans"); + + hawq::test::FileReplace frep; + std::unordered_map<std::string, std::string> strs_src_dst; + strs_src_dst["@SHARE_LIBRARY_PATH@"] = d_feature_test_root + "/UDF/lib/function.so"; + + frep.replace(f_sql_tpl, f_sql, strs_src_dst); + frep.replace(f_ans_tpl, f_ans, strs_src_dst); + + // run sql file to get ans file and then diff it with out file + util.execSQLFile("UDF/sql/function_creation.sql", + "UDF/ans/function_creation.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/49fd529a/src/test/feature/UDF/ans/function_basics.ans.orca ---------------------------------------------------------------------- diff --git a/src/test/feature/UDF/ans/function_basics.ans.orca b/src/test/feature/UDF/ans/function_basics.ans.orca new file mode 100755 index 0000000..ff45af2 --- /dev/null +++ b/src/test/feature/UDF/ans/function_basics.ans.orca @@ -0,0 +1,1088 @@ +-- 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; + outer +------- + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 +(10 rows) + +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/49fd529a/src/test/feature/UDF/ans/function_basics.ans.planner ---------------------------------------------------------------------- diff --git a/src/test/feature/UDF/ans/function_basics.ans.planner b/src/test/feature/UDF/ans/function_basics.ans.planner new file mode 100755 index 0000000..763223f --- /dev/null +++ b/src/test/feature/UDF/ans/function_basics.ans.planner @@ -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=76055) +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/49fd529a/src/test/feature/UDF/ans/function_creation.ans.source ---------------------------------------------------------------------- diff --git a/src/test/feature/UDF/ans/function_creation.ans.source b/src/test/feature/UDF/ans/function_creation.ans.source new file mode 100755 index 0000000..8524e79 --- /dev/null +++ b/src/test/feature/UDF/ans/function_creation.ans.source @@ -0,0 +1,96 @@ +-- start_ignore +SET SEARCH_PATH=TestUDF_TestUDFCreation; +SET +-- end_ignore +-- +-- CREATE_FUNCTION +-- +CREATE FUNCTION widget_in(cstring) + RETURNS widget + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C IMMUTABLE STRICT; +psql:/tmp/TestUDF_TestUDFCreation.sql:11: NOTICE: type "widget" is not yet defined +DETAIL: Creating a shell type definition. +CREATE FUNCTION +CREATE FUNCTION widget_out(widget) + RETURNS cstring + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C IMMUTABLE STRICT; +psql:/tmp/TestUDF_TestUDFCreation.sql:16: NOTICE: argument type widget is only a shell +CREATE FUNCTION +CREATE FUNCTION int44in(cstring) + RETURNS city_budget + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C IMMUTABLE STRICT; +psql:/tmp/TestUDF_TestUDFCreation.sql:21: NOTICE: type "city_budget" is not yet defined +DETAIL: Creating a shell type definition. +CREATE FUNCTION +CREATE FUNCTION int44out(city_budget) + RETURNS cstring + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C IMMUTABLE STRICT; +psql:/tmp/TestUDF_TestUDFCreation.sql:26: NOTICE: argument type city_budget is only a shell +CREATE FUNCTION +CREATE FUNCTION check_primary_key () + RETURNS trigger + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C; +CREATE FUNCTION +CREATE FUNCTION check_foreign_key () + RETURNS trigger + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C; +CREATE FUNCTION +CREATE FUNCTION autoinc () + RETURNS trigger + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C; +CREATE FUNCTION +CREATE FUNCTION funny_dup17 () + RETURNS trigger + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C; +CREATE FUNCTION +CREATE FUNCTION ttdummy () + RETURNS trigger + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C; +CREATE FUNCTION +CREATE FUNCTION set_ttdummy (int4) + RETURNS int4 + AS '@SHARE_LIBRARY_PATH@' + LANGUAGE C STRICT; +CREATE FUNCTION +-- Things that shouldn't work: +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT ''not an integer'';'; +psql:/tmp/TestUDF_TestUDFCreation.sql:61: ERROR: return type mismatch in function declared to return integer +DETAIL: Actual return type is unknown. +CONTEXT: SQL function "test1" +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'not even SQL'; +psql:/tmp/TestUDF_TestUDFCreation.sql:64: 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;'; +psql:/tmp/TestUDF_TestUDFCreation.sql:67: ERROR: return type mismatch in function declared to return integer +DETAIL: Final SELECT must return exactly one column. +CONTEXT: SQL function "test1" +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'SELECT $2;'; +psql:/tmp/TestUDF_TestUDFCreation.sql:70: ERROR: there is no parameter $2 +LINE 2: AS 'SELECT $2;'; + ^ +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL + AS 'a', 'b'; +psql:/tmp/TestUDF_TestUDFCreation.sql:73: ERROR: only one AS item needed for language "sql" +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C + AS 'nosuchfile'; +psql:/tmp/TestUDF_TestUDFCreation.sql:76: ERROR: could not access file "nosuchfile": No such file or directory +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C + AS '@SHARE_LIBRARY_PATH@', 'nosuchsymbol'; +psql:/tmp/TestUDF_TestUDFCreation.sql:79: ERROR: could not find function "nosuchsymbol" in file "@SHARE_LIBRARY_PATH@" +CREATE FUNCTION test1 (int) RETURNS int LANGUAGE internal + AS 'nosuch'; +psql:/tmp/TestUDF_TestUDFCreation.sql:82: ERROR: there is no built-in function named "nosuch" http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/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/49fd529a/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/49fd529a/src/test/feature/UDF/lib/Makefile ---------------------------------------------------------------------- diff --git a/src/test/feature/UDF/lib/Makefile b/src/test/feature/UDF/lib/Makefile new file mode 100755 index 0000000..ccbf99b --- /dev/null +++ b/src/test/feature/UDF/lib/Makefile @@ -0,0 +1,43 @@ +top_builddir = ../../../../.. +include $(top_builddir)/src/Makefile.global + +OS = $(shell uname) + +CXX = gcc +CXXFLAGS = -Wall -O1 -g -std=gnu99 -Wmissing-prototypes -Wpointer-arith -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fPIC + +CC = gcc +CFLAGS = -Wall -O1 -g -std=gnu99 -Wmissing-prototypes -Wpointer-arith -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fPIC + +CPPFLAGS = -I$(abs_top_srcdir)/src/include +CPPFLAGS += -I$(abs_top_srcdir)/depends/libhdfs3/build/install$(prefix)/include +CPPFLAGS += -I$(abs_top_srcdir)/depends/libyarn/build/install$(prefix)/include + +LDFLAGS = -L$(libdir) +LDFLAGS += -L$(abs_top_srcdir)/src/port +LDFLAGS += -L$(abs_top_builddir)/src/port +LDFLAGS += -L$(abs_top_srcdir)/depends/libhdfs3/build/install$(prefix)/lib +LDFLAGS += -L$(abs_top_srcdir)/depends/libyarn/build/install$(prefix)/lib + +POSTGRES = $(abs_top_srcdir)/src/backend/postgres + +PROG = function.c +OBJS = function.o +TARGET = function.so + +RM = rm -rf + +all: $(OBJS) $(TARGET) + +$(OBJS): $(PROG) + $(CXX) $(CXXFLAGS) $(CPPFLAGS) -c -o $(OBJS) $(PROG) + +$(TARGET): +ifeq ($(OS),Darwin) + $(CXX) $(CXXFLAGS) -bundle $(OBJS) -bundle_loader $(POSTGRES) $(LDFLAGS) -o $@ +else + $(CXX) $(CXXFLAGS) -shared $(OBJS) $(LDFLAGS) -Wl,-rpath,'$(abs_top_builddir)/lib' -o $@ +endif + +clean: + $(RM) *.o
