You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hawq.apache.org by hu...@apache.org on 2016/07/05 01:51:44 UTC
[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
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;
[2/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
Posted by hu...@apache.org.
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/lib/function.c
----------------------------------------------------------------------
diff --git a/src/test/feature/UDF/lib/function.c b/src/test/feature/UDF/lib/function.c
new file mode 100755
index 0000000..f346855
--- /dev/null
+++ b/src/test/feature/UDF/lib/function.c
@@ -0,0 +1,1245 @@
+#include "postgres.h"
+#include "funcapi.h"
+#include "tablefuncapi.h"
+
+#include <float.h>
+#include <math.h>
+
+#include "access/transam.h"
+#include "access/xact.h"
+#include "catalog/pg_type.h"
+#include "cdb/memquota.h"
+#include "commands/sequence.h"
+#include "commands/trigger.h"
+#include "executor/executor.h"
+#include "executor/spi.h"
+#include "parser/parse_expr.h"
+#include "libpq/auth.h"
+#include "libpq/hba.h"
+#include "utils/builtins.h"
+#include "utils/geo_decls.h"
+#include "utils/lsyscache.h"
+#include "utils/resscheduler.h"
+
+
+#define LDELIM '('
+#define RDELIM ')'
+#define NARGS 3
+#define TTDUMMY_INFINITY 999999
+
+
+extern Datum int44in(PG_FUNCTION_ARGS);
+extern Datum int44out(PG_FUNCTION_ARGS);
+extern Datum check_primary_key(PG_FUNCTION_ARGS);
+extern Datum check_foreign_key(PG_FUNCTION_ARGS);
+extern Datum autoinc(PG_FUNCTION_ARGS);
+extern Datum funny_dup17(PG_FUNCTION_ARGS);
+extern Datum ttdummy(PG_FUNCTION_ARGS);
+extern Datum set_ttdummy(PG_FUNCTION_ARGS);
+
+
+#ifdef PG_MODULE_MAGIC
+PG_MODULE_MAGIC;
+#endif
+
+
+
+/* widget_in and widget_out */
+typedef struct
+{
+ Point center;
+ double radius;
+} WIDGET;
+
+WIDGET *widget_in(char *str);
+char *widget_out(WIDGET * widget);
+
+WIDGET *widget_in(char *str)
+{
+ char *p, *coord[NARGS], buf2[1000];
+ int i;
+ WIDGET *result;
+
+ if (str == NULL)
+ {
+ return NULL;
+ }
+
+ for (i = 0, p = str; *p && i < NARGS && *p != RDELIM; p++)
+ {
+ if (*p == ',' || (*p == LDELIM && !i))
+ {
+ coord[i++] = p + 1;
+ }
+ }
+
+ if (i < NARGS - 1)
+ {
+ return NULL;
+ }
+
+ result = (WIDGET *) palloc(sizeof(WIDGET));
+ result->center.x = atof(coord[0]);
+ result->center.y = atof(coord[1]);
+ result->radius = atof(coord[2]);
+
+ snprintf(buf2, sizeof(buf2), "widget_in: read (%f, %f, %f)\n",
+ result->center.x, result->center.y, result->radius);
+
+ return result;
+}
+
+
+char *widget_out(WIDGET * widget)
+{
+ char *result;
+
+ if (widget == NULL)
+ {
+ return NULL;
+ }
+
+ result = (char *) palloc(60);
+
+ sprintf(result, "(%g,%g,%g)",
+ widget->center.x, widget->center.y, widget->radius);
+
+ return result;
+}
+
+
+
+/* int44in and int44out */
+/*
+ * Type int44 has no real-world use, but the function tests use it.
+ * It's a four-element vector of int4's.
+ */
+
+/*
+ * int44in: converts "num num ..." to internal form
+ * Note: Fills any missing positions with zeroes.
+ */
+PG_FUNCTION_INFO_V1(int44in);
+Datum int44in(PG_FUNCTION_ARGS)
+{
+ char *input_string = PG_GETARG_CSTRING(0);
+ int32 *result = (int32 *) palloc(4 * sizeof(int32));
+ int i;
+
+ i = sscanf(input_string,
+ "%d, %d, %d, %d",
+ &result[0],
+ &result[1],
+ &result[2],
+ &result[3]);
+
+ while (i < 4)
+ {
+ result[i++] = 0;
+ }
+
+ PG_RETURN_POINTER(result);
+}
+
+/*
+ * int44out: converts internal form to "num num ..."
+ */
+PG_FUNCTION_INFO_V1(int44out);
+Datum int44out(PG_FUNCTION_ARGS)
+{
+ int32 *an_array = (int32 *) PG_GETARG_POINTER(0);
+ /* Allow 14 digits sign */
+ char *result = (char *) palloc(16 * 4);
+ int i;
+ char *walk;
+
+ walk = result;
+ for (i = 0; i < 4; i++)
+ {
+ pg_ltoa(an_array[i], walk);
+ while (*++walk != '\0')
+ ;
+ *walk++ = ' ';
+ }
+ *--walk = '\0';
+ PG_RETURN_CSTRING(result);
+}
+
+
+
+/* check_primary_key, check_foreign_key and helper function find_plan */
+typedef struct
+{
+ char *ident;
+ int nplans;
+ void **splan;
+} EPlan;
+
+static EPlan *FPlans = NULL;
+static int nFPlans = 0;
+static EPlan *PPlans = NULL;
+static int nPPlans = 0;
+
+static EPlan *find_plan(char *ident, EPlan ** eplan, int *nplans);
+
+/*
+ * check_primary_key () -- check that key in tuple being inserted/updated
+ * references existing tuple in "primary" table.
+ * Though it's called without args You have to specify referenced
+ * table/keys while creating trigger: key field names in triggered table,
+ * referenced table name, referenced key field names:
+ * EXECUTE PROCEDURE
+ * check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2').
+ */
+PG_FUNCTION_INFO_V1(check_primary_key);
+Datum check_primary_key(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ Trigger *trigger; /* to get trigger name */
+ int nargs; /* # of args specified in CREATE TRIGGER */
+ char **args; /* arguments: column names and table name */
+ int nkeys; /* # of key columns (= nargs / 2) */
+ Datum *kvals; /* key values */
+ char *relname; /* referenced relation name */
+ Relation rel; /* triggered relation */
+ HeapTuple tuple = NULL; /* tuple to return */
+ TupleDesc tupdesc; /* tuple description */
+ EPlan *plan; /* prepared plan */
+ Oid *argtypes = NULL; /* key types to prepare execution plan */
+ bool isnull; /* to know is some column NULL or not */
+ char ident[2 * NAMEDATALEN]; /* to identify myself */
+ int ret;
+ int i;
+
+#ifdef DEBUG_QUERY
+ elog(DEBUG4, "check_primary_key: Enter Function");
+#endif
+
+ /*
+ * Some checks first...
+ */
+
+ /* Called by trigger manager ? */
+ if (!CALLED_AS_TRIGGER(fcinfo))
+ /* internal error */
+ elog(ERROR, "check_primary_key: not fired by trigger manager");
+
+ /* Should be called for ROW trigger */
+ if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
+ /* internal error */
+ elog(ERROR, "check_primary_key: can't process STATEMENT events");
+
+ /* If INSERTion then must check Tuple to being inserted */
+ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
+ tuple = trigdata->tg_trigtuple;
+
+ /* Not should be called for DELETE */
+ else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
+ /* internal error */
+ elog(ERROR, "check_primary_key: can't process DELETE events");
+
+ /* If UPDATion the must check new Tuple, not old one */
+ else
+ tuple = trigdata->tg_newtuple;
+
+ trigger = trigdata->tg_trigger;
+ nargs = trigger->tgnargs;
+ args = trigger->tgargs;
+
+ if (nargs % 2 != 1) /* odd number of arguments! */
+ /* internal error */
+ elog(ERROR, "check_primary_key: odd number of arguments should be specified");
+
+ nkeys = nargs / 2;
+ relname = args[nkeys];
+ rel = trigdata->tg_relation;
+ tupdesc = rel->rd_att;
+
+ /* Connect to SPI manager */
+ if ((ret = SPI_connect()) < 0)
+ /* internal error */
+ elog(ERROR, "check_primary_key: SPI_connect returned %d", ret);
+
+ /*
+ * We use SPI plan preparation feature, so allocate space to place key
+ * values.
+ */
+ kvals = (Datum *) palloc(nkeys * sizeof(Datum));
+
+ /*
+ * Construct ident string as TriggerName $ TriggeredRelationId and try to
+ * find prepared execution plan.
+ */
+ snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
+ plan = find_plan(ident, &PPlans, &nPPlans);
+
+ /* if there is no plan then allocate argtypes for preparation */
+ if (plan->nplans <= 0)
+ argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
+
+ /* For each column in key ... */
+ for (i = 0; i < nkeys; i++)
+ {
+ /* get index of column in tuple */
+ int fnumber = SPI_fnumber(tupdesc, args[i]);
+
+ /* Bad guys may give us un-existing column in CREATE TRIGGER */
+ if (fnumber < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("there is no attribute \"%s\" in relation \"%s\"",
+ args[i], SPI_getrelname(rel))));
+
+ /* Well, get binary (in internal format) value of column */
+ kvals[i] = SPI_getbinval(tuple, tupdesc, fnumber, &isnull);
+
+ /*
+ * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
+ * DON'T FORGET return tuple! Executor inserts tuple you're returning!
+ * If you return NULL then nothing will be inserted!
+ */
+ if (isnull)
+ {
+ SPI_finish();
+ return PointerGetDatum(tuple);
+ }
+
+ if (plan->nplans <= 0) /* Get typeId of column */
+ argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
+ }
+
+ /*
+ * If we have to prepare plan ...
+ */
+ if (plan->nplans <= 0)
+ {
+ void *pplan;
+ char sql[8192];
+
+ /*
+ * Construct query: SELECT 1 FROM _referenced_relation_ WHERE Pkey1 =
+ * $1 [AND Pkey2 = $2 [...]]
+ */
+ snprintf(sql, sizeof(sql), "select 1 from %s where ", relname);
+ for (i = 0; i < nkeys; i++)
+ {
+ snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s",
+ args[i + nkeys + 1], i + 1, (i < nkeys - 1) ? "and " : "");
+ }
+
+ /* Prepare plan for query */
+ pplan = SPI_prepare(sql, nkeys, argtypes);
+ if (pplan == NULL)
+ /* internal error */
+ elog(ERROR, "check_primary_key: SPI_prepare returned %d", SPI_result);
+
+ /*
+ * Remember that SPI_prepare places plan in current memory context -
+ * so, we have to save plan in Top memory context for latter use.
+ */
+ pplan = SPI_saveplan(pplan);
+ if (pplan == NULL)
+ /* internal error */
+ elog(ERROR, "check_primary_key: SPI_saveplan returned %d", SPI_result);
+ plan->splan = (void **) malloc(sizeof(void *));
+ *(plan->splan) = pplan;
+ plan->nplans = 1;
+ }
+
+ /*
+ * Ok, execute prepared plan.
+ */
+ ret = SPI_execp(*(plan->splan), kvals, NULL, 1);
+ /* we have no NULLs - so we pass ^^^^ here */
+
+ if (ret < 0)
+ /* internal error */
+ elog(ERROR, "check_primary_key: SPI_execp returned %d", ret);
+
+ /*
+ * If there are no tuples returned by SELECT then ...
+ */
+ if (SPI_processed == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
+ errmsg("tuple references non-existent key"),
+ errdetail("Trigger \"%s\" found tuple referencing non-existent key in \"%s\".", trigger->tgname, relname)));
+
+ SPI_finish();
+
+ return PointerGetDatum(tuple);
+}
+
+
+/*
+ * check_foreign_key () -- check that key in tuple being deleted/updated
+ * is not referenced by tuples in "foreign" table(s).
+ * Though it's called without args You have to specify (while creating trigger):
+ * number of references, action to do if key referenced
+ * ('restrict' | 'setnull' | 'cascade'), key field names in triggered
+ * ("primary") table and referencing table(s)/keys:
+ * EXECUTE PROCEDURE
+ * check_foreign_key (2, 'restrict', 'Pkey1', 'Pkey2',
+ * 'Ftable1', 'Fkey11', 'Fkey12', 'Ftable2', 'Fkey21', 'Fkey22').
+ */
+PG_FUNCTION_INFO_V1(check_foreign_key);
+Datum check_foreign_key(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ Trigger *trigger; /* to get trigger name */
+ int nargs; /* # of args specified in CREATE TRIGGER */
+ char **args; /* arguments: as described above */
+ char **args_temp;
+ int nrefs; /* number of references (== # of plans) */
+ char action; /* 'R'estrict | 'S'etnull | 'C'ascade */
+ int nkeys; /* # of key columns */
+ Datum *kvals; /* key values */
+ char *relname; /* referencing relation name */
+ Relation rel; /* triggered relation */
+ HeapTuple trigtuple = NULL; /* tuple to being changed */
+ HeapTuple newtuple = NULL; /* tuple to return */
+ TupleDesc tupdesc; /* tuple description */
+ EPlan *plan; /* prepared plan(s) */
+ Oid *argtypes = NULL; /* key types to prepare execution plan */
+ bool isnull; /* to know is some column NULL or not */
+ bool isequal = true; /* are keys in both tuples equal (in UPDATE) */
+ char ident[2 * NAMEDATALEN]; /* to identify myself */
+ int is_update = 0;
+ int ret;
+ int i,
+ r;
+
+#ifdef DEBUG_QUERY
+ elog(DEBUG4, "check_foreign_key: Enter Function");
+#endif
+
+ /*
+ * Some checks first...
+ */
+
+ /* Called by trigger manager ? */
+ if (!CALLED_AS_TRIGGER(fcinfo))
+ /* internal error */
+ elog(ERROR, "check_foreign_key: not fired by trigger manager");
+
+ /* Should be called for ROW trigger */
+ if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
+ /* internal error */
+ elog(ERROR, "check_foreign_key: can't process STATEMENT events");
+
+ /* Not should be called for INSERT */
+ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
+ /* internal error */
+ elog(ERROR, "check_foreign_key: can't process INSERT events");
+
+ /* Have to check tg_trigtuple - tuple being deleted */
+ trigtuple = trigdata->tg_trigtuple;
+
+ /*
+ * But if this is UPDATE then we have to return tg_newtuple. Also, if key
+ * in tg_newtuple is the same as in tg_trigtuple then nothing to do.
+ */
+ is_update = 0;
+ if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ {
+ newtuple = trigdata->tg_newtuple;
+ is_update = 1;
+ }
+ trigger = trigdata->tg_trigger;
+ nargs = trigger->tgnargs;
+ args = trigger->tgargs;
+
+ if (nargs < 5) /* nrefs, action, key, Relation, key - at
+ * least */
+ /* internal error */
+ elog(ERROR, "check_foreign_key: too short %d (< 5) list of arguments", nargs);
+
+ nrefs = pg_atoi(args[0], sizeof(int), 0);
+ if (nrefs < 1)
+ /* internal error */
+ elog(ERROR, "check_foreign_key: %d (< 1) number of references specified", nrefs);
+ action = tolower((unsigned char) *(args[1]));
+ if (action != 'r' && action != 'c' && action != 's')
+ /* internal error */
+ elog(ERROR, "check_foreign_key: invalid action %s", args[1]);
+ nargs -= 2;
+ args += 2;
+ nkeys = (nargs - nrefs) / (nrefs + 1);
+ if (nkeys <= 0 || nargs != (nrefs + nkeys * (nrefs + 1)))
+ /* internal error */
+ elog(ERROR, "check_foreign_key: invalid number of arguments %d for %d references",
+ nargs + 2, nrefs);
+
+ rel = trigdata->tg_relation;
+ tupdesc = rel->rd_att;
+
+ /* Connect to SPI manager */
+ if ((ret = SPI_connect()) < 0)
+ /* internal error */
+ elog(ERROR, "check_foreign_key: SPI_connect returned %d", ret);
+
+ /*
+ * We use SPI plan preparation feature, so allocate space to place key
+ * values.
+ */
+ kvals = (Datum *) palloc(nkeys * sizeof(Datum));
+
+ /*
+ * Construct ident string as TriggerName $ TriggeredRelationId and try to
+ * find prepared execution plan(s).
+ */
+ snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
+ plan = find_plan(ident, &FPlans, &nFPlans);
+
+ /* if there is no plan(s) then allocate argtypes for preparation */
+ if (plan->nplans <= 0)
+ argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
+
+ /*
+ * else - check that we have exactly nrefs plan(s) ready
+ */
+ else if (plan->nplans != nrefs)
+ /* internal error */
+ elog(ERROR, "%s: check_foreign_key: # of plans changed in meantime",
+ trigger->tgname);
+
+ /* For each column in key ... */
+ for (i = 0; i < nkeys; i++)
+ {
+ /* get index of column in tuple */
+ int fnumber = SPI_fnumber(tupdesc, args[i]);
+
+ /* Bad guys may give us un-existing column in CREATE TRIGGER */
+ if (fnumber < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("there is no attribute \"%s\" in relation \"%s\"",
+ args[i], SPI_getrelname(rel))));
+
+ /* Well, get binary (in internal format) value of column */
+ kvals[i] = SPI_getbinval(trigtuple, tupdesc, fnumber, &isnull);
+
+ /*
+ * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
+ * DON'T FORGET return tuple! Executor inserts tuple you're returning!
+ * If you return NULL then nothing will be inserted!
+ */
+ if (isnull)
+ {
+ SPI_finish();
+ return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
+ }
+
+ /*
+ * If UPDATE then get column value from new tuple being inserted and
+ * compare is this the same as old one. For the moment we use string
+ * presentation of values...
+ */
+ if (newtuple != NULL)
+ {
+ char *oldval = SPI_getvalue(trigtuple, tupdesc, fnumber);
+ char *newval;
+
+ /* this shouldn't happen! SPI_ERROR_NOOUTFUNC ? */
+ if (oldval == NULL)
+ /* internal error */
+ elog(ERROR, "check_foreign_key: SPI_getvalue returned %d", SPI_result);
+ newval = SPI_getvalue(newtuple, tupdesc, fnumber);
+ if (newval == NULL || strcmp(oldval, newval) != 0)
+ isequal = false;
+ }
+
+ if (plan->nplans <= 0) /* Get typeId of column */
+ argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
+ }
+ args_temp = args;
+ nargs -= nkeys;
+ args += nkeys;
+
+ /*
+ * If we have to prepare plans ...
+ */
+ if (plan->nplans <= 0)
+ {
+ void *pplan;
+ char sql[8192];
+ char **args2 = args;
+
+ plan->splan = (void **) malloc(nrefs * sizeof(void *));
+
+ for (r = 0; r < nrefs; r++)
+ {
+ relname = args2[0];
+
+ /*---------
+ * For 'R'estrict action we construct SELECT query:
+ *
+ * SELECT 1
+ * FROM _referencing_relation_
+ * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
+ *
+ * to check is tuple referenced or not.
+ *---------
+ */
+ if (action == 'r')
+
+ snprintf(sql, sizeof(sql), "select 1 from %s where ", relname);
+
+ /*---------
+ * For 'C'ascade action we construct DELETE query
+ *
+ * DELETE
+ * FROM _referencing_relation_
+ * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
+ *
+ * to delete all referencing tuples.
+ *---------
+ */
+
+ /*
+ * Max : Cascade with UPDATE query i create update query that
+ * updates new key values in referenced tables
+ */
+
+
+ else if (action == 'c')
+ {
+ if (is_update == 1)
+ {
+ int fn;
+ char *nv;
+ int k;
+
+ snprintf(sql, sizeof(sql), "update %s set ", relname);
+ for (k = 1; k <= nkeys; k++)
+ {
+ int is_char_type = 0;
+ char *type;
+
+ fn = SPI_fnumber(tupdesc, args_temp[k - 1]);
+ nv = SPI_getvalue(newtuple, tupdesc, fn);
+ type = SPI_gettype(tupdesc, fn);
+
+ if ((strcmp(type, "text") && strcmp(type, "varchar") &&
+ strcmp(type, "char") && strcmp(type, "bpchar") &&
+ strcmp(type, "date") && strcmp(type, "timestamp")) == 0)
+ is_char_type = 1;
+#ifdef DEBUG_QUERY
+ elog(DEBUG4, "check_foreign_key Debug value %s type %s %d",
+ nv, type, is_char_type);
+#endif
+
+ /*
+ * is_char_type =1 i set ' ' for define a new value
+ */
+ snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql),
+ " %s = %s%s%s %s ",
+ args2[k], (is_char_type > 0) ? "'" : "",
+ nv, (is_char_type > 0) ? "'" : "", (k < nkeys) ? ", " : "");
+ is_char_type = 0;
+ }
+ strcat(sql, " where ");
+
+ }
+ else
+ /* DELETE */
+ snprintf(sql, sizeof(sql), "delete from %s where ", relname);
+
+ }
+
+ /*
+ * For 'S'etnull action we construct UPDATE query - UPDATE
+ * _referencing_relation_ SET Fkey1 null [, Fkey2 null [...]]
+ * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]] - to set key columns in
+ * all referencing tuples to NULL.
+ */
+ else if (action == 's')
+ {
+ snprintf(sql, sizeof(sql), "update %s set ", relname);
+ for (i = 1; i <= nkeys; i++)
+ {
+ snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql),
+ "%s = null%s",
+ args2[i], (i < nkeys) ? ", " : "");
+ }
+ strcat(sql, " where ");
+ }
+
+ /* Construct WHERE qual */
+ for (i = 1; i <= nkeys; i++)
+ {
+ snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s",
+ args2[i], i, (i < nkeys) ? "and " : "");
+ }
+
+ /* Prepare plan for query */
+ pplan = SPI_prepare(sql, nkeys, argtypes);
+ if (pplan == NULL)
+ /* internal error */
+ elog(ERROR, "check_foreign_key: SPI_prepare returned %d", SPI_result);
+
+ /*
+ * Remember that SPI_prepare places plan in current memory context
+ * - so, we have to save plan in Top memory context for latter
+ * use.
+ */
+ pplan = SPI_saveplan(pplan);
+ if (pplan == NULL)
+ /* internal error */
+ elog(ERROR, "check_foreign_key: SPI_saveplan returned %d", SPI_result);
+
+ plan->splan[r] = pplan;
+
+ args2 += nkeys + 1; /* to the next relation */
+ }
+ plan->nplans = nrefs;
+#ifdef DEBUG_QUERY
+ elog(DEBUG4, "check_foreign_key Debug Query is : %s ", sql);
+#endif
+ }
+
+ /*
+ * If UPDATE and key is not changed ...
+ */
+ if (newtuple != NULL && isequal)
+ {
+ SPI_finish();
+ return PointerGetDatum(newtuple);
+ }
+
+ /*
+ * Ok, execute prepared plan(s).
+ */
+ for (r = 0; r < nrefs; r++)
+ {
+ /*
+ * For 'R'estrict we may to execute plan for one tuple only, for other
+ * actions - for all tuples.
+ */
+ int tcount = (action == 'r') ? 1 : 0;
+
+ relname = args[0];
+
+ snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
+ plan = find_plan(ident, &FPlans, &nFPlans);
+ ret = SPI_execp(plan->splan[r], kvals, NULL, tcount);
+ /* we have no NULLs - so we pass ^^^^ here */
+
+ if (ret < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
+ errmsg("SPI_execp returned %d", ret)));
+
+ /* If action is 'R'estrict ... */
+ if (action == 'r')
+ {
+ /* If there is tuple returned by SELECT then ... */
+ if (SPI_processed > 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
+ errmsg("\"%s\": tuple is referenced in \"%s\"",
+ trigger->tgname, relname)));
+ }
+ else
+ {
+#ifdef REFINT_VERBOSE
+ elog(NOTICE, "%s: %d tuple(s) of %s are %s",
+ trigger->tgname, SPI_processed, relname,
+ (action == 'c') ? "deleted" : "set to null");
+#endif
+ }
+ args += nkeys + 1; /* to the next relation */
+ }
+
+ SPI_finish();
+
+ return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
+}
+
+
+static EPlan *find_plan(char *ident, EPlan ** eplan, int *nplans)
+{
+ EPlan *newp;
+ int i;
+
+ if (*nplans > 0)
+ {
+ for (i = 0; i < *nplans; i++)
+ {
+ if (strcmp((*eplan)[i].ident, ident) == 0)
+ break;
+ }
+ if (i != *nplans)
+ return (*eplan + i);
+ *eplan = (EPlan *) realloc(*eplan, (i + 1) * sizeof(EPlan));
+ newp = *eplan + i;
+ }
+ else
+ {
+ newp = *eplan = (EPlan *) malloc(sizeof(EPlan));
+ (*nplans) = i = 0;
+ }
+
+ newp->ident = (char *) malloc(strlen(ident) + 1);
+ strcpy(newp->ident, ident);
+ newp->nplans = 0;
+ newp->splan = NULL;
+ (*nplans)++;
+
+ return (newp);
+}
+
+
+
+/* autoinc */
+PG_FUNCTION_INFO_V1(autoinc);
+Datum autoinc(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ Trigger *trigger; /* to get trigger name */
+ int nargs; /* # of arguments */
+ int *chattrs; /* attnums of attributes to change */
+ int chnattrs = 0; /* # of above */
+ Datum *newvals; /* vals of above */
+ char **args; /* arguments */
+ char *relname; /* triggered relation name */
+ Relation rel; /* triggered relation */
+ HeapTuple rettuple = NULL;
+ TupleDesc tupdesc; /* tuple description */
+ bool isnull;
+ int i;
+
+ if (!CALLED_AS_TRIGGER(fcinfo))
+ /* internal error */
+ elog(ERROR, "not fired by trigger manager");
+ if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
+ /* internal error */
+ elog(ERROR, "can't process STATEMENT events");
+ if (TRIGGER_FIRED_AFTER(trigdata->tg_event))
+ /* internal error */
+ elog(ERROR, "must be fired before event");
+
+ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
+ rettuple = trigdata->tg_trigtuple;
+ else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ rettuple = trigdata->tg_newtuple;
+ else
+ /* internal error */
+ elog(ERROR, "can't process DELETE events");
+
+ rel = trigdata->tg_relation;
+ relname = SPI_getrelname(rel);
+
+ trigger = trigdata->tg_trigger;
+
+ nargs = trigger->tgnargs;
+ if (nargs <= 0 || nargs % 2 != 0)
+ /* internal error */
+ elog(ERROR, "autoinc (%s): even number gt 0 of arguments was expected", relname);
+
+ args = trigger->tgargs;
+ tupdesc = rel->rd_att;
+
+ chattrs = (int *) palloc(nargs / 2 * sizeof(int));
+ newvals = (Datum *) palloc(nargs / 2 * sizeof(Datum));
+
+ for (i = 0; i < nargs;)
+ {
+ int attnum = SPI_fnumber(tupdesc, args[i]);
+ int32 val;
+ Datum seqname;
+
+ if (attnum < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
+ errmsg("\"%s\" has no attribute \"%s\"",
+ relname, args[i])));
+
+ if (SPI_gettypeid(tupdesc, attnum) != INT4OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
+ errmsg("attribute \"%s\" of \"%s\" must be type INT4",
+ args[i], relname)));
+
+ val = DatumGetInt32(SPI_getbinval(rettuple, tupdesc, attnum, &isnull));
+
+ if (!isnull && val != 0)
+ {
+ i += 2;
+ continue;
+ }
+
+ i++;
+ chattrs[chnattrs] = attnum;
+ seqname = DirectFunctionCall1(textin,
+ CStringGetDatum(args[i]));
+ newvals[chnattrs] = DirectFunctionCall1(nextval, seqname);
+ /* nextval now returns int64; coerce down to int32 */
+ newvals[chnattrs] = Int32GetDatum((int32) DatumGetInt64(newvals[chnattrs]));
+ if (DatumGetInt32(newvals[chnattrs]) == 0)
+ {
+ newvals[chnattrs] = DirectFunctionCall1(nextval, seqname);
+ newvals[chnattrs] = Int32GetDatum((int32) DatumGetInt64(newvals[chnattrs]));
+ }
+ pfree(DatumGetTextP(seqname));
+ chnattrs++;
+ i++;
+ }
+
+ if (chnattrs > 0)
+ {
+ rettuple = SPI_modifytuple(rel, rettuple, chnattrs, chattrs, newvals, NULL);
+ if (rettuple == NULL)
+ /* internal error */
+ elog(ERROR, "autoinc (%s): %d returned by SPI_modifytuple",
+ relname, SPI_result);
+ }
+
+ pfree(relname);
+ pfree(chattrs);
+ pfree(newvals);
+
+ return PointerGetDatum(rettuple);
+}
+
+
+
+/* funny_dup17 */
+static TransactionId fd17b_xid = InvalidTransactionId;
+static TransactionId fd17a_xid = InvalidTransactionId;
+static int fd17b_level = 0;
+static int fd17a_level = 0;
+static bool fd17b_recursion = true;
+static bool fd17a_recursion = true;
+
+PG_FUNCTION_INFO_V1(funny_dup17);
+Datum funny_dup17(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ TransactionId *xid;
+ int *level;
+ bool *recursion;
+ Relation rel;
+ TupleDesc tupdesc;
+ HeapTuple tuple;
+ char *query,
+ *fieldval,
+ *fieldtype;
+ char *when;
+ int inserted;
+ int selected = 0;
+ int ret;
+
+ if (!CALLED_AS_TRIGGER(fcinfo))
+ elog(ERROR, "funny_dup17: not fired by trigger manager");
+
+ tuple = trigdata->tg_trigtuple;
+ rel = trigdata->tg_relation;
+ tupdesc = rel->rd_att;
+ if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
+ {
+ xid = &fd17b_xid;
+ level = &fd17b_level;
+ recursion = &fd17b_recursion;
+ when = "BEFORE";
+ }
+ else
+ {
+ xid = &fd17a_xid;
+ level = &fd17a_level;
+ recursion = &fd17a_recursion;
+ when = "AFTER ";
+ }
+
+ if (!TransactionIdIsCurrentTransactionId(*xid))
+ {
+ *xid = GetCurrentTransactionId();
+ *level = 0;
+ *recursion = true;
+ }
+
+ if (*level == 17)
+ {
+ *recursion = false;
+ return PointerGetDatum(tuple);
+ }
+
+ if (!(*recursion))
+ return PointerGetDatum(tuple);
+
+ (*level)++;
+
+ SPI_connect();
+
+ fieldval = SPI_getvalue(tuple, tupdesc, 1);
+ fieldtype = SPI_gettype(tupdesc, 1);
+
+ query = (char *) palloc(100 + NAMEDATALEN * 3 +
+ strlen(fieldval) + strlen(fieldtype));
+
+ sprintf(query, "insert into %s select * from %s where %s = '%s'::%s",
+ SPI_getrelname(rel), SPI_getrelname(rel),
+ SPI_fname(tupdesc, 1),
+ fieldval, fieldtype);
+
+ if ((ret = SPI_exec(query, 0)) < 0)
+ elog(ERROR, "funny_dup17 (fired %s) on level %3d: SPI_exec (insert ...) returned %d",
+ when, *level, ret);
+
+ inserted = SPI_processed;
+
+ sprintf(query, "select count (*) from %s where %s = '%s'::%s",
+ SPI_getrelname(rel),
+ SPI_fname(tupdesc, 1),
+ fieldval, fieldtype);
+
+ if ((ret = SPI_exec(query, 0)) < 0)
+ elog(ERROR, "funny_dup17 (fired %s) on level %3d: SPI_exec (select ...) returned %d",
+ when, *level, ret);
+
+ if (SPI_processed > 0)
+ {
+ selected = DatumGetInt32(DirectFunctionCall1(int4in,
+ CStringGetDatum(SPI_getvalue(
+ SPI_tuptable->vals[0],
+ SPI_tuptable->tupdesc,
+ 1
+ ))));
+ }
+
+ elog(DEBUG4, "funny_dup17 (fired %s) on level %3d: %d/%d tuples inserted/selected",
+ when, *level, inserted, selected);
+
+ SPI_finish();
+
+ (*level)--;
+
+ if (*level == 0)
+ *xid = InvalidTransactionId;
+
+ return PointerGetDatum(tuple);
+}
+
+
+
+/* ttdummy and set_ttdummy */
+static SPIPlanPtr splan = NULL;
+static bool ttoff = false;
+
+PG_FUNCTION_INFO_V1(ttdummy);
+Datum ttdummy(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ Trigger *trigger; /* to get trigger name */
+ char **args; /* arguments */
+ int attnum[2]; /* fnumbers of start/stop columns */
+ Datum oldon,
+ oldoff;
+ Datum newon,
+ newoff;
+ Datum *cvals; /* column values */
+ char *cnulls; /* column nulls */
+ char *relname; /* triggered relation name */
+ Relation rel; /* triggered relation */
+ HeapTuple trigtuple;
+ HeapTuple newtuple = NULL;
+ HeapTuple rettuple;
+ TupleDesc tupdesc; /* tuple description */
+ int natts; /* # of attributes */
+ bool isnull; /* to know is some column NULL or not */
+ int ret;
+ int i;
+
+ if (!CALLED_AS_TRIGGER(fcinfo))
+ elog(ERROR, "ttdummy: not fired by trigger manager");
+ if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
+ elog(ERROR, "ttdummy: cannot process STATEMENT events");
+ if (TRIGGER_FIRED_AFTER(trigdata->tg_event))
+ elog(ERROR, "ttdummy: must be fired before event");
+ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
+ elog(ERROR, "ttdummy: cannot process INSERT event");
+ if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ newtuple = trigdata->tg_newtuple;
+
+ trigtuple = trigdata->tg_trigtuple;
+
+ rel = trigdata->tg_relation;
+ relname = SPI_getrelname(rel);
+
+ /* check if TT is OFF for this relation */
+ if (ttoff) /* OFF - nothing to do */
+ {
+ pfree(relname);
+ return PointerGetDatum((newtuple != NULL) ? newtuple : trigtuple);
+ }
+
+ trigger = trigdata->tg_trigger;
+
+ if (trigger->tgnargs != 2)
+ elog(ERROR, "ttdummy (%s): invalid (!= 2) number of arguments %d",
+ relname, trigger->tgnargs);
+
+ args = trigger->tgargs;
+ tupdesc = rel->rd_att;
+ natts = tupdesc->natts;
+
+ for (i = 0; i < 2; i++)
+ {
+ attnum[i] = SPI_fnumber(tupdesc, args[i]);
+ if (attnum[i] < 0)
+ elog(ERROR, "ttdummy (%s): there is no attribute %s", relname, args[i]);
+ if (SPI_gettypeid(tupdesc, attnum[i]) != INT4OID)
+ elog(ERROR, "ttdummy (%s): attributes %s and %s must be of abstime type",
+ relname, args[0], args[1]);
+ }
+
+ oldon = SPI_getbinval(trigtuple, tupdesc, attnum[0], &isnull);
+ if (isnull)
+ elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[0]);
+
+ oldoff = SPI_getbinval(trigtuple, tupdesc, attnum[1], &isnull);
+ if (isnull)
+ elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[1]);
+
+ if (newtuple != NULL) /* UPDATE */
+ {
+ newon = SPI_getbinval(newtuple, tupdesc, attnum[0], &isnull);
+ if (isnull)
+ elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[0]);
+ newoff = SPI_getbinval(newtuple, tupdesc, attnum[1], &isnull);
+ if (isnull)
+ elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[1]);
+
+ if (oldon != newon || oldoff != newoff)
+ elog(ERROR, "ttdummy (%s): you cannot change %s and/or %s columns (use set_ttdummy)",
+ relname, args[0], args[1]);
+
+ if (newoff != TTDUMMY_INFINITY)
+ {
+ pfree(relname); /* allocated in upper executor context */
+ return PointerGetDatum(NULL);
+ }
+ }
+ else if (oldoff != TTDUMMY_INFINITY) /* DELETE */
+ {
+ pfree(relname);
+ return PointerGetDatum(NULL);
+ }
+
+ newoff = DirectFunctionCall1(nextval, CStringGetTextDatum("ttdummy_seq"));
+ /* nextval now returns int64; coerce down to int32 */
+ newoff = Int32GetDatum((int32) DatumGetInt64(newoff));
+
+ /* Connect to SPI manager */
+ if ((ret = SPI_connect()) < 0)
+ elog(ERROR, "ttdummy (%s): SPI_connect returned %d", relname, ret);
+
+ /* Fetch tuple values and nulls */
+ cvals = (Datum *) palloc(natts * sizeof(Datum));
+ cnulls = (char *) palloc(natts * sizeof(char));
+ for (i = 0; i < natts; i++)
+ {
+ cvals[i] = SPI_getbinval((newtuple != NULL) ? newtuple : trigtuple,
+ tupdesc, i + 1, &isnull);
+ cnulls[i] = (isnull) ? 'n' : ' ';
+ }
+
+ /* change date column(s) */
+ if (newtuple) /* UPDATE */
+ {
+ cvals[attnum[0] - 1] = newoff; /* start_date eq current date */
+ cnulls[attnum[0] - 1] = ' ';
+ cvals[attnum[1] - 1] = TTDUMMY_INFINITY; /* stop_date eq INFINITY */
+ cnulls[attnum[1] - 1] = ' ';
+ }
+ else
+ /* DELETE */
+ {
+ cvals[attnum[1] - 1] = newoff; /* stop_date eq current date */
+ cnulls[attnum[1] - 1] = ' ';
+ }
+
+ /* if there is no plan ... */
+ if (splan == NULL)
+ {
+ SPIPlanPtr pplan;
+ Oid *ctypes;
+ char *query;
+
+ /* allocate space in preparation */
+ ctypes = (Oid *) palloc(natts * sizeof(Oid));
+ query = (char *) palloc(100 + 16 * natts);
+
+ /*
+ * Construct query: INSERT INTO _relation_ VALUES ($1, ...)
+ */
+ sprintf(query, "INSERT INTO %s VALUES (", relname);
+ for (i = 1; i <= natts; i++)
+ {
+ sprintf(query + strlen(query), "$%d%s",
+ i, (i < natts) ? ", " : ")");
+ ctypes[i - 1] = SPI_gettypeid(tupdesc, i);
+ }
+
+ /* Prepare plan for query */
+ pplan = SPI_prepare(query, natts, ctypes);
+ if (pplan == NULL)
+ elog(ERROR, "ttdummy (%s): SPI_prepare returned %d", relname, SPI_result);
+
+ pplan = SPI_saveplan(pplan);
+ if (pplan == NULL)
+ elog(ERROR, "ttdummy (%s): SPI_saveplan returned %d", relname, SPI_result);
+
+ splan = pplan;
+ }
+
+ ret = SPI_execp(splan, cvals, cnulls, 0);
+
+ if (ret < 0)
+ elog(ERROR, "ttdummy (%s): SPI_execp returned %d", relname, ret);
+
+ /* Tuple to return to upper Executor ... */
+ if (newtuple) /* UPDATE */
+ {
+ HeapTuple tmptuple;
+
+ tmptuple = SPI_copytuple(trigtuple);
+ rettuple = SPI_modifytuple(rel, tmptuple, 1, &(attnum[1]), &newoff, NULL);
+ SPI_freetuple(tmptuple);
+ }
+ else
+ /* DELETE */
+ rettuple = trigtuple;
+
+ SPI_finish(); /* don't forget say Bye to SPI mgr */
+
+ pfree(relname);
+
+ return PointerGetDatum(rettuple);
+}
+
+
+PG_FUNCTION_INFO_V1(set_ttdummy);
+Datum set_ttdummy(PG_FUNCTION_ARGS)
+{
+ int32 on = PG_GETARG_INT32(0);
+
+ if (ttoff) /* OFF currently */
+ {
+ if (on == 0)
+ PG_RETURN_INT32(0);
+
+ /* turn ON */
+ ttoff = false;
+ PG_RETURN_INT32(0);
+ }
+
+ /* ON currently */
+ if (on != 0)
+ PG_RETURN_INT32(1);
+
+ /* turn OFF */
+ ttoff = true;
+
+ PG_RETURN_INT32(1);
+}
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
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/49fd529a/src/test/feature/UDF/sql/function_creation.sql.source
----------------------------------------------------------------------
diff --git a/src/test/feature/UDF/sql/function_creation.sql.source b/src/test/feature/UDF/sql/function_creation.sql.source
new file mode 100755
index 0000000..226044e
--- /dev/null
+++ b/src/test/feature/UDF/sql/function_creation.sql.source
@@ -0,0 +1,79 @@
+--
+-- CREATE_FUNCTION
+--
+
+CREATE FUNCTION widget_in(cstring)
+ RETURNS widget
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C IMMUTABLE STRICT;
+
+CREATE FUNCTION widget_out(widget)
+ RETURNS cstring
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C IMMUTABLE STRICT;
+
+CREATE FUNCTION int44in(cstring)
+ RETURNS city_budget
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C IMMUTABLE STRICT;
+
+CREATE FUNCTION int44out(city_budget)
+ RETURNS cstring
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C IMMUTABLE STRICT;
+
+CREATE FUNCTION check_primary_key ()
+ RETURNS trigger
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C;
+
+CREATE FUNCTION check_foreign_key ()
+ RETURNS trigger
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C;
+
+CREATE FUNCTION autoinc ()
+ RETURNS trigger
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C;
+
+CREATE FUNCTION funny_dup17 ()
+ RETURNS trigger
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C;
+
+CREATE FUNCTION ttdummy ()
+ RETURNS trigger
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C;
+
+CREATE FUNCTION set_ttdummy (int4)
+ RETURNS int4
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C STRICT;
+
+-- Things that shouldn't work:
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'SELECT ''not an integer'';';
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'not even SQL';
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'SELECT 1, 2, 3;';
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'SELECT $2;';
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'a', 'b';
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C
+ AS 'nosuchfile';
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C
+ AS '@SHARE_LIBRARY_PATH@', 'nosuchsymbol';
+
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE internal
+ AS 'nosuch';
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
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/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
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;
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/udf/TestUDF.cpp
----------------------------------------------------------------------
diff --git a/src/test/feature/udf/TestUDF.cpp b/src/test/feature/udf/TestUDF.cpp
deleted file mode 100755
index fcd33ea..0000000
--- a/src/test/feature/udf/TestUDF.cpp
+++ /dev/null
@@ -1,32 +0,0 @@
-#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/49fd529a/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
deleted file mode 100755
index ff45af2..0000000
--- a/src/test/feature/udf/ans/function_basics.ans
+++ /dev/null
@@ -1,1088 +0,0 @@
--- start_ignore
-SET SEARCH_PATH=TestUDF_TestUDFBasics;
-SET
--- end_ignore
--- SETUP
-DROP TABLE IF EXISTS foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:5: NOTICE: table "foo" does not exist, skipping
-DROP TABLE
-CREATE TABLE foo AS SELECT * FROM generate_series(1, 10) x;
-SELECT 10
-CREATE FUNCTION f(x INT) RETURNS INT AS $$
-BEGIN
-RETURN x;
-END
-$$ LANGUAGE PLPGSQL;
-CREATE FUNCTION
--- DDL, CREATE FUNCTION
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-CREATE FUNCTION
-SELECT proname FROM pg_proc WHERE proname = 'g';
- proname
----------
- g
-(1 row)
-
-SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g';
- proname
----------
-(0 rows)
-
-DROP FUNCTION g(int);
-DROP FUNCTION
--- DDL, CREATE OR REPLACE FUNCTION
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-CREATE FUNCTION
-SELECT proname FROM pg_proc WHERE proname = 'g';
- proname
----------
- g
-(1 row)
-
-SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g';
- proname
----------
-(0 rows)
-
-CREATE OR REPLACE FUNCTION g(x INT) RETURNS INT AS $$
-BEGIN
-RETURN (-1) * x;
-END
-$$ LANGUAGE PLPGSQL;
-CREATE FUNCTION
-SELECT proname, prosrc FROM pg_proc WHERE proname = 'g';
- proname | prosrc
----------+------------------
- g |
- : BEGIN
- : RETURN (-1) * x;
- : END
- :
-(1 row)
-
-SELECT proname, prosrc FROM gp_dist_random('pg_proc') WHERE proname = 'g';
- proname | prosrc
----------+--------
-(0 rows)
-
-DROP FUNCTION g(int);
-DROP FUNCTION
--- DDL, DROP FUNCTION
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-CREATE FUNCTION
-DROP FUNCTION g(int);
-DROP FUNCTION
-SELECT oid, proname FROM pg_proc WHERE proname = 'g';
- oid | proname
------+---------
-(0 rows)
-
-SELECT oid, proname FROM gp_dist_random('pg_proc') WHERE proname = 'g';
- oid | proname
------+---------
-(0 rows)
-
--- DDL, DROP FUNCTION, NEGATIVE
-DROP FUNCTION g(int);
-psql:/tmp/TestUDF_TestUDFBasics.sql:47: ERROR: function g(integer) does not exist
--- DDL, CREATE FUNCTION, RECORD
-CREATE FUNCTION foo(int) RETURNS record AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL;
-CREATE FUNCTION
-SELECT foo(5);
- foo
------
- (5)
-(1 row)
-
-DROP FUNCTION foo(int);
-DROP FUNCTION
-CREATE FUNCTION foo(int) RETURNS foo AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL;
-CREATE FUNCTION
-SELECT foo(5);
- foo
------
- (5)
-(1 row)
-
-DROP FUNCTION foo(int);
-DROP FUNCTION
--- DDL, CREATE FUNCTION, SRF
-CREATE FUNCTION g(x setof int) RETURNS INT
- AS $$ SELECT 1 $$ LANGUAGE SQL;
-CREATE FUNCTION
-DROP FUNCTION g(setof int);
-DROP FUNCTION
-CREATE FUNCTION g() RETURNS setof INT
- AS $$ SELECT 1 $$ LANGUAGE SQL;
-CREATE FUNCTION
-DROP FUNCTION g();
-DROP FUNCTION
--- DDL, CREATE FUNCTION, TABLE, NEGATIVE
-CREATE FUNCTION g() RETURNS TABLE(x int)
- AS $$ SELECT * FROM foo $$ LANGUAGE SQL;
-CREATE FUNCTION
-DROP FUNCTION g();
-DROP FUNCTION
-CREATE FUNCTION g(anytable) RETURNS int
- AS 'does_not_exist', 'does_not_exist' LANGUAGE C;
-psql:/tmp/TestUDF_TestUDFBasics.sql:76: ERROR: TABLE functions not supported
--- DDL, CREATE FUNCTION, SECURITY DEFINER
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE SECURITY DEFINER;
-CREATE FUNCTION
-DROP FUNCTION g(int);
-DROP FUNCTION
--- DDL, ALTER FUNCTION
--- DDL, STRICT
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
-CREATE FUNCTION
-SELECT g(NULL);
- g
----
- 1
-(1 row)
-
-ALTER FUNCTION g(int) STRICT;
-ALTER FUNCTION
-SELECT g(NULL);
- g
----
-
-(1 row)
-
-DROP FUNCTION g(int);
-DROP FUNCTION
--- DDL, ALTER FUNCTION, OWNER
-CREATE ROLE superuser SUPERUSER;
-CREATE ROLE
-CREATE ROLE u1;
-psql:/tmp/TestUDF_TestUDFBasics.sql:97: NOTICE: resource queue required -- using default resource queue "pg_default"
-CREATE ROLE
-SET ROLE superuser;
-SET
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
-CREATE FUNCTION
-SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g';
- rolname
------------
- superuser
-(1 row)
-
-ALTER FUNCTION g(int) OWNER TO u1;
-ALTER FUNCTION
-SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g';
- rolname
----------
- u1
-(1 row)
-
-DROP FUNCTION g(int);
-DROP FUNCTION
-RESET ROLE;
-RESET
-DROP ROLE u1;
-DROP ROLE
-DROP ROLE superuser;
-DROP ROLE
--- DDL, ALTER FUNCTION, RENAME
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
-CREATE FUNCTION
-SELECT g(0);
- g
----
- 1
-(1 row)
-
-ALTER FUNCTION g(int) RENAME TO h;
-ALTER FUNCTION
-SELECT h(0);
- h
----
- 1
-(1 row)
-
-DROP FUNCTION h(int);
-DROP FUNCTION
--- DDL, ALTER FUNCTION, SET SCHEMA
-CREATE SCHEMA bar;
-CREATE SCHEMA
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
-CREATE FUNCTION
-SELECT g(0);
- g
----
- 1
-(1 row)
-
-ALTER FUNCTION g(int) SET SCHEMA bar;
-ALTER FUNCTION
-SELECT bar.g(0);
- g
----
- 1
-(1 row)
-
-DROP SCHEMA bar CASCADE;
-psql:/tmp/TestUDF_TestUDFBasics.sql:125: NOTICE: drop cascades to function bar.g(integer)
-DROP SCHEMA
--- DDL, ALTER FUNCTION, SECURITY DEFINER
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
-CREATE FUNCTION
-ALTER FUNCTION g(int) SECURITY DEFINER;
-ALTER FUNCTION
-DROP FUNCTION g(int);
-DROP FUNCTION
--- DCL, GRANT/REVOKE
--- GRANT { EXECUTE | ALL [ PRIVILEGES ] }
--- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
--- TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
--- REVOKE [ GRANT OPTION FOR ]
--- { EXECUTE | ALL [ PRIVILEGES ] }
--- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
--- FROM { username | GROUP groupname | PUBLIC } [, ...]
--- [ CASCADE | RESTRICT ]
--- DCL, GRANT/REVOKE, EXECUTE
-CREATE ROLE superuser SUPERUSER;
-CREATE ROLE
-SET ROLE superuser;
-SET
-CREATE ROLE u1;
-psql:/tmp/TestUDF_TestUDFBasics.sql:149: NOTICE: resource queue required -- using default resource queue "pg_default"
-CREATE ROLE
-GRANT SELECT ON TABLE foo TO u1;
-GRANT
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-CREATE FUNCTION
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
---------
-
-(1 row)
-
-REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
-REVOKE
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
--------------------------
- {superuser=X/superuser}
-(1 row)
-
-SELECT g(1);
- g
----
- 1
-(1 row)
-
-SELECT count(g(x)) FROM foo;
- count
--------
- 10
-(1 row)
-
-SET ROLE u1;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:158: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:159: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-SET ROLE superuser;
-SET
-GRANT EXECUTE ON FUNCTION g(int) TO u1;
-GRANT
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
-----------------------------------------
- {superuser=X/superuser,u1=X/superuser}
-(1 row)
-
-SET ROLE u1;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:164: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:165: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-SET ROLE superuser;
-SET
-REVOKE EXECUTE ON FUNCTION g(int) FROM u1;
-REVOKE
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
--------------------------
- {superuser=X/superuser}
-(1 row)
-
-SET ROLE u1;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:170: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:171: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-RESET ROLE;
-RESET
-DROP FUNCTION g(int);
-DROP FUNCTION
-REVOKE SELECT ON TABLE foo FROM u1;
-REVOKE
-DROP ROLE u1;
-DROP ROLE
-DROP ROLE superuser;
-DROP ROLE
--- DCL, GRANT/REVOKE, PUBLIC
-CREATE ROLE superuser SUPERUSER;
-CREATE ROLE
-SET ROLE superuser;
-SET
-CREATE ROLE u1;
-psql:/tmp/TestUDF_TestUDFBasics.sql:183: NOTICE: resource queue required -- using default resource queue "pg_default"
-CREATE ROLE
-GRANT SELECT ON TABLE foo TO u1;
-GRANT
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-CREATE FUNCTION
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
---------
-
-(1 row)
-
-REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
-REVOKE
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
--------------------------
- {superuser=X/superuser}
-(1 row)
-
-SELECT g(1);
- g
----
- 1
-(1 row)
-
-SELECT count(g(x)) FROM foo;
- count
--------
- 10
-(1 row)
-
-SET ROLE u1;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:192: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:193: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-SET ROLE superuser;
-SET
-GRANT EXECUTE ON FUNCTION g(int) TO PUBLIC;
-GRANT
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
---------------------------------------
- {superuser=X/superuser,=X/superuser}
-(1 row)
-
-SET ROLE u1;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:198: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:199: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-SET ROLE superuser;
-SET
-REVOKE EXECUTE ON FUNCTION g(int) FROM PUBLIC;
-REVOKE
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
--------------------------
- {superuser=X/superuser}
-(1 row)
-
-SET ROLE u1;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:204: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:205: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-RESET ROLE;
-RESET
-DROP FUNCTION g(int);
-DROP FUNCTION
-REVOKE SELECT ON TABLE foo FROM u1;
-REVOKE
-DROP ROLE u1;
-DROP ROLE
-DROP ROLE superuser;
-DROP ROLE
--- DCL, GRANT/REVOKE, Groups
-CREATE ROLE superuser SUPERUSER;
-CREATE ROLE
-SET ROLE superuser;
-SET
-CREATE ROLE u1;
-psql:/tmp/TestUDF_TestUDFBasics.sql:217: NOTICE: resource queue required -- using default resource queue "pg_default"
-CREATE ROLE
-CREATE ROLE u2 IN GROUP u1;
-psql:/tmp/TestUDF_TestUDFBasics.sql:218: NOTICE: resource queue required -- using default resource queue "pg_default"
-CREATE ROLE
-GRANT SELECT ON TABLE foo TO u1;
-GRANT
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-CREATE FUNCTION
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
---------
-
-(1 row)
-
-REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
-REVOKE
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
--------------------------
- {superuser=X/superuser}
-(1 row)
-
-SELECT g(1);
- g
----
- 1
-(1 row)
-
-SELECT count(g(x)) FROM foo;
- count
--------
- 10
-(1 row)
-
-SET ROLE u2;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:227: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:228: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-SET ROLE superuser;
-SET
-GRANT EXECUTE ON FUNCTION g(int) TO u1;
-GRANT
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
-----------------------------------------
- {superuser=X/superuser,u1=X/superuser}
-(1 row)
-
-SET ROLE u2;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:233: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:234: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-SET ROLE superuser;
-SET
-REVOKE EXECUTE ON FUNCTION g(int) FROM u1;
-REVOKE
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
--------------------------
- {superuser=X/superuser}
-(1 row)
-
-SET ROLE u2;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:239: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:240: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-RESET ROLE;
-RESET
-DROP FUNCTION g(int);
-DROP FUNCTION
-REVOKE SELECT ON TABLE foo FROM u1;
-REVOKE
-DROP ROLE u1;
-DROP ROLE
-DROP ROLE u2;
-DROP ROLE
-DROP ROLE superuser;
-DROP ROLE
--- DCL, GRANT/REVOKE, WITH GRANT OPTION
-CREATE ROLE superuser SUPERUSER;
-CREATE ROLE
-SET ROLE superuser;
-SET
-CREATE ROLE u1;
-psql:/tmp/TestUDF_TestUDFBasics.sql:253: NOTICE: resource queue required -- using default resource queue "pg_default"
-CREATE ROLE
-CREATE ROLE u2;
-psql:/tmp/TestUDF_TestUDFBasics.sql:254: NOTICE: resource queue required -- using default resource queue "pg_default"
-CREATE ROLE
-GRANT SELECT ON TABLE foo TO PUBLIC;
-GRANT
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-CREATE FUNCTION
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
---------
-
-(1 row)
-
-REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
-REVOKE
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
--------------------------
- {superuser=X/superuser}
-(1 row)
-
-SELECT g(1);
- g
----
- 1
-(1 row)
-
-SELECT count(g(x)) FROM foo;
- count
--------
- 10
-(1 row)
-
-SET ROLE u2;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:263: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:264: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-SET ROLE superuser;
-SET
-GRANT ALL ON FUNCTION g(int) TO u1 WITH GRANT OPTION;
-GRANT
-SET ROLE u1;
-SET
-GRANT ALL ON FUNCTION g(int) TO u2;
-psql:/tmp/TestUDF_TestUDFBasics.sql:268: ERROR: function g(integer) does not exist
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
------------------------------------------
- {superuser=X/superuser,u1=X*/superuser}
-(1 row)
-
-SET ROLE u1;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:271: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:272: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-SET ROLE u2;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:274: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:275: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-SET ROLE superuser;
-SET
-REVOKE ALL ON FUNCTION g(int) FROM u1 CASCADE;
-REVOKE
-SELECT proacl FROM pg_proc where proname = 'g';
- proacl
--------------------------
- {superuser=X/superuser}
-(1 row)
-
-SET ROLE u1;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:280: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:281: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-SET ROLE u2;
-SET
-SELECT g(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:283: ERROR: function g(integer) does not exist
-LINE 1: SELECT g(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
-SELECT count(g(x)) FROM foo;
-psql:/tmp/TestUDF_TestUDFBasics.sql:284: ERROR: relation "foo" does not exist
-LINE 1: SELECT count(g(x)) FROM foo;
- ^
-RESET ROLE;
-RESET
-DROP FUNCTION g(int);
-DROP FUNCTION
-REVOKE SELECT ON TABLE foo FROM PUBLIC;
-REVOKE
-DROP ROLE u1;
-DROP ROLE
-DROP ROLE u2;
-DROP ROLE
-DROP ROLE superuser;
-DROP ROLE
--- DML, CaseExpr
-SELECT CASE WHEN x % 2 = 0 THEN f(x) ELSE 0 END FROM foo ORDER BY x;
- case
-------
- 0
- 2
- 0
- 4
- 0
- 6
- 0
- 8
- 0
- 10
-(10 rows)
-
--- DML, OpExpr
-SELECT f(x) + f(x) FROM foo ORDER BY x;
- ?column?
-----------
- 2
- 4
- 6
- 8
- 10
- 12
- 14
- 16
- 18
- 20
-(10 rows)
-
-SELECT f(x) + f(x) + f(x) FROM foo ORDER BY x;
- ?column?
-----------
- 3
- 6
- 9
- 12
- 15
- 18
- 21
- 24
- 27
- 30
-(10 rows)
-
-SELECT f(x) + f(x) - f(x) FROM foo ORDER BY x;
- ?column?
-----------
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-(10 rows)
-
--- DML, FuncExpr
-CREATE FUNCTION g(x INT) RETURNS INT AS $$
-BEGIN
-RETURN x;
-END
-$$ LANGUAGE PLPGSQL;
-CREATE FUNCTION
-SELECT g(f(x)) FROM foo ORDER BY x;
- g
-----
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-(10 rows)
-
-DROP FUNCTION g(int);
-DROP FUNCTION
--- DML, BoolExpr
-SELECT x % 2 = 0 AND f(x) % 2 = 1 FROM foo ORDER BY x;
- ?column?
-----------
- f
- f
- f
- f
- f
- f
- f
- f
- f
- f
-(10 rows)
-
--- DML, DistinctExpr
-SELECT x IS DISTINCT FROM f(x) from foo ORDER BY x;
- ?column?
-----------
- f
- f
- f
- f
- f
- f
- f
- f
- f
- f
-(10 rows)
-
--- DML, PercentileExpr
-SELECT MEDIAN(f(x)) FROM foo;
- median
---------
- 5.5
-(1 row)
-
--- DML, Complex Expression
-CREATE FUNCTION g(x INT) RETURNS INT AS $$
-BEGIN
-RETURN x;
-END
-$$ LANGUAGE PLPGSQL;
-CREATE FUNCTION
-SELECT CASE
- WHEN x % 2 = 0 THEN g(g(x)) + g(g(x))
- WHEN f(x) % 2 = 1 THEN g(g(x)) - g(g(x))
- END FROM foo ORDER BY x;
- case
-------
- 0
- 4
- 0
- 8
- 0
- 12
- 0
- 16
- 0
- 20
-(10 rows)
-
-DROP FUNCTION g(int);
-DROP FUNCTION
--- DML, Qual
-SELECT x FROM foo WHERE f(x) % 2 = 0 ORDER BY x;
- x
-----
- 2
- 4
- 6
- 8
- 10
-(5 rows)
-
--- DML, FROM
-SELECT * FROM f(5);
- f
----
- 5
-(1 row)
-
--- DML, Grouping
-SELECT DISTINCT f(x) FROM foo ORDER BY f(x);
- f
-----
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-(10 rows)
-
-SELECT f(x) FROM foo GROUP BY f(x) ORDER BY f(x);
- f
-----
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-(10 rows)
-
--- DML, Join
-SELECT a.x FROM foo a, foo b WHERE f(a.x) = f(b.x) ORDER BY x;
- x
-----
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-(10 rows)
-
-SELECT a.x FROM foo a JOIN foo b ON f(a.x) = f(b.x) ORDER BY x;
- x
-----
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-(10 rows)
-
--- DML, Windowing
-SELECT avg(x) OVER (PARTITION BY f(x)) FROM foo ORDER BY x;
- avg
------
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-(10 rows)
-
--- DML, CTE
-WITH t AS (SELECT x from foo)
- SELECT f(x) from t ORDER BY x;
- f
-----
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-(10 rows)
-
--- DML, InitPlan
-SELECT UNNEST(ARRAY(SELECT x FROM foo)) ORDER BY 1;
- unnest
---------
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-(10 rows)
-
-SELECT UNNEST(ARRAY(SELECT f(1)));
- unnest
---------
- 1
-(1 row)
-
--- PROPERTIES, VOLATILITY, IMMUTABLE
-CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL IMMUTABLE;
-CREATE FUNCTION
-SELECT COUNT(DISTINCT(g())) > 1 FROM foo;
- ?column?
-----------
- f
-(1 row)
-
-DROP FUNCTION g();
-DROP FUNCTION
--- PROPERTIES, VOLATILITY, STABLE
-CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL STABLE;
-CREATE FUNCTION
-SELECT COUNT(DISTINCT(g())) > 1 FROM foo;
- ?column?
-----------
- f
-(1 row)
-
-DROP FUNCTION g();
-DROP FUNCTION
--- PROPERTIES, VOLATILITY, VOLATILE
-CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL VOLATILE;
-CREATE FUNCTION
-SELECT COUNT(DISTINCT(g())) > 1 FROM foo;
- ?column?
-----------
- t
-(1 row)
-
-DROP FUNCTION g();
-DROP FUNCTION
------------------
--- NEGATIVE TESTS
------------------
-SELECT h(1);
-psql:/tmp/TestUDF_TestUDFBasics.sql:405: ERROR: function h(integer) does not exist
-LINE 1: SELECT h(1);
- ^
-HINT: No function matches the given name and argument types. You may need to add explicit type casts.
--- DML, InitPlan
-SELECT UNNEST(ARRAY(SELECT f(x) from foo));
- unnest
---------
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-(10 rows)
-
--- LANGUAGES not yet supported
--- CREATE LANGUAGE plr;
--- CREATE LANGUAGE plpython;
--- CREATE LANGUAGE pljava;
--- CREATE LANGUAGE plperl;
--- NESTED FUNCTION
-CREATE FUNCTION inner(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
-CREATE FUNCTION
-CREATE FUNCTION outer(x INT) RETURNS INT AS $$
-BEGIN
-RETURN inner(x);
-END
-$$ LANGUAGE PLPGSQL;
-CREATE FUNCTION
-SELECT outer(0);
- outer
--------
- 1
-(1 row)
-
-SELECT outer(0) FROM foo;
- outer
--------
- 1
- 1
- 1
- 1
- 1
- 1
- 1
- 1
- 1
- 1
-(10 rows)
-
-DROP FUNCTION outer(int);
-DROP FUNCTION
-DROP FUNCTION inner(int);
-DROP FUNCTION
--- TEARDOWN
-DROP TABLE foo;
-DROP TABLE
--- HAWQ-510
-drop table if exists testEntryDB;
-psql:/tmp/TestUDF_TestUDFBasics.sql:435: NOTICE: table "testentrydb" does not exist, skipping
-DROP TABLE
-create table testEntryDB(key int, value int) distributed randomly;
-CREATE TABLE
-insert into testEntryDB values(1, 0);
-INSERT 0 1
-select t2.key, t2.value
-from (select key, value from testEntryDB where value = 0) as t1,
- (select generate_series(1,2)::int as key, 0::int as value) as t2
-where t1.value=t2.value;
- key | value
------+-------
- 1 | 0
- 2 | 0
-(2 rows)
-
-drop table testEntryDB;
-DROP TABLE
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/udf/ans/function_extension.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/udf/ans/function_extension.ans b/src/test/feature/udf/ans/function_extension.ans
deleted file mode 100755
index e1d14f4..0000000
--- a/src/test/feature/udf/ans/function_extension.ans
+++ /dev/null
@@ -1,183 +0,0 @@
--- 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
[3/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
Posted by hu...@apache.org.
HAWQ-814. Enhance user-defined function by migrating create_function_1 of UDF from installcheck to new feature test framework
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/49fd529a
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/49fd529a
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/49fd529a
Branch: refs/heads/master
Commit: 49fd529aa01d54199465639060c01577bca06981
Parents: c66cfba
Author: Ruilong Huo <rh...@pivotal.io>
Authored: Tue Jun 28 09:49:05 2016 +0800
Committer: Ruilong Huo <rh...@pivotal.io>
Committed: Tue Jul 5 09:40:03 2016 +0800
----------------------------------------------------------------------
src/test/feature/Makefile | 12 +-
src/test/feature/UDF/TestUDF.cpp | 63 +
.../feature/UDF/ans/function_basics.ans.orca | 1088 +++++++++++++++
.../feature/UDF/ans/function_basics.ans.planner | 1076 +++++++++++++++
.../UDF/ans/function_creation.ans.source | 96 ++
src/test/feature/UDF/ans/function_extension.ans | 183 +++
.../feature/UDF/ans/function_set_returning.ans | 287 ++++
src/test/feature/UDF/lib/Makefile | 43 +
src/test/feature/UDF/lib/function.c | 1245 ++++++++++++++++++
src/test/feature/UDF/sql/function_basics.sql | 439 ++++++
.../UDF/sql/function_creation.sql.source | 79 ++
src/test/feature/UDF/sql/function_extension.sql | 123 ++
.../feature/UDF/sql/function_set_returning.sql | 93 ++
src/test/feature/udf/TestUDF.cpp | 32 -
src/test/feature/udf/ans/function_basics.ans | 1088 ---------------
src/test/feature/udf/ans/function_extension.ans | 183 ---
.../feature/udf/ans/function_set_returning.ans | 287 ----
src/test/feature/udf/sql/function_basics.sql | 439 ------
src/test/feature/udf/sql/function_extension.sql | 123 --
.../feature/udf/sql/function_set_returning.sql | 93 --
20 files changed, 4824 insertions(+), 2248 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/Makefile
----------------------------------------------------------------------
diff --git a/src/test/feature/Makefile b/src/test/feature/Makefile
index 82a3dc3..e97716c 100644
--- a/src/test/feature/Makefile
+++ b/src/test/feature/Makefile
@@ -15,16 +15,22 @@ override LDFLAGS += -L/usr/local/lib -L/usr/lib -L$(abs_top_srcdir)/src/test/fea
PROG = test_main.cpp $(wildcard */*.cpp)
RM = rm -rf
-.PHONY: all distclean clean doc
+.PHONY: all sharelib sharelibclean distclean clean doc
-all:
+all: sharelib
$(MAKE) -C lib all
$(CXX) $(CPPFLAGS) $(CXXFLAGS) $(PROG) $(LDFLAGS) $(LIBS) -o feature-test
+sharelib:
+ cd UDF/lib || exit 1; $(MAKE) || exit 2; $(MAKE) clean || exit 3
+
+sharelibclean:
+ cd UDF/lib || exit 1; $(RM) *.o *.so || exit 2
+
doc:
doxygen doxygen_template
-clean distclean:
+clean distclean: sharelibclean
$(RM) feature-test
$(RM) feature-test.dSYM
$(RM) doc
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/TestUDF.cpp
----------------------------------------------------------------------
diff --git a/src/test/feature/UDF/TestUDF.cpp b/src/test/feature/UDF/TestUDF.cpp
new file mode 100755
index 0000000..ce7f158
--- /dev/null
+++ b/src/test/feature/UDF/TestUDF.cpp
@@ -0,0 +1,63 @@
+#include "gtest/gtest.h"
+
+#include "lib/sql_util.h"
+#include "lib/file_replace.h"
+
+
+class TestUDF: public ::testing::Test
+{
+ public:
+ TestUDF() {}
+ ~TestUDF() {}
+};
+
+TEST_F(TestUDF, TestUDFBasics)
+{
+ hawq::test::SQLUtility util;
+ if (util.getGUCValue("optimizer") == "on")
+ {
+ util.execSQLFile("UDF/sql/function_basics.sql",
+ "UDF/ans/function_basics.ans.orca");
+ }
+ else
+ {
+ util.execSQLFile("UDF/sql/function_basics.sql",
+ "UDF/ans/function_basics.ans.planner");
+ }
+}
+
+TEST_F(TestUDF, TestUDFCreation)
+{
+ // preprocess source files to get sql/ans files
+ hawq::test::SQLUtility util;
+ std::string d_feature_test_root(util.getTestRootPath());
+ std::string f_sql_tpl(d_feature_test_root + "/UDF/sql/function_creation.sql.source");
+ std::string f_ans_tpl(d_feature_test_root + "/UDF/ans/function_creation.ans.source");
+ std::string f_sql(d_feature_test_root + "/UDF/sql/function_creation.sql");
+ std::string f_ans(d_feature_test_root + "/UDF/ans/function_creation.ans");
+
+ hawq::test::FileReplace frep;
+ std::unordered_map<std::string, std::string> strs_src_dst;
+ strs_src_dst["@SHARE_LIBRARY_PATH@"] = d_feature_test_root + "/UDF/lib/function.so";
+
+ frep.replace(f_sql_tpl, f_sql, strs_src_dst);
+ frep.replace(f_ans_tpl, f_ans, strs_src_dst);
+
+ // run sql file to get ans file and then diff it with out file
+ util.execSQLFile("UDF/sql/function_creation.sql",
+ "UDF/ans/function_creation.ans");
+}
+
+TEST_F(TestUDF, TestUDFSetReturning)
+{
+ hawq::test::SQLUtility util;
+ util.execSQLFile("UDF/sql/function_set_returning.sql",
+ "UDF/ans/function_set_returning.ans");
+}
+
+TEST_F(TestUDF, TestUDFExtension)
+{
+ hawq::test::SQLUtility util;
+ util.execSQLFile("UDF/sql/function_extension.sql",
+ "UDF/ans/function_extension.ans");
+}
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/ans/function_basics.ans.orca
----------------------------------------------------------------------
diff --git a/src/test/feature/UDF/ans/function_basics.ans.orca b/src/test/feature/UDF/ans/function_basics.ans.orca
new file mode 100755
index 0000000..ff45af2
--- /dev/null
+++ b/src/test/feature/UDF/ans/function_basics.ans.orca
@@ -0,0 +1,1088 @@
+-- start_ignore
+SET SEARCH_PATH=TestUDF_TestUDFBasics;
+SET
+-- end_ignore
+-- SETUP
+DROP TABLE IF EXISTS foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:5: NOTICE: table "foo" does not exist, skipping
+DROP TABLE
+CREATE TABLE foo AS SELECT * FROM generate_series(1, 10) x;
+SELECT 10
+CREATE FUNCTION f(x INT) RETURNS INT AS $$
+BEGIN
+RETURN x;
+END
+$$ LANGUAGE PLPGSQL;
+CREATE FUNCTION
+-- DDL, CREATE FUNCTION
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+SELECT proname FROM pg_proc WHERE proname = 'g';
+ proname
+---------
+ g
+(1 row)
+
+SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g';
+ proname
+---------
+(0 rows)
+
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DDL, CREATE OR REPLACE FUNCTION
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+SELECT proname FROM pg_proc WHERE proname = 'g';
+ proname
+---------
+ g
+(1 row)
+
+SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g';
+ proname
+---------
+(0 rows)
+
+CREATE OR REPLACE FUNCTION g(x INT) RETURNS INT AS $$
+BEGIN
+RETURN (-1) * x;
+END
+$$ LANGUAGE PLPGSQL;
+CREATE FUNCTION
+SELECT proname, prosrc FROM pg_proc WHERE proname = 'g';
+ proname | prosrc
+---------+------------------
+ g |
+ : BEGIN
+ : RETURN (-1) * x;
+ : END
+ :
+(1 row)
+
+SELECT proname, prosrc FROM gp_dist_random('pg_proc') WHERE proname = 'g';
+ proname | prosrc
+---------+--------
+(0 rows)
+
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DDL, DROP FUNCTION
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+DROP FUNCTION g(int);
+DROP FUNCTION
+SELECT oid, proname FROM pg_proc WHERE proname = 'g';
+ oid | proname
+-----+---------
+(0 rows)
+
+SELECT oid, proname FROM gp_dist_random('pg_proc') WHERE proname = 'g';
+ oid | proname
+-----+---------
+(0 rows)
+
+-- DDL, DROP FUNCTION, NEGATIVE
+DROP FUNCTION g(int);
+psql:/tmp/TestUDF_TestUDFBasics.sql:47: ERROR: function g(integer) does not exist
+-- DDL, CREATE FUNCTION, RECORD
+CREATE FUNCTION foo(int) RETURNS record AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL;
+CREATE FUNCTION
+SELECT foo(5);
+ foo
+-----
+ (5)
+(1 row)
+
+DROP FUNCTION foo(int);
+DROP FUNCTION
+CREATE FUNCTION foo(int) RETURNS foo AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL;
+CREATE FUNCTION
+SELECT foo(5);
+ foo
+-----
+ (5)
+(1 row)
+
+DROP FUNCTION foo(int);
+DROP FUNCTION
+-- DDL, CREATE FUNCTION, SRF
+CREATE FUNCTION g(x setof int) RETURNS INT
+ AS $$ SELECT 1 $$ LANGUAGE SQL;
+CREATE FUNCTION
+DROP FUNCTION g(setof int);
+DROP FUNCTION
+CREATE FUNCTION g() RETURNS setof INT
+ AS $$ SELECT 1 $$ LANGUAGE SQL;
+CREATE FUNCTION
+DROP FUNCTION g();
+DROP FUNCTION
+-- DDL, CREATE FUNCTION, TABLE, NEGATIVE
+CREATE FUNCTION g() RETURNS TABLE(x int)
+ AS $$ SELECT * FROM foo $$ LANGUAGE SQL;
+CREATE FUNCTION
+DROP FUNCTION g();
+DROP FUNCTION
+CREATE FUNCTION g(anytable) RETURNS int
+ AS 'does_not_exist', 'does_not_exist' LANGUAGE C;
+psql:/tmp/TestUDF_TestUDFBasics.sql:76: ERROR: TABLE functions not supported
+-- DDL, CREATE FUNCTION, SECURITY DEFINER
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE SECURITY DEFINER;
+CREATE FUNCTION
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DDL, ALTER FUNCTION
+-- DDL, STRICT
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+SELECT g(NULL);
+ g
+---
+ 1
+(1 row)
+
+ALTER FUNCTION g(int) STRICT;
+ALTER FUNCTION
+SELECT g(NULL);
+ g
+---
+
+(1 row)
+
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DDL, ALTER FUNCTION, OWNER
+CREATE ROLE superuser SUPERUSER;
+CREATE ROLE
+CREATE ROLE u1;
+psql:/tmp/TestUDF_TestUDFBasics.sql:97: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+SET ROLE superuser;
+SET
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g';
+ rolname
+-----------
+ superuser
+(1 row)
+
+ALTER FUNCTION g(int) OWNER TO u1;
+ALTER FUNCTION
+SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g';
+ rolname
+---------
+ u1
+(1 row)
+
+DROP FUNCTION g(int);
+DROP FUNCTION
+RESET ROLE;
+RESET
+DROP ROLE u1;
+DROP ROLE
+DROP ROLE superuser;
+DROP ROLE
+-- DDL, ALTER FUNCTION, RENAME
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+SELECT g(0);
+ g
+---
+ 1
+(1 row)
+
+ALTER FUNCTION g(int) RENAME TO h;
+ALTER FUNCTION
+SELECT h(0);
+ h
+---
+ 1
+(1 row)
+
+DROP FUNCTION h(int);
+DROP FUNCTION
+-- DDL, ALTER FUNCTION, SET SCHEMA
+CREATE SCHEMA bar;
+CREATE SCHEMA
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+SELECT g(0);
+ g
+---
+ 1
+(1 row)
+
+ALTER FUNCTION g(int) SET SCHEMA bar;
+ALTER FUNCTION
+SELECT bar.g(0);
+ g
+---
+ 1
+(1 row)
+
+DROP SCHEMA bar CASCADE;
+psql:/tmp/TestUDF_TestUDFBasics.sql:125: NOTICE: drop cascades to function bar.g(integer)
+DROP SCHEMA
+-- DDL, ALTER FUNCTION, SECURITY DEFINER
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+ALTER FUNCTION g(int) SECURITY DEFINER;
+ALTER FUNCTION
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DCL, GRANT/REVOKE
+-- GRANT { EXECUTE | ALL [ PRIVILEGES ] }
+-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
+-- TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+-- REVOKE [ GRANT OPTION FOR ]
+-- { EXECUTE | ALL [ PRIVILEGES ] }
+-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
+-- FROM { username | GROUP groupname | PUBLIC } [, ...]
+-- [ CASCADE | RESTRICT ]
+-- DCL, GRANT/REVOKE, EXECUTE
+CREATE ROLE superuser SUPERUSER;
+CREATE ROLE
+SET ROLE superuser;
+SET
+CREATE ROLE u1;
+psql:/tmp/TestUDF_TestUDFBasics.sql:149: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+GRANT SELECT ON TABLE foo TO u1;
+GRANT
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+--------
+
+(1 row)
+
+REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SELECT g(1);
+ g
+---
+ 1
+(1 row)
+
+SELECT count(g(x)) FROM foo;
+ count
+-------
+ 10
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:158: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:159: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+GRANT EXECUTE ON FUNCTION g(int) TO u1;
+GRANT
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+----------------------------------------
+ {superuser=X/superuser,u1=X/superuser}
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:164: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:165: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+REVOKE EXECUTE ON FUNCTION g(int) FROM u1;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:170: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:171: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+RESET ROLE;
+RESET
+DROP FUNCTION g(int);
+DROP FUNCTION
+REVOKE SELECT ON TABLE foo FROM u1;
+REVOKE
+DROP ROLE u1;
+DROP ROLE
+DROP ROLE superuser;
+DROP ROLE
+-- DCL, GRANT/REVOKE, PUBLIC
+CREATE ROLE superuser SUPERUSER;
+CREATE ROLE
+SET ROLE superuser;
+SET
+CREATE ROLE u1;
+psql:/tmp/TestUDF_TestUDFBasics.sql:183: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+GRANT SELECT ON TABLE foo TO u1;
+GRANT
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+--------
+
+(1 row)
+
+REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SELECT g(1);
+ g
+---
+ 1
+(1 row)
+
+SELECT count(g(x)) FROM foo;
+ count
+-------
+ 10
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:192: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:193: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+GRANT EXECUTE ON FUNCTION g(int) TO PUBLIC;
+GRANT
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+--------------------------------------
+ {superuser=X/superuser,=X/superuser}
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:198: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:199: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+REVOKE EXECUTE ON FUNCTION g(int) FROM PUBLIC;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:204: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:205: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+RESET ROLE;
+RESET
+DROP FUNCTION g(int);
+DROP FUNCTION
+REVOKE SELECT ON TABLE foo FROM u1;
+REVOKE
+DROP ROLE u1;
+DROP ROLE
+DROP ROLE superuser;
+DROP ROLE
+-- DCL, GRANT/REVOKE, Groups
+CREATE ROLE superuser SUPERUSER;
+CREATE ROLE
+SET ROLE superuser;
+SET
+CREATE ROLE u1;
+psql:/tmp/TestUDF_TestUDFBasics.sql:217: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+CREATE ROLE u2 IN GROUP u1;
+psql:/tmp/TestUDF_TestUDFBasics.sql:218: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+GRANT SELECT ON TABLE foo TO u1;
+GRANT
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+--------
+
+(1 row)
+
+REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SELECT g(1);
+ g
+---
+ 1
+(1 row)
+
+SELECT count(g(x)) FROM foo;
+ count
+-------
+ 10
+(1 row)
+
+SET ROLE u2;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:227: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:228: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+GRANT EXECUTE ON FUNCTION g(int) TO u1;
+GRANT
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+----------------------------------------
+ {superuser=X/superuser,u1=X/superuser}
+(1 row)
+
+SET ROLE u2;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:233: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:234: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+REVOKE EXECUTE ON FUNCTION g(int) FROM u1;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SET ROLE u2;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:239: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:240: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+RESET ROLE;
+RESET
+DROP FUNCTION g(int);
+DROP FUNCTION
+REVOKE SELECT ON TABLE foo FROM u1;
+REVOKE
+DROP ROLE u1;
+DROP ROLE
+DROP ROLE u2;
+DROP ROLE
+DROP ROLE superuser;
+DROP ROLE
+-- DCL, GRANT/REVOKE, WITH GRANT OPTION
+CREATE ROLE superuser SUPERUSER;
+CREATE ROLE
+SET ROLE superuser;
+SET
+CREATE ROLE u1;
+psql:/tmp/TestUDF_TestUDFBasics.sql:253: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+CREATE ROLE u2;
+psql:/tmp/TestUDF_TestUDFBasics.sql:254: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+GRANT SELECT ON TABLE foo TO PUBLIC;
+GRANT
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+--------
+
+(1 row)
+
+REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SELECT g(1);
+ g
+---
+ 1
+(1 row)
+
+SELECT count(g(x)) FROM foo;
+ count
+-------
+ 10
+(1 row)
+
+SET ROLE u2;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:263: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:264: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+GRANT ALL ON FUNCTION g(int) TO u1 WITH GRANT OPTION;
+GRANT
+SET ROLE u1;
+SET
+GRANT ALL ON FUNCTION g(int) TO u2;
+psql:/tmp/TestUDF_TestUDFBasics.sql:268: ERROR: function g(integer) does not exist
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-----------------------------------------
+ {superuser=X/superuser,u1=X*/superuser}
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:271: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:272: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE u2;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:274: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:275: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+REVOKE ALL ON FUNCTION g(int) FROM u1 CASCADE;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:280: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:281: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE u2;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:283: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:284: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+RESET ROLE;
+RESET
+DROP FUNCTION g(int);
+DROP FUNCTION
+REVOKE SELECT ON TABLE foo FROM PUBLIC;
+REVOKE
+DROP ROLE u1;
+DROP ROLE
+DROP ROLE u2;
+DROP ROLE
+DROP ROLE superuser;
+DROP ROLE
+-- DML, CaseExpr
+SELECT CASE WHEN x % 2 = 0 THEN f(x) ELSE 0 END FROM foo ORDER BY x;
+ case
+------
+ 0
+ 2
+ 0
+ 4
+ 0
+ 6
+ 0
+ 8
+ 0
+ 10
+(10 rows)
+
+-- DML, OpExpr
+SELECT f(x) + f(x) FROM foo ORDER BY x;
+ ?column?
+----------
+ 2
+ 4
+ 6
+ 8
+ 10
+ 12
+ 14
+ 16
+ 18
+ 20
+(10 rows)
+
+SELECT f(x) + f(x) + f(x) FROM foo ORDER BY x;
+ ?column?
+----------
+ 3
+ 6
+ 9
+ 12
+ 15
+ 18
+ 21
+ 24
+ 27
+ 30
+(10 rows)
+
+SELECT f(x) + f(x) - f(x) FROM foo ORDER BY x;
+ ?column?
+----------
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- DML, FuncExpr
+CREATE FUNCTION g(x INT) RETURNS INT AS $$
+BEGIN
+RETURN x;
+END
+$$ LANGUAGE PLPGSQL;
+CREATE FUNCTION
+SELECT g(f(x)) FROM foo ORDER BY x;
+ g
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DML, BoolExpr
+SELECT x % 2 = 0 AND f(x) % 2 = 1 FROM foo ORDER BY x;
+ ?column?
+----------
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+(10 rows)
+
+-- DML, DistinctExpr
+SELECT x IS DISTINCT FROM f(x) from foo ORDER BY x;
+ ?column?
+----------
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+(10 rows)
+
+-- DML, PercentileExpr
+SELECT MEDIAN(f(x)) FROM foo;
+ median
+--------
+ 5.5
+(1 row)
+
+-- DML, Complex Expression
+CREATE FUNCTION g(x INT) RETURNS INT AS $$
+BEGIN
+RETURN x;
+END
+$$ LANGUAGE PLPGSQL;
+CREATE FUNCTION
+SELECT CASE
+ WHEN x % 2 = 0 THEN g(g(x)) + g(g(x))
+ WHEN f(x) % 2 = 1 THEN g(g(x)) - g(g(x))
+ END FROM foo ORDER BY x;
+ case
+------
+ 0
+ 4
+ 0
+ 8
+ 0
+ 12
+ 0
+ 16
+ 0
+ 20
+(10 rows)
+
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DML, Qual
+SELECT x FROM foo WHERE f(x) % 2 = 0 ORDER BY x;
+ x
+----
+ 2
+ 4
+ 6
+ 8
+ 10
+(5 rows)
+
+-- DML, FROM
+SELECT * FROM f(5);
+ f
+---
+ 5
+(1 row)
+
+-- DML, Grouping
+SELECT DISTINCT f(x) FROM foo ORDER BY f(x);
+ f
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+SELECT f(x) FROM foo GROUP BY f(x) ORDER BY f(x);
+ f
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- DML, Join
+SELECT a.x FROM foo a, foo b WHERE f(a.x) = f(b.x) ORDER BY x;
+ x
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+SELECT a.x FROM foo a JOIN foo b ON f(a.x) = f(b.x) ORDER BY x;
+ x
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- DML, Windowing
+SELECT avg(x) OVER (PARTITION BY f(x)) FROM foo ORDER BY x;
+ avg
+-----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- DML, CTE
+WITH t AS (SELECT x from foo)
+ SELECT f(x) from t ORDER BY x;
+ f
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- DML, InitPlan
+SELECT UNNEST(ARRAY(SELECT x FROM foo)) ORDER BY 1;
+ unnest
+--------
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+SELECT UNNEST(ARRAY(SELECT f(1)));
+ unnest
+--------
+ 1
+(1 row)
+
+-- PROPERTIES, VOLATILITY, IMMUTABLE
+CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+SELECT COUNT(DISTINCT(g())) > 1 FROM foo;
+ ?column?
+----------
+ f
+(1 row)
+
+DROP FUNCTION g();
+DROP FUNCTION
+-- PROPERTIES, VOLATILITY, STABLE
+CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL STABLE;
+CREATE FUNCTION
+SELECT COUNT(DISTINCT(g())) > 1 FROM foo;
+ ?column?
+----------
+ f
+(1 row)
+
+DROP FUNCTION g();
+DROP FUNCTION
+-- PROPERTIES, VOLATILITY, VOLATILE
+CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL VOLATILE;
+CREATE FUNCTION
+SELECT COUNT(DISTINCT(g())) > 1 FROM foo;
+ ?column?
+----------
+ t
+(1 row)
+
+DROP FUNCTION g();
+DROP FUNCTION
+-----------------
+-- NEGATIVE TESTS
+-----------------
+SELECT h(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:405: ERROR: function h(integer) does not exist
+LINE 1: SELECT h(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+-- DML, InitPlan
+SELECT UNNEST(ARRAY(SELECT f(x) from foo));
+ unnest
+--------
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- LANGUAGES not yet supported
+-- CREATE LANGUAGE plr;
+-- CREATE LANGUAGE plpython;
+-- CREATE LANGUAGE pljava;
+-- CREATE LANGUAGE plperl;
+-- NESTED FUNCTION
+CREATE FUNCTION inner(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+CREATE FUNCTION outer(x INT) RETURNS INT AS $$
+BEGIN
+RETURN inner(x);
+END
+$$ LANGUAGE PLPGSQL;
+CREATE FUNCTION
+SELECT outer(0);
+ outer
+-------
+ 1
+(1 row)
+
+SELECT outer(0) FROM foo;
+ outer
+-------
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+(10 rows)
+
+DROP FUNCTION outer(int);
+DROP FUNCTION
+DROP FUNCTION inner(int);
+DROP FUNCTION
+-- TEARDOWN
+DROP TABLE foo;
+DROP TABLE
+-- HAWQ-510
+drop table if exists testEntryDB;
+psql:/tmp/TestUDF_TestUDFBasics.sql:435: NOTICE: table "testentrydb" does not exist, skipping
+DROP TABLE
+create table testEntryDB(key int, value int) distributed randomly;
+CREATE TABLE
+insert into testEntryDB values(1, 0);
+INSERT 0 1
+select t2.key, t2.value
+from (select key, value from testEntryDB where value = 0) as t1,
+ (select generate_series(1,2)::int as key, 0::int as value) as t2
+where t1.value=t2.value;
+ key | value
+-----+-------
+ 1 | 0
+ 2 | 0
+(2 rows)
+
+drop table testEntryDB;
+DROP TABLE
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/ans/function_basics.ans.planner
----------------------------------------------------------------------
diff --git a/src/test/feature/UDF/ans/function_basics.ans.planner b/src/test/feature/UDF/ans/function_basics.ans.planner
new file mode 100755
index 0000000..763223f
--- /dev/null
+++ b/src/test/feature/UDF/ans/function_basics.ans.planner
@@ -0,0 +1,1076 @@
+-- start_ignore
+SET SEARCH_PATH=TestUDF_TestUDFBasics;
+SET
+-- end_ignore
+-- SETUP
+DROP TABLE IF EXISTS foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:5: NOTICE: table "foo" does not exist, skipping
+DROP TABLE
+CREATE TABLE foo AS SELECT * FROM generate_series(1, 10) x;
+SELECT 10
+CREATE FUNCTION f(x INT) RETURNS INT AS $$
+BEGIN
+RETURN x;
+END
+$$ LANGUAGE PLPGSQL;
+CREATE FUNCTION
+-- DDL, CREATE FUNCTION
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+SELECT proname FROM pg_proc WHERE proname = 'g';
+ proname
+---------
+ g
+(1 row)
+
+SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g';
+ proname
+---------
+(0 rows)
+
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DDL, CREATE OR REPLACE FUNCTION
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+SELECT proname FROM pg_proc WHERE proname = 'g';
+ proname
+---------
+ g
+(1 row)
+
+SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g';
+ proname
+---------
+(0 rows)
+
+CREATE OR REPLACE FUNCTION g(x INT) RETURNS INT AS $$
+BEGIN
+RETURN (-1) * x;
+END
+$$ LANGUAGE PLPGSQL;
+CREATE FUNCTION
+SELECT proname, prosrc FROM pg_proc WHERE proname = 'g';
+ proname | prosrc
+---------+------------------
+ g |
+ : BEGIN
+ : RETURN (-1) * x;
+ : END
+ :
+(1 row)
+
+SELECT proname, prosrc FROM gp_dist_random('pg_proc') WHERE proname = 'g';
+ proname | prosrc
+---------+--------
+(0 rows)
+
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DDL, DROP FUNCTION
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+DROP FUNCTION g(int);
+DROP FUNCTION
+SELECT oid, proname FROM pg_proc WHERE proname = 'g';
+ oid | proname
+-----+---------
+(0 rows)
+
+SELECT oid, proname FROM gp_dist_random('pg_proc') WHERE proname = 'g';
+ oid | proname
+-----+---------
+(0 rows)
+
+-- DDL, DROP FUNCTION, NEGATIVE
+DROP FUNCTION g(int);
+psql:/tmp/TestUDF_TestUDFBasics.sql:47: ERROR: function g(integer) does not exist
+-- DDL, CREATE FUNCTION, RECORD
+CREATE FUNCTION foo(int) RETURNS record AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL;
+CREATE FUNCTION
+SELECT foo(5);
+ foo
+-----
+ (5)
+(1 row)
+
+DROP FUNCTION foo(int);
+DROP FUNCTION
+CREATE FUNCTION foo(int) RETURNS foo AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL;
+CREATE FUNCTION
+SELECT foo(5);
+ foo
+-----
+ (5)
+(1 row)
+
+DROP FUNCTION foo(int);
+DROP FUNCTION
+-- DDL, CREATE FUNCTION, SRF
+CREATE FUNCTION g(x setof int) RETURNS INT
+ AS $$ SELECT 1 $$ LANGUAGE SQL;
+CREATE FUNCTION
+DROP FUNCTION g(setof int);
+DROP FUNCTION
+CREATE FUNCTION g() RETURNS setof INT
+ AS $$ SELECT 1 $$ LANGUAGE SQL;
+CREATE FUNCTION
+DROP FUNCTION g();
+DROP FUNCTION
+-- DDL, CREATE FUNCTION, TABLE, NEGATIVE
+CREATE FUNCTION g() RETURNS TABLE(x int)
+ AS $$ SELECT * FROM foo $$ LANGUAGE SQL;
+CREATE FUNCTION
+DROP FUNCTION g();
+DROP FUNCTION
+CREATE FUNCTION g(anytable) RETURNS int
+ AS 'does_not_exist', 'does_not_exist' LANGUAGE C;
+psql:/tmp/TestUDF_TestUDFBasics.sql:76: ERROR: TABLE functions not supported
+-- DDL, CREATE FUNCTION, SECURITY DEFINER
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE SECURITY DEFINER;
+CREATE FUNCTION
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DDL, ALTER FUNCTION
+-- DDL, STRICT
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+SELECT g(NULL);
+ g
+---
+ 1
+(1 row)
+
+ALTER FUNCTION g(int) STRICT;
+ALTER FUNCTION
+SELECT g(NULL);
+ g
+---
+
+(1 row)
+
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DDL, ALTER FUNCTION, OWNER
+CREATE ROLE superuser SUPERUSER;
+CREATE ROLE
+CREATE ROLE u1;
+psql:/tmp/TestUDF_TestUDFBasics.sql:97: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+SET ROLE superuser;
+SET
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g';
+ rolname
+-----------
+ superuser
+(1 row)
+
+ALTER FUNCTION g(int) OWNER TO u1;
+ALTER FUNCTION
+SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g';
+ rolname
+---------
+ u1
+(1 row)
+
+DROP FUNCTION g(int);
+DROP FUNCTION
+RESET ROLE;
+RESET
+DROP ROLE u1;
+DROP ROLE
+DROP ROLE superuser;
+DROP ROLE
+-- DDL, ALTER FUNCTION, RENAME
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+SELECT g(0);
+ g
+---
+ 1
+(1 row)
+
+ALTER FUNCTION g(int) RENAME TO h;
+ALTER FUNCTION
+SELECT h(0);
+ h
+---
+ 1
+(1 row)
+
+DROP FUNCTION h(int);
+DROP FUNCTION
+-- DDL, ALTER FUNCTION, SET SCHEMA
+CREATE SCHEMA bar;
+CREATE SCHEMA
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+SELECT g(0);
+ g
+---
+ 1
+(1 row)
+
+ALTER FUNCTION g(int) SET SCHEMA bar;
+ALTER FUNCTION
+SELECT bar.g(0);
+ g
+---
+ 1
+(1 row)
+
+DROP SCHEMA bar CASCADE;
+psql:/tmp/TestUDF_TestUDFBasics.sql:125: NOTICE: drop cascades to function bar.g(integer)
+DROP SCHEMA
+-- DDL, ALTER FUNCTION, SECURITY DEFINER
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+ALTER FUNCTION g(int) SECURITY DEFINER;
+ALTER FUNCTION
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DCL, GRANT/REVOKE
+-- GRANT { EXECUTE | ALL [ PRIVILEGES ] }
+-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
+-- TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+-- REVOKE [ GRANT OPTION FOR ]
+-- { EXECUTE | ALL [ PRIVILEGES ] }
+-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
+-- FROM { username | GROUP groupname | PUBLIC } [, ...]
+-- [ CASCADE | RESTRICT ]
+-- DCL, GRANT/REVOKE, EXECUTE
+CREATE ROLE superuser SUPERUSER;
+CREATE ROLE
+SET ROLE superuser;
+SET
+CREATE ROLE u1;
+psql:/tmp/TestUDF_TestUDFBasics.sql:149: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+GRANT SELECT ON TABLE foo TO u1;
+GRANT
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+--------
+
+(1 row)
+
+REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SELECT g(1);
+ g
+---
+ 1
+(1 row)
+
+SELECT count(g(x)) FROM foo;
+ count
+-------
+ 10
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:158: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:159: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+GRANT EXECUTE ON FUNCTION g(int) TO u1;
+GRANT
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+----------------------------------------
+ {superuser=X/superuser,u1=X/superuser}
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:164: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:165: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+REVOKE EXECUTE ON FUNCTION g(int) FROM u1;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:170: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:171: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+RESET ROLE;
+RESET
+DROP FUNCTION g(int);
+DROP FUNCTION
+REVOKE SELECT ON TABLE foo FROM u1;
+REVOKE
+DROP ROLE u1;
+DROP ROLE
+DROP ROLE superuser;
+DROP ROLE
+-- DCL, GRANT/REVOKE, PUBLIC
+CREATE ROLE superuser SUPERUSER;
+CREATE ROLE
+SET ROLE superuser;
+SET
+CREATE ROLE u1;
+psql:/tmp/TestUDF_TestUDFBasics.sql:183: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+GRANT SELECT ON TABLE foo TO u1;
+GRANT
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+--------
+
+(1 row)
+
+REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SELECT g(1);
+ g
+---
+ 1
+(1 row)
+
+SELECT count(g(x)) FROM foo;
+ count
+-------
+ 10
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:192: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:193: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+GRANT EXECUTE ON FUNCTION g(int) TO PUBLIC;
+GRANT
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+--------------------------------------
+ {superuser=X/superuser,=X/superuser}
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:198: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:199: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+REVOKE EXECUTE ON FUNCTION g(int) FROM PUBLIC;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:204: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:205: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+RESET ROLE;
+RESET
+DROP FUNCTION g(int);
+DROP FUNCTION
+REVOKE SELECT ON TABLE foo FROM u1;
+REVOKE
+DROP ROLE u1;
+DROP ROLE
+DROP ROLE superuser;
+DROP ROLE
+-- DCL, GRANT/REVOKE, Groups
+CREATE ROLE superuser SUPERUSER;
+CREATE ROLE
+SET ROLE superuser;
+SET
+CREATE ROLE u1;
+psql:/tmp/TestUDF_TestUDFBasics.sql:217: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+CREATE ROLE u2 IN GROUP u1;
+psql:/tmp/TestUDF_TestUDFBasics.sql:218: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+GRANT SELECT ON TABLE foo TO u1;
+GRANT
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+--------
+
+(1 row)
+
+REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SELECT g(1);
+ g
+---
+ 1
+(1 row)
+
+SELECT count(g(x)) FROM foo;
+ count
+-------
+ 10
+(1 row)
+
+SET ROLE u2;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:227: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:228: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+GRANT EXECUTE ON FUNCTION g(int) TO u1;
+GRANT
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+----------------------------------------
+ {superuser=X/superuser,u1=X/superuser}
+(1 row)
+
+SET ROLE u2;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:233: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:234: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+REVOKE EXECUTE ON FUNCTION g(int) FROM u1;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SET ROLE u2;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:239: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:240: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+RESET ROLE;
+RESET
+DROP FUNCTION g(int);
+DROP FUNCTION
+REVOKE SELECT ON TABLE foo FROM u1;
+REVOKE
+DROP ROLE u1;
+DROP ROLE
+DROP ROLE u2;
+DROP ROLE
+DROP ROLE superuser;
+DROP ROLE
+-- DCL, GRANT/REVOKE, WITH GRANT OPTION
+CREATE ROLE superuser SUPERUSER;
+CREATE ROLE
+SET ROLE superuser;
+SET
+CREATE ROLE u1;
+psql:/tmp/TestUDF_TestUDFBasics.sql:253: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+CREATE ROLE u2;
+psql:/tmp/TestUDF_TestUDFBasics.sql:254: NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE ROLE
+GRANT SELECT ON TABLE foo TO PUBLIC;
+GRANT
+CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
+CREATE FUNCTION
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+--------
+
+(1 row)
+
+REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SELECT g(1);
+ g
+---
+ 1
+(1 row)
+
+SELECT count(g(x)) FROM foo;
+ count
+-------
+ 10
+(1 row)
+
+SET ROLE u2;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:263: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:264: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+GRANT ALL ON FUNCTION g(int) TO u1 WITH GRANT OPTION;
+GRANT
+SET ROLE u1;
+SET
+GRANT ALL ON FUNCTION g(int) TO u2;
+psql:/tmp/TestUDF_TestUDFBasics.sql:268: ERROR: function g(integer) does not exist
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-----------------------------------------
+ {superuser=X/superuser,u1=X*/superuser}
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:271: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:272: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE u2;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:274: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:275: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE superuser;
+SET
+REVOKE ALL ON FUNCTION g(int) FROM u1 CASCADE;
+REVOKE
+SELECT proacl FROM pg_proc where proname = 'g';
+ proacl
+-------------------------
+ {superuser=X/superuser}
+(1 row)
+
+SET ROLE u1;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:280: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:281: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+SET ROLE u2;
+SET
+SELECT g(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:283: ERROR: function g(integer) does not exist
+LINE 1: SELECT g(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+SELECT count(g(x)) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:284: ERROR: relation "foo" does not exist
+LINE 1: SELECT count(g(x)) FROM foo;
+ ^
+RESET ROLE;
+RESET
+DROP FUNCTION g(int);
+DROP FUNCTION
+REVOKE SELECT ON TABLE foo FROM PUBLIC;
+REVOKE
+DROP ROLE u1;
+DROP ROLE
+DROP ROLE u2;
+DROP ROLE
+DROP ROLE superuser;
+DROP ROLE
+-- DML, CaseExpr
+SELECT CASE WHEN x % 2 = 0 THEN f(x) ELSE 0 END FROM foo ORDER BY x;
+ case
+------
+ 0
+ 2
+ 0
+ 4
+ 0
+ 6
+ 0
+ 8
+ 0
+ 10
+(10 rows)
+
+-- DML, OpExpr
+SELECT f(x) + f(x) FROM foo ORDER BY x;
+ ?column?
+----------
+ 2
+ 4
+ 6
+ 8
+ 10
+ 12
+ 14
+ 16
+ 18
+ 20
+(10 rows)
+
+SELECT f(x) + f(x) + f(x) FROM foo ORDER BY x;
+ ?column?
+----------
+ 3
+ 6
+ 9
+ 12
+ 15
+ 18
+ 21
+ 24
+ 27
+ 30
+(10 rows)
+
+SELECT f(x) + f(x) - f(x) FROM foo ORDER BY x;
+ ?column?
+----------
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- DML, FuncExpr
+CREATE FUNCTION g(x INT) RETURNS INT AS $$
+BEGIN
+RETURN x;
+END
+$$ LANGUAGE PLPGSQL;
+CREATE FUNCTION
+SELECT g(f(x)) FROM foo ORDER BY x;
+ g
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DML, BoolExpr
+SELECT x % 2 = 0 AND f(x) % 2 = 1 FROM foo ORDER BY x;
+ ?column?
+----------
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+(10 rows)
+
+-- DML, DistinctExpr
+SELECT x IS DISTINCT FROM f(x) from foo ORDER BY x;
+ ?column?
+----------
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+(10 rows)
+
+-- DML, PercentileExpr
+SELECT MEDIAN(f(x)) FROM foo;
+ median
+--------
+ 5.5
+(1 row)
+
+-- DML, Complex Expression
+CREATE FUNCTION g(x INT) RETURNS INT AS $$
+BEGIN
+RETURN x;
+END
+$$ LANGUAGE PLPGSQL;
+CREATE FUNCTION
+SELECT CASE
+ WHEN x % 2 = 0 THEN g(g(x)) + g(g(x))
+ WHEN f(x) % 2 = 1 THEN g(g(x)) - g(g(x))
+ END FROM foo ORDER BY x;
+ case
+------
+ 0
+ 4
+ 0
+ 8
+ 0
+ 12
+ 0
+ 16
+ 0
+ 20
+(10 rows)
+
+DROP FUNCTION g(int);
+DROP FUNCTION
+-- DML, Qual
+SELECT x FROM foo WHERE f(x) % 2 = 0 ORDER BY x;
+ x
+----
+ 2
+ 4
+ 6
+ 8
+ 10
+(5 rows)
+
+-- DML, FROM
+SELECT * FROM f(5);
+ f
+---
+ 5
+(1 row)
+
+-- DML, Grouping
+SELECT DISTINCT f(x) FROM foo ORDER BY f(x);
+ f
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+SELECT f(x) FROM foo GROUP BY f(x) ORDER BY f(x);
+ f
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- DML, Join
+SELECT a.x FROM foo a, foo b WHERE f(a.x) = f(b.x) ORDER BY x;
+ x
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+SELECT a.x FROM foo a JOIN foo b ON f(a.x) = f(b.x) ORDER BY x;
+ x
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- DML, Windowing
+SELECT avg(x) OVER (PARTITION BY f(x)) FROM foo ORDER BY x;
+ avg
+-----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- DML, CTE
+WITH t AS (SELECT x from foo)
+ SELECT f(x) from t ORDER BY x;
+ f
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- DML, InitPlan
+SELECT UNNEST(ARRAY(SELECT x FROM foo)) ORDER BY 1;
+ unnest
+--------
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+SELECT UNNEST(ARRAY(SELECT f(1)));
+ unnest
+--------
+ 1
+(1 row)
+
+-- PROPERTIES, VOLATILITY, IMMUTABLE
+CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+SELECT COUNT(DISTINCT(g())) > 1 FROM foo;
+ ?column?
+----------
+ f
+(1 row)
+
+DROP FUNCTION g();
+DROP FUNCTION
+-- PROPERTIES, VOLATILITY, STABLE
+CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL STABLE;
+CREATE FUNCTION
+SELECT COUNT(DISTINCT(g())) > 1 FROM foo;
+ ?column?
+----------
+ f
+(1 row)
+
+DROP FUNCTION g();
+DROP FUNCTION
+-- PROPERTIES, VOLATILITY, VOLATILE
+CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL VOLATILE;
+CREATE FUNCTION
+SELECT COUNT(DISTINCT(g())) > 1 FROM foo;
+ ?column?
+----------
+ t
+(1 row)
+
+DROP FUNCTION g();
+DROP FUNCTION
+-----------------
+-- NEGATIVE TESTS
+-----------------
+SELECT h(1);
+psql:/tmp/TestUDF_TestUDFBasics.sql:405: ERROR: function h(integer) does not exist
+LINE 1: SELECT h(1);
+ ^
+HINT: No function matches the given name and argument types. You may need to add explicit type casts.
+-- DML, InitPlan
+SELECT UNNEST(ARRAY(SELECT f(x) from foo));
+ unnest
+--------
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- LANGUAGES not yet supported
+-- CREATE LANGUAGE plr;
+-- CREATE LANGUAGE plpython;
+-- CREATE LANGUAGE pljava;
+-- CREATE LANGUAGE plperl;
+-- NESTED FUNCTION
+CREATE FUNCTION inner(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
+CREATE FUNCTION
+CREATE FUNCTION outer(x INT) RETURNS INT AS $$
+BEGIN
+RETURN inner(x);
+END
+$$ LANGUAGE PLPGSQL;
+CREATE FUNCTION
+SELECT outer(0);
+ outer
+-------
+ 1
+(1 row)
+
+SELECT outer(0) FROM foo;
+psql:/tmp/TestUDF_TestUDFBasics.sql:423: ERROR: function inner(integer) does not exist (seg2 localhost:40000 pid=76055)
+DETAIL: PL/pgSQL function "outer" line 2 at return
+DROP FUNCTION outer(int);
+DROP FUNCTION
+DROP FUNCTION inner(int);
+DROP FUNCTION
+-- TEARDOWN
+DROP TABLE foo;
+DROP TABLE
+-- HAWQ-510
+drop table if exists testEntryDB;
+psql:/tmp/TestUDF_TestUDFBasics.sql:435: NOTICE: table "testentrydb" does not exist, skipping
+DROP TABLE
+create table testEntryDB(key int, value int) distributed randomly;
+CREATE TABLE
+insert into testEntryDB values(1, 0);
+INSERT 0 1
+select t2.key, t2.value
+from (select key, value from testEntryDB where value = 0) as t1,
+ (select generate_series(1,2)::int as key, 0::int as value) as t2
+where t1.value=t2.value;
+ key | value
+-----+-------
+ 1 | 0
+ 2 | 0
+(2 rows)
+
+drop table testEntryDB;
+DROP TABLE
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/ans/function_creation.ans.source
----------------------------------------------------------------------
diff --git a/src/test/feature/UDF/ans/function_creation.ans.source b/src/test/feature/UDF/ans/function_creation.ans.source
new file mode 100755
index 0000000..8524e79
--- /dev/null
+++ b/src/test/feature/UDF/ans/function_creation.ans.source
@@ -0,0 +1,96 @@
+-- start_ignore
+SET SEARCH_PATH=TestUDF_TestUDFCreation;
+SET
+-- end_ignore
+--
+-- CREATE_FUNCTION
+--
+CREATE FUNCTION widget_in(cstring)
+ RETURNS widget
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C IMMUTABLE STRICT;
+psql:/tmp/TestUDF_TestUDFCreation.sql:11: NOTICE: type "widget" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION
+CREATE FUNCTION widget_out(widget)
+ RETURNS cstring
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C IMMUTABLE STRICT;
+psql:/tmp/TestUDF_TestUDFCreation.sql:16: NOTICE: argument type widget is only a shell
+CREATE FUNCTION
+CREATE FUNCTION int44in(cstring)
+ RETURNS city_budget
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C IMMUTABLE STRICT;
+psql:/tmp/TestUDF_TestUDFCreation.sql:21: NOTICE: type "city_budget" is not yet defined
+DETAIL: Creating a shell type definition.
+CREATE FUNCTION
+CREATE FUNCTION int44out(city_budget)
+ RETURNS cstring
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C IMMUTABLE STRICT;
+psql:/tmp/TestUDF_TestUDFCreation.sql:26: NOTICE: argument type city_budget is only a shell
+CREATE FUNCTION
+CREATE FUNCTION check_primary_key ()
+ RETURNS trigger
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C;
+CREATE FUNCTION
+CREATE FUNCTION check_foreign_key ()
+ RETURNS trigger
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C;
+CREATE FUNCTION
+CREATE FUNCTION autoinc ()
+ RETURNS trigger
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C;
+CREATE FUNCTION
+CREATE FUNCTION funny_dup17 ()
+ RETURNS trigger
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C;
+CREATE FUNCTION
+CREATE FUNCTION ttdummy ()
+ RETURNS trigger
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C;
+CREATE FUNCTION
+CREATE FUNCTION set_ttdummy (int4)
+ RETURNS int4
+ AS '@SHARE_LIBRARY_PATH@'
+ LANGUAGE C STRICT;
+CREATE FUNCTION
+-- Things that shouldn't work:
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'SELECT ''not an integer'';';
+psql:/tmp/TestUDF_TestUDFCreation.sql:61: ERROR: return type mismatch in function declared to return integer
+DETAIL: Actual return type is unknown.
+CONTEXT: SQL function "test1"
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'not even SQL';
+psql:/tmp/TestUDF_TestUDFCreation.sql:64: ERROR: syntax error at or near "not"
+LINE 2: AS 'not even SQL';
+ ^
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'SELECT 1, 2, 3;';
+psql:/tmp/TestUDF_TestUDFCreation.sql:67: ERROR: return type mismatch in function declared to return integer
+DETAIL: Final SELECT must return exactly one column.
+CONTEXT: SQL function "test1"
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'SELECT $2;';
+psql:/tmp/TestUDF_TestUDFCreation.sql:70: ERROR: there is no parameter $2
+LINE 2: AS 'SELECT $2;';
+ ^
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
+ AS 'a', 'b';
+psql:/tmp/TestUDF_TestUDFCreation.sql:73: ERROR: only one AS item needed for language "sql"
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C
+ AS 'nosuchfile';
+psql:/tmp/TestUDF_TestUDFCreation.sql:76: ERROR: could not access file "nosuchfile": No such file or directory
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE C
+ AS '@SHARE_LIBRARY_PATH@', 'nosuchsymbol';
+psql:/tmp/TestUDF_TestUDFCreation.sql:79: ERROR: could not find function "nosuchsymbol" in file "@SHARE_LIBRARY_PATH@"
+CREATE FUNCTION test1 (int) RETURNS int LANGUAGE internal
+ AS 'nosuch';
+psql:/tmp/TestUDF_TestUDFCreation.sql:82: ERROR: there is no built-in function named "nosuch"
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/ans/function_extension.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/UDF/ans/function_extension.ans b/src/test/feature/UDF/ans/function_extension.ans
new file mode 100755
index 0000000..e1d14f4
--- /dev/null
+++ b/src/test/feature/UDF/ans/function_extension.ans
@@ -0,0 +1,183 @@
+-- start_ignore
+SET SEARCH_PATH=TestUDF_TestUDFExtension;
+SET
+-- end_ignore
+-- -----------------------------------------------------------------
+-- Test extensions to functions (MPP-16060)
+-- 1. data access indicators
+-- -----------------------------------------------------------------
+-- test prodataaccess
+create function func1(int, int) returns int as
+$$
+ select $1 + $2;
+$$ language sql immutable contains sql;
+CREATE FUNCTION
+-- check prodataaccess column in pg_proc
+select proname, prodataaccess from pg_proc where proname = 'func1';
+ proname | prodataaccess
+---------+---------------
+ func1 | c
+(1 row)
+
+-- check prodataaccess in pg_attribute
+select relname, attname, attlen from pg_class c, pg_attribute
+where attname = 'prodataaccess' and attrelid = c.oid and c.relname = 'pg_proc';
+ relname | attname | attlen
+---------+---------------+--------
+ pg_proc | prodataaccess | 1
+(1 row)
+
+create function func2(a anyelement, b anyelement, flag bool)
+returns anyelement as
+$$
+ select $1 + $2;
+$$ language sql reads sql data;
+CREATE FUNCTION
+-- check prodataaccess column in pg_proc
+select proname, prodataaccess from pg_proc where proname = 'func2';
+ proname | prodataaccess
+---------+---------------
+ func2 | r
+(1 row)
+
+create function func3() returns oid as
+$$
+ select oid from pg_class where relname = 'pg_type';
+$$ language sql modifies sql data volatile;
+CREATE FUNCTION
+-- check prodataaccess column in pg_proc
+select proname, prodataaccess from pg_proc where proname = 'func3';
+ proname | prodataaccess
+---------+---------------
+ func3 | m
+(1 row)
+
+-- check default value of prodataaccess
+drop function func1(int, int);
+DROP FUNCTION
+create function func1(int, int) returns varchar as $$
+declare
+ v_name varchar(20) DEFAULT 'zzzzz';
+begin
+ select relname from pg_class into v_name where oid=$1;
+ return v_name;
+end;
+$$ language plpgsql;
+CREATE FUNCTION
+select proname, proargnames, prodataaccess from pg_proc where proname = 'func1';
+ proname | proargnames | prodataaccess
+---------+-------------+---------------
+ func1 | | n
+(1 row)
+
+create function func4(int, int) returns int as
+$$
+ select $1 + $2;
+$$ language sql;
+CREATE FUNCTION
+-- check prodataaccess column
+select proname, proargnames, prodataaccess from pg_proc where proname = 'func4';
+ proname | proargnames | prodataaccess
+---------+-------------+---------------
+ func4 | | c
+(1 row)
+
+-- change prodataaccess option
+create or replace function func4(int, int) returns int as
+$$
+ select $1 + $2;
+$$ language sql modifies sql data;
+CREATE FUNCTION
+select proname, proargnames, prodataaccess from pg_proc where proname = 'func4';
+ proname | proargnames | prodataaccess
+---------+-------------+---------------
+ func4 | | m
+(1 row)
+
+-- upper case language name
+create or replace function func5(int) returns int as
+$$
+ select $1;
+$$ language "SQL";
+CREATE FUNCTION
+-- check prodataaccess column
+select proname, proargnames, prodataaccess from pg_proc where proname = 'func5';
+ proname | proargnames | prodataaccess
+---------+-------------+---------------
+ func5 | | c
+(1 row)
+
+-- alter function with data access
+alter function func5(int) reads sql data;
+ALTER FUNCTION
+-- check prodataaccess column
+select proname, proargnames, prodataaccess from pg_proc where proname = 'func5';
+ proname | proargnames | prodataaccess
+---------+-------------+---------------
+ func5 | | r
+(1 row)
+
+-- alter function with data access
+alter function func5(int) modifies sql data;
+ALTER FUNCTION
+-- check prodataaccess column
+select proname, proargnames, prodataaccess from pg_proc where proname = 'func5';
+ proname | proargnames | prodataaccess
+---------+-------------+---------------
+ func5 | | m
+(1 row)
+
+-- alter function with data access
+alter function func5(int) no sql;
+psql:/tmp/TestUDF_TestUDFExtension.sql:90: ERROR: conflicting options
+HINT: A SQL function cannot specify NO SQL.
+-- alter function with data access
+alter function func5(int) volatile contains sql;
+ALTER FUNCTION
+alter function func5(int) immutable reads sql data;
+psql:/tmp/TestUDF_TestUDFExtension.sql:95: ERROR: conflicting options
+HINT: IMMUTABLE conflicts with READS SQL DATA.
+alter function func5(int) immutable modifies sql data;
+psql:/tmp/TestUDF_TestUDFExtension.sql:96: ERROR: conflicting options
+HINT: IMMUTABLE conflicts with MODIFIES SQL DATA.
+-- data_access indicators for plpgsql
+drop function func1(int, int);
+DROP FUNCTION
+create or replace function func1(int, int) returns varchar as $$
+declare
+ v_name varchar(20) DEFAULT 'zzzzz';
+begin
+ select relname from pg_class into v_name where oid=$1;
+ return v_name;
+end;
+$$ language plpgsql reads sql data;
+CREATE FUNCTION
+select proname, proargnames, prodataaccess from pg_proc where proname = 'func1';
+ proname | proargnames | prodataaccess
+---------+-------------+---------------
+ func1 | | r
+(1 row)
+
+-- check conflicts
+drop function func1(int, int);
+DROP FUNCTION
+create function func1(int, int) returns int as
+$$
+ select $1 + $2;
+$$ language sql immutable no sql;
+psql:/tmp/TestUDF_TestUDFExtension.sql:116: ERROR: conflicting options
+HINT: A SQL function cannot specify NO SQL.
+create function func1(int, int) returns int as
+$$
+ select $1 + $2;
+$$ language sql immutable reads sql data;
+psql:/tmp/TestUDF_TestUDFExtension.sql:121: ERROR: conflicting options
+HINT: IMMUTABLE conflicts with READS SQL DATA.
+drop function func2(anyelement, anyelement, bool);
+DROP FUNCTION
+drop function func3();
+DROP FUNCTION
+drop function func4(int, int);
+DROP FUNCTION
+drop function func5(int);
+DROP FUNCTION
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/ans/function_set_returning.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/UDF/ans/function_set_returning.ans b/src/test/feature/UDF/ans/function_set_returning.ans
new file mode 100755
index 0000000..4544a2d
--- /dev/null
+++ b/src/test/feature/UDF/ans/function_set_returning.ans
@@ -0,0 +1,287 @@
+-- start_ignore
+SET SEARCH_PATH=TestUDF_TestUDFSetReturning;
+SET
+-- end_ignore
+DROP LANGUAGE IF EXISTS plpythonu CASCADE;
+DROP LANGUAGE
+CREATE LANGUAGE plpythonu;
+CREATE LANGUAGE
+CREATE TABLE foo2(fooid int, f2 int);
+CREATE TABLE
+INSERT INTO foo2 VALUES(1, 11);
+INSERT 0 1
+INSERT INTO foo2 VALUES(2, 22);
+INSERT 0 1
+INSERT INTO foo2 VALUES(1, 111);
+INSERT 0 1
+CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
+CREATE FUNCTION
+select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
+ fooid | f2
+-------+-----
+ 1 | 11
+ 1 | 111
+(2 rows)
+
+CREATE TABLE foo (fooid int, foosubid int, fooname text);
+CREATE TABLE
+INSERT INTO foo VALUES(1,1,'Joe');
+INSERT 0 1
+INSERT INTO foo VALUES(1,2,'Ed');
+INSERT 0 1
+INSERT INTO foo VALUES(2,1,'Mary');
+INSERT 0 1
+CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
+CREATE FUNCTION
+SELECT * FROM getfoo(1) AS t1;
+ t1
+----
+ 1
+ 1
+(2 rows)
+
+CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+CREATE VIEW
+SELECT * FROM vw_getfoo;
+ getfoo
+--------
+ 1
+ 1
+(2 rows)
+
+DROP VIEW vw_getfoo;
+DROP VIEW
+DROP FUNCTION getfoo(int);
+DROP FUNCTION
+DROP FUNCTION foot(int);
+DROP FUNCTION
+DROP TABLE foo2;
+DROP TABLE
+DROP TABLE foo;
+DROP TABLE
+-- setof as a paramater --
+CREATE TYPE numtype as (i int, j int);
+CREATE TYPE
+CREATE FUNCTION g_numtype(x setof numtype) RETURNS setof numtype AS $$ select $1; $$ LANGUAGE SQL;
+CREATE FUNCTION
+DROP FUNCTION g_numtype(x setof numtype);
+DROP FUNCTION
+DROP TYPE numtype;
+DROP TYPE
+--
+-- Set functions samples from Madlib
+--
+create function combination(s text) returns setof text[] as $$
+x = s.split(',')
+
+def subset(myset, N):
+ left = []
+ right = []
+ for i in range(0, len(myset)):
+ if ((1 << i) & N) > 0:
+ left.append(myset[i])
+ else:
+ right.append(myset[i])
+ return (', '.join(left), ', '.join(right))
+
+for i in range(1, (1 << len(x)) - 2):
+ yield subset(x, i)
+$$ language plpythonu strict;
+CREATE FUNCTION
+select x[1] || ' => ' || x[2] from combination('a,b,c,d') x;
+ ?column?
+--------------
+ a => b, c, d
+ b => a, c, d
+ a, b => c, d
+ c => a, b, d
+ a, c => b, d
+ b, c => a, d
+ a, b, c => d
+ d => a, b, c
+ a, d => b, c
+ b, d => a, c
+ a, b, d => c
+ c, d => a, b
+ a, c, d => b
+(13 rows)
+
+CREATE TABLE rules(rule text) distributed by (rule);
+CREATE TABLE
+insert into rules values('a,b,c');
+INSERT 0 1
+insert into rules values('d,e');
+INSERT 0 1
+insert into rules values('f,g,h,i,j');
+INSERT 0 1
+insert into rules values('k,l,m');
+INSERT 0 1
+SELECT rule, combination(rule) from rules order by 1,2;
+ rule | combination
+-----------+--------------------
+ a,b,c | {a,"b, c"}
+ a,b,c | {"a, b",c}
+ a,b,c | {"a, c",b}
+ a,b,c | {b,"a, c"}
+ a,b,c | {c,"a, b"}
+ d,e | {d,e}
+ f,g,h,i,j | {f,"g, h, i, j"}
+ f,g,h,i,j | {"f, g","h, i, j"}
+ f,g,h,i,j | {"f, g, h","i, j"}
+ f,g,h,i,j | {"f, g, h, i",j}
+ f,g,h,i,j | {"f, g, h, j",i}
+ f,g,h,i,j | {"f, g, i","h, j"}
+ f,g,h,i,j | {"f, g, i, j",h}
+ f,g,h,i,j | {"f, g, j","h, i"}
+ f,g,h,i,j | {"f, h","g, i, j"}
+ f,g,h,i,j | {"f, h, i","g, j"}
+ f,g,h,i,j | {"f, h, i, j",g}
+ f,g,h,i,j | {"f, h, j","g, i"}
+ f,g,h,i,j | {"f, i","g, h, j"}
+ f,g,h,i,j | {"f, i, j","g, h"}
+ f,g,h,i,j | {"f, j","g, h, i"}
+ f,g,h,i,j | {g,"f, h, i, j"}
+ f,g,h,i,j | {"g, h","f, i, j"}
+ f,g,h,i,j | {"g, h, i","f, j"}
+ f,g,h,i,j | {"g, h, j","f, i"}
+ f,g,h,i,j | {"g, i","f, h, j"}
+ f,g,h,i,j | {"g, i, j","f, h"}
+ f,g,h,i,j | {"g, j","f, h, i"}
+ f,g,h,i,j | {h,"f, g, i, j"}
+ f,g,h,i,j | {"h, i","f, g, j"}
+ f,g,h,i,j | {"h, i, j","f, g"}
+ f,g,h,i,j | {"h, j","f, g, i"}
+ f,g,h,i,j | {i,"f, g, h, j"}
+ f,g,h,i,j | {"i, j","f, g, h"}
+ f,g,h,i,j | {j,"f, g, h, i"}
+ k,l,m | {k,"l, m"}
+ k,l,m | {"k, l",m}
+ k,l,m | {"k, m",l}
+ k,l,m | {l,"k, m"}
+ k,l,m | {m,"k, l"}
+(40 rows)
+
+DROP TABLE IF EXISTS foo;
+psql:/tmp/TestUDF_TestUDFSetReturning.sql:69: NOTICE: table "foo" does not exist, skipping
+DROP TABLE
+CREATE TABLE foo AS SELECT rule, combination(rule) from rules distributed by (rule);
+SELECT 40
+-- UDT as argument/return type of set returning UDF
+CREATE TYPE r_type as (a int, b text);
+CREATE TYPE
+CREATE FUNCTION f1(x r_type) returns setof text as $$ SELECT $1.b from generate_series(1, $1.a) $$ language sql;
+CREATE FUNCTION
+CREATE FUNCTION f2(x int) returns setof r_type as $$ SELECT i, 'hello'::text from generate_series(1, $1) i $$ language sql;
+CREATE FUNCTION
+CREATE FUNCTION f3(x r_type) returns setof r_type as $$ SELECT $1 from generate_series(1, $1.a) $$ language sql;
+CREATE FUNCTION
+SELECT f1(row(2, 'hello'));
+ f1
+-------
+ hello
+ hello
+(2 rows)
+
+SELECT f2(2);
+ f2
+-----------
+ (1,hello)
+ (2,hello)
+(2 rows)
+
+SELECT f3(row(2,'hello'));
+ f3
+-----------
+ (2,hello)
+ (2,hello)
+(2 rows)
+
+SELECT * FROM f1(row(2,'hello'));
+ f1
+-------
+ hello
+ hello
+(2 rows)
+
+SELECT * FROM f2(2);
+ a | b
+---+-------
+ 1 | hello
+ 2 | hello
+(2 rows)
+
+SELECT * FROM f3(row(2,'hello'));
+ a | b
+---+-------
+ 2 | hello
+ 2 | hello
+(2 rows)
+
+CREATE TABLE t1 as SELECT i from generate_series(1,5) i distributed by (i);
+SELECT 5
+SELECT i, f1(row(i, 'hello')) from t1;
+ i | f1
+---+-------
+ 1 | hello
+ 3 | hello
+ 3 | hello
+ 3 | hello
+ 5 | hello
+ 5 | hello
+ 5 | hello
+ 5 | hello
+ 5 | hello
+ 2 | hello
+ 2 | hello
+ 4 | hello
+ 4 | hello
+ 4 | hello
+ 4 | hello
+(15 rows)
+
+SELECT i, f2(i) from t1;
+ i | f2
+---+-----------
+ 1 | (1,hello)
+ 3 | (1,hello)
+ 3 | (2,hello)
+ 3 | (3,hello)
+ 5 | (1,hello)
+ 5 | (2,hello)
+ 5 | (3,hello)
+ 5 | (4,hello)
+ 5 | (5,hello)
+ 2 | (1,hello)
+ 2 | (2,hello)
+ 4 | (1,hello)
+ 4 | (2,hello)
+ 4 | (3,hello)
+ 4 | (4,hello)
+(15 rows)
+
+SELECT i, f3(row(i,'hello')) from t1;
+ i | f3
+---+-----------
+ 1 | (1,hello)
+ 4 | (4,hello)
+ 4 | (4,hello)
+ 4 | (4,hello)
+ 4 | (4,hello)
+ 3 | (3,hello)
+ 3 | (3,hello)
+ 3 | (3,hello)
+ 5 | (5,hello)
+ 5 | (5,hello)
+ 5 | (5,hello)
+ 5 | (5,hello)
+ 5 | (5,hello)
+ 2 | (2,hello)
+ 2 | (2,hello)
+(15 rows)
+
+CREATE TABLE o1 as SELECT f1(row(i, 'hello')) from t1;
+SELECT 15
+CREATE TABLE o2 as SELECT f2(i) from t1;
+SELECT 15
+CREATE TABLE o3 as SELECT f3(row(i,'hello')) from t1;
+SELECT 15
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/49fd529a/src/test/feature/UDF/lib/Makefile
----------------------------------------------------------------------
diff --git a/src/test/feature/UDF/lib/Makefile b/src/test/feature/UDF/lib/Makefile
new file mode 100755
index 0000000..ccbf99b
--- /dev/null
+++ b/src/test/feature/UDF/lib/Makefile
@@ -0,0 +1,43 @@
+top_builddir = ../../../../..
+include $(top_builddir)/src/Makefile.global
+
+OS = $(shell uname)
+
+CXX = gcc
+CXXFLAGS = -Wall -O1 -g -std=gnu99 -Wmissing-prototypes -Wpointer-arith -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fPIC
+
+CC = gcc
+CFLAGS = -Wall -O1 -g -std=gnu99 -Wmissing-prototypes -Wpointer-arith -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fPIC
+
+CPPFLAGS = -I$(abs_top_srcdir)/src/include
+CPPFLAGS += -I$(abs_top_srcdir)/depends/libhdfs3/build/install$(prefix)/include
+CPPFLAGS += -I$(abs_top_srcdir)/depends/libyarn/build/install$(prefix)/include
+
+LDFLAGS = -L$(libdir)
+LDFLAGS += -L$(abs_top_srcdir)/src/port
+LDFLAGS += -L$(abs_top_builddir)/src/port
+LDFLAGS += -L$(abs_top_srcdir)/depends/libhdfs3/build/install$(prefix)/lib
+LDFLAGS += -L$(abs_top_srcdir)/depends/libyarn/build/install$(prefix)/lib
+
+POSTGRES = $(abs_top_srcdir)/src/backend/postgres
+
+PROG = function.c
+OBJS = function.o
+TARGET = function.so
+
+RM = rm -rf
+
+all: $(OBJS) $(TARGET)
+
+$(OBJS): $(PROG)
+ $(CXX) $(CXXFLAGS) $(CPPFLAGS) -c -o $(OBJS) $(PROG)
+
+$(TARGET):
+ifeq ($(OS),Darwin)
+ $(CXX) $(CXXFLAGS) -bundle $(OBJS) -bundle_loader $(POSTGRES) $(LDFLAGS) -o $@
+else
+ $(CXX) $(CXXFLAGS) -shared $(OBJS) $(LDFLAGS) -Wl,-rpath,'$(abs_top_builddir)/lib' -o $@
+endif
+
+clean:
+ $(RM) *.o