hawq-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From w...@apache.org
Subject incubator-hawq git commit: HAWQ-1312. Forbid partial grant/revoke command in HAWQ side once Ranger is configured.
Date Wed, 08 Feb 2017 02:08:56 GMT
Repository: incubator-hawq
Updated Branches:
  refs/heads/master 31aff875c -> 35ed3ad38


HAWQ-1312. Forbid partial grant/revoke command in HAWQ side once Ranger is configured.


Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/35ed3ad3
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/35ed3ad3
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/35ed3ad3

Branch: refs/heads/master
Commit: 35ed3ad38c7ad0207f4e594fdbcdf4e324449c38
Parents: 31aff87
Author: Wen Lin <wlin@pivotal.io>
Authored: Wed Feb 8 10:05:56 2017 +0800
Committer: Wen Lin <wlin@pivotal.io>
Committed: Wed Feb 8 10:05:56 2017 +0800

----------------------------------------------------------------------
 src/backend/catalog/aclchk.c                    |   41 +
 src/test/feature/UDF/TestUDF.cpp                |   24 +-
 .../UDF/ans/function_basics.ranger.ans.orca     | 1088 ++++++++++++++++++
 .../UDF/ans/function_basics.ranger.ans.planner  | 1076 +++++++++++++++++
 4 files changed, 2225 insertions(+), 4 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/35ed3ad3/src/backend/catalog/aclchk.c
----------------------------------------------------------------------
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 667aa61..3ab3248 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -285,6 +285,31 @@ restrict_and_check_grant(bool is_grant, AclMode avail_goptions, bool all_privs,
 }
 
 /*
+ * Check if this object's ACL is checked natively.
+ */
+bool checkACLNative(GrantObjectType type, Oid oid)
+{
+	AclObjectKind kind = MAX_ACL_KIND;
+	if (type == ACL_OBJECT_RELATION)
+	{
+		kind = ACL_KIND_CLASS;
+	}
+	else if (type == ACL_OBJECT_NAMESPACE)
+	{
+		kind = ACL_KIND_NAMESPACE;
+	}
+	else if (type == ACL_OBJECT_FUNCTION)
+	{
+		kind = ACL_KIND_PROC;
+	}
+	else
+	{
+		return false;
+	}
+	return fallBackToNativeCheck(kind, oid, GetUserId());
+}
+
+/*
  * Called to execute the utility commands GRANT and REVOKE
  */
 void
@@ -304,6 +329,22 @@ ExecuteGrantStmt(GrantStmt *stmt)
 	istmt.objtype = stmt->objtype;
 	istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects);
 
+	/*
+	 * Don't allow GRANT/REVOKE for objects managed by Ranger
+	 * if in ranger mode.
+	 */
+	if (aclType == HAWQ_ACL_RANGER)
+	{
+		foreach(cell, istmt.objects)
+		{
+			Oid oid = lfirst_oid(cell);
+			if (!checkACLNative(stmt->objtype, oid))
+			{
+				elog(ERROR, "GRANT/REVOKE is not allowed for this object in ranger mode");
+			}
+		}
+	}
+
 	/* If this is a GRANT/REVOKE on a table, expand partition references */
 	if (istmt.objtype == ACL_OBJECT_RELATION)
 	{

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/35ed3ad3/src/test/feature/UDF/TestUDF.cpp
----------------------------------------------------------------------
diff --git a/src/test/feature/UDF/TestUDF.cpp b/src/test/feature/UDF/TestUDF.cpp
index 7107b63..95509fb 100755
--- a/src/test/feature/UDF/TestUDF.cpp
+++ b/src/test/feature/UDF/TestUDF.cpp
@@ -18,13 +18,29 @@ 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");
+		if (util.getGUCValue("hawq_acl_type") == "standalone")
+		{
+			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.ranger.ans.orca");
+		}
 	}
 	else
 	{
-		util.execSQLFile("UDF/sql/function_basics.sql",
-		                 "UDF/ans/function_basics.ans.planner");
+		if (util.getGUCValue("hawq_acl_type") == "standalone")
+		{
+			util.execSQLFile("UDF/sql/function_basics.sql",
+							 "UDF/ans/function_basics.ans.planner");
+		}
+		else
+		{
+			util.execSQLFile("UDF/sql/function_basics.sql",
+							 "UDF/ans/function_basics.ranger.ans.planner");
+		}
 	}
 }
 

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/35ed3ad3/src/test/feature/UDF/ans/function_basics.ranger.ans.orca
----------------------------------------------------------------------
diff --git a/src/test/feature/UDF/ans/function_basics.ranger.ans.orca b/src/test/feature/UDF/ans/function_basics.ranger.ans.orca
new file mode 100644
index 0000000..5523093
--- /dev/null
+++ b/src/test/feature/UDF/ans/function_basics.ranger.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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:150: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:153: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:161: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:167: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:174: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:184: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:187: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:195: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:201: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:208: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:219: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:222: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:230: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:236: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:243: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:255: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:258: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:266: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:277: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:287: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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/35ed3ad3/src/test/feature/UDF/ans/function_basics.ranger.ans.planner
----------------------------------------------------------------------
diff --git a/src/test/feature/UDF/ans/function_basics.ranger.ans.planner b/src/test/feature/UDF/ans/function_basics.ranger.ans.planner
new file mode 100644
index 0000000..818f424
--- /dev/null
+++ b/src/test/feature/UDF/ans/function_basics.ranger.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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:150: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:153: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:161: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:167: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:174: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:184: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:187: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:195: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:201: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:208: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:219: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:222: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:230: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:236: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:243: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:255: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:258: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:266: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:277: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl 
+--------
+ 
+(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;
+psql:/tmp/TestUDF_TestUDFBasics.sql:287: ERROR:  GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343)
+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
+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


Mime
View raw message