hawq-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From h...@apache.org
Subject [1/4] incubator-hawq git commit: HAWQ-769. Migrate function, function_extensions and set_functions of user-defined function test from installcheck to new feature test framework
Date Wed, 15 Jun 2016 01:30:27 GMT
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 <rhuo@pivotal.io>
Authored: Tue Jun 14 16:12:51 2016 +0800
Committer: Ruilong Huo <rhuo@pivotal.io>
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;


Mime
View raw message