DROP TABLE foo2;
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);
DROP FUNCTION foot(int);
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;

-- supposed to fail with ERROR
select * from foo2, foot(fooid) z where foo2.f2 = z.f2;

-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid);

-- function in subselect
select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid);

-- nested functions
select * from foot(sin(pi()/2)::int);

DROP TABLE foo;
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
INSERT INTO foo VALUES(1,1,'Joe');
INSERT INTO foo VALUES(1,2,'Ed');
INSERT INTO foo VALUES(2,1,'Mary');

-- sql, proretset = f, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = f, prorettype = c
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- sql, proretset = t, prorettype = c
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;

-- C, proretset = f, prorettype = b
SELECT * FROM dblink_replace('123456789987654321', '99', 'HelloWorld');
DROP VIEW vw_dblink_replace;
CREATE VIEW vw_dblink_replace AS SELECT * FROM dblink_replace('123456789987654321', '99', 'HelloWorld');
SELECT * FROM vw_dblink_replace;

-- C, proretset = t, prorettype = b
SELECT dblink_get_pkey FROM dblink_get_pkey('foo');
DROP VIEW vw_dblink_get_pkey;
CREATE VIEW vw_dblink_get_pkey AS SELECT dblink_get_pkey FROM dblink_get_pkey('foo');
SELECT * FROM vw_dblink_get_pkey;










-- plpgsql
--DROP FUNCTION getfoo(int);
--CREATE OR REPLACE FUNCTION testplpgsql() RETURNS setof int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo; RETURN fooint; END;' LANGUAGE 'plpgsql';