Mailing-List: contact commits-help@hawq.incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hawq.incubator.apache.org
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
From: huor@apache.org
To: commits@hawq.incubator.apache.org
Date: Tue, 05 Jul 2016 01:51:44 -0000
Message-Id: <b52e42a19a1a4aee87ccda89ce6aa722@git.apache.org>
Subject: [1/3] incubator-hawq git commit: HAWQ-814. Enhance user-defined
 function by migrating create_function_1 of UDF from installcheck to new
 feature test framework
archived-at: Tue, 05 Jul 2016 01:51:53 -0000

Repository: incubator-hawq
Updated Branches:
  refs/heads/master c66cfbad1 -> 49fd529aa


http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/udf/ans/function_set_returning.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/udf/ans/function_set_returning.ans b/src/test/feature/udf/ans/function_set_returning.ans
deleted file mode 100755
index 4544a2d..0000000
--- a/src/test/feature/udf/ans/function_set_returning.ans
+++ /dev/null
@@ -1,287 +0,0 @@
--- start_ignore
-SET SEARCH_PATH=TestUDF_TestUDFSetReturning;
-SET
--- end_ignore
-DROP LANGUAGE IF EXISTS plpythonu CASCADE;
-DROP LANGUAGE
-CREATE LANGUAGE plpythonu;
-CREATE LANGUAGE
-CREATE TABLE foo2(fooid int, f2 int);
-CREATE TABLE
-INSERT INTO foo2 VALUES(1, 11);
-INSERT 0 1
-INSERT INTO foo2 VALUES(2, 22);
-INSERT 0 1
-INSERT INTO foo2 VALUES(1, 111);
-INSERT 0 1
-CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
-CREATE FUNCTION
-select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
- fooid | f2  
--------+-----
-     1 |  11
-     1 | 111
-(2 rows)
-
-CREATE TABLE foo (fooid int, foosubid int, fooname text);
-CREATE TABLE
-INSERT INTO foo VALUES(1,1,'Joe');
-INSERT 0 1
-INSERT INTO foo VALUES(1,2,'Ed');
-INSERT 0 1
-INSERT INTO foo VALUES(2,1,'Mary');
-INSERT 0 1
-CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
-CREATE FUNCTION
-SELECT * FROM getfoo(1) AS t1;
- t1 
-----
-  1
-  1
-(2 rows)
-
-CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
-CREATE VIEW
-SELECT * FROM vw_getfoo;
- getfoo 
---------
-      1
-      1
-(2 rows)
-
-DROP VIEW vw_getfoo;
-DROP VIEW
-DROP FUNCTION getfoo(int);
-DROP FUNCTION
-DROP FUNCTION foot(int);
-DROP FUNCTION
-DROP TABLE foo2;
-DROP TABLE
-DROP TABLE foo;
-DROP TABLE
--- setof as a paramater --
-CREATE TYPE numtype as (i int, j int);
-CREATE TYPE
-CREATE FUNCTION g_numtype(x setof numtype) RETURNS setof numtype AS $$ select $1; $$ LANGUAGE SQL;
-CREATE FUNCTION
-DROP FUNCTION g_numtype(x setof numtype);
-DROP FUNCTION
-DROP TYPE numtype;
-DROP TYPE
---
--- Set functions samples from Madlib
---
-create function combination(s text) returns setof text[] as $$
-x = s.split(',')
-
-def subset(myset, N):
-   left = []
-   right = []
-   for i in range(0, len(myset)):
-      if ((1 << i) & N) > 0:
-         left.append(myset[i])
-      else:
-         right.append(myset[i])
-   return (', '.join(left), ', '.join(right))
-
-for i in range(1, (1 << len(x)) - 2):
-   yield subset(x, i)
-$$ language plpythonu strict;
-CREATE FUNCTION
-select x[1] || ' => ' || x[2] from combination('a,b,c,d') x;
-   ?column?   
---------------
- a => b, c, d
- b => a, c, d
- a, b => c, d
- c => a, b, d
- a, c => b, d
- b, c => a, d
- a, b, c => d
- d => a, b, c
- a, d => b, c
- b, d => a, c
- a, b, d => c
- c, d => a, b
- a, c, d => b
-(13 rows)
-
-CREATE TABLE rules(rule text) distributed by (rule);
-CREATE TABLE
-insert into rules values('a,b,c');
-INSERT 0 1
-insert into rules values('d,e');
-INSERT 0 1
-insert into rules values('f,g,h,i,j');
-INSERT 0 1
-insert into rules values('k,l,m');
-INSERT 0 1
-SELECT rule, combination(rule) from rules order by 1,2;
-   rule    |    combination     
------------+--------------------
- a,b,c     | {a,"b, c"}
- a,b,c     | {"a, b",c}
- a,b,c     | {"a, c",b}
- a,b,c     | {b,"a, c"}
- a,b,c     | {c,"a, b"}
- d,e       | {d,e}
- f,g,h,i,j | {f,"g, h, i, j"}
- f,g,h,i,j | {"f, g","h, i, j"}
- f,g,h,i,j | {"f, g, h","i, j"}
- f,g,h,i,j | {"f, g, h, i",j}
- f,g,h,i,j | {"f, g, h, j",i}
- f,g,h,i,j | {"f, g, i","h, j"}
- f,g,h,i,j | {"f, g, i, j",h}
- f,g,h,i,j | {"f, g, j","h, i"}
- f,g,h,i,j | {"f, h","g, i, j"}
- f,g,h,i,j | {"f, h, i","g, j"}
- f,g,h,i,j | {"f, h, i, j",g}
- f,g,h,i,j | {"f, h, j","g, i"}
- f,g,h,i,j | {"f, i","g, h, j"}
- f,g,h,i,j | {"f, i, j","g, h"}
- f,g,h,i,j | {"f, j","g, h, i"}
- f,g,h,i,j | {g,"f, h, i, j"}
- f,g,h,i,j | {"g, h","f, i, j"}
- f,g,h,i,j | {"g, h, i","f, j"}
- f,g,h,i,j | {"g, h, j","f, i"}
- f,g,h,i,j | {"g, i","f, h, j"}
- f,g,h,i,j | {"g, i, j","f, h"}
- f,g,h,i,j | {"g, j","f, h, i"}
- f,g,h,i,j | {h,"f, g, i, j"}
- f,g,h,i,j | {"h, i","f, g, j"}
- f,g,h,i,j | {"h, i, j","f, g"}
- f,g,h,i,j | {"h, j","f, g, i"}
- f,g,h,i,j | {i,"f, g, h, j"}
- f,g,h,i,j | {"i, j","f, g, h"}
- f,g,h,i,j | {j,"f, g, h, i"}
- k,l,m     | {k,"l, m"}
- k,l,m     | {"k, l",m}
- k,l,m     | {"k, m",l}
- k,l,m     | {l,"k, m"}
- k,l,m     | {m,"k, l"}
-(40 rows)
-
-DROP TABLE IF EXISTS foo;
-psql:/tmp/TestUDF_TestUDFSetReturning.sql:69: NOTICE:  table "foo" does not exist, skipping
-DROP TABLE
-CREATE TABLE foo AS SELECT rule, combination(rule) from rules distributed by (rule);
-SELECT 40
--- UDT as argument/return type of set returning UDF
-CREATE TYPE r_type as (a int, b text);
-CREATE TYPE
-CREATE FUNCTION f1(x r_type) returns setof text as $$ SELECT $1.b from generate_series(1, $1.a) $$ language sql;
-CREATE FUNCTION
-CREATE FUNCTION f2(x int) returns setof r_type as $$ SELECT i, 'hello'::text from generate_series(1, $1) i $$ language sql;
-CREATE FUNCTION
-CREATE FUNCTION f3(x r_type) returns setof r_type as $$ SELECT $1 from generate_series(1, $1.a) $$ language sql;
-CREATE FUNCTION
-SELECT f1(row(2, 'hello'));
-  f1   
--------
- hello
- hello
-(2 rows)
-
-SELECT f2(2);
-    f2     
------------
- (1,hello)
- (2,hello)
-(2 rows)
-
-SELECT f3(row(2,'hello'));
-    f3     
------------
- (2,hello)
- (2,hello)
-(2 rows)
-
-SELECT * FROM f1(row(2,'hello'));
-  f1   
--------
- hello
- hello
-(2 rows)
-
-SELECT * FROM f2(2);
- a |   b   
----+-------
- 1 | hello
- 2 | hello
-(2 rows)
-
-SELECT * FROM f3(row(2,'hello'));
- a |   b   
----+-------
- 2 | hello
- 2 | hello
-(2 rows)
-
-CREATE TABLE t1 as SELECT i from generate_series(1,5) i distributed by (i);
-SELECT 5
-SELECT i, f1(row(i, 'hello')) from t1;
- i |  f1   
----+-------
- 1 | hello
- 3 | hello
- 3 | hello
- 3 | hello
- 5 | hello
- 5 | hello
- 5 | hello
- 5 | hello
- 5 | hello
- 2 | hello
- 2 | hello
- 4 | hello
- 4 | hello
- 4 | hello
- 4 | hello
-(15 rows)
-
-SELECT i, f2(i) from t1;
- i |    f2     
----+-----------
- 1 | (1,hello)
- 3 | (1,hello)
- 3 | (2,hello)
- 3 | (3,hello)
- 5 | (1,hello)
- 5 | (2,hello)
- 5 | (3,hello)
- 5 | (4,hello)
- 5 | (5,hello)
- 2 | (1,hello)
- 2 | (2,hello)
- 4 | (1,hello)
- 4 | (2,hello)
- 4 | (3,hello)
- 4 | (4,hello)
-(15 rows)
-
-SELECT i, f3(row(i,'hello')) from t1;
- i |    f3     
----+-----------
- 1 | (1,hello)
- 4 | (4,hello)
- 4 | (4,hello)
- 4 | (4,hello)
- 4 | (4,hello)
- 3 | (3,hello)
- 3 | (3,hello)
- 3 | (3,hello)
- 5 | (5,hello)
- 5 | (5,hello)
- 5 | (5,hello)
- 5 | (5,hello)
- 5 | (5,hello)
- 2 | (2,hello)
- 2 | (2,hello)
-(15 rows)
-
-CREATE TABLE o1 as SELECT f1(row(i, 'hello')) from t1;
-SELECT 15
-CREATE TABLE o2 as SELECT f2(i) from t1;
-SELECT 15
-CREATE TABLE o3 as SELECT f3(row(i,'hello')) from t1;
-SELECT 15

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/udf/sql/function_basics.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/udf/sql/function_basics.sql b/src/test/feature/udf/sql/function_basics.sql
deleted file mode 100755
index a050b22..0000000
--- a/src/test/feature/udf/sql/function_basics.sql
+++ /dev/null
@@ -1,439 +0,0 @@
--- 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/49fd529a/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
deleted file mode 100755
index d3d2abc..0000000
--- a/src/test/feature/udf/sql/function_extension.sql
+++ /dev/null
@@ -1,123 +0,0 @@
--- -----------------------------------------------------------------
--- 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/49fd529a/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
deleted file mode 100755
index 3beb31f..0000000
--- a/src/test/feature/udf/sql/function_set_returning.sql
+++ /dev/null
@@ -1,93 +0,0 @@
-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;