Re: Set-returning function syntax - Mailing list pgsql-hackers

Joel Burton wrote:
 > For those who want to play on the bleeding edge of CVS, can someone
 > provide the syntax for the recently-checked-in set-returning
 > functions? I've got it figured out when I'm returning a many rows of
 > single column, but not for many rows of several columns.

For multiple columns, you need a composite data type defined --
basically you need to create a table, even if it is an unused shell,
which has the column names and data types of the returned tuple. See
below for more.

 >
 > If someone can do this, and no one has put together docs on this
 > feature, I'll volunteer to write this up.

I hadn't gotten to the docs yet, but if you wanted to write something up
that would be great! :) I'll certainly help too.

Attached is the script I've been using to test as I go. It shows the
usage of SRFs in a variety of situations (note that the C function tests
require contrib/dblink installed). There's also a description in one of
my earlier posts. Here is a recap, edited to the latest reality:

How it currently works:
-----------------------
1. The SRF may be either marked as returning a set or not. A function
not marked as returning a set simply produces one row.

2. The SRF may either return a base data type (e.g. TEXT) or a composite
data type (e.g. pg_class). If the function returns a base data type, the
single result column is named for the function. If the function returns
a composite type, the result columns get the same names as the
individual attributes of the type.

3. The SRF may be aliased in the FROM clause, but it also be left
unaliased. If a function is used in the FROM clause with no alias, the
function name is used as the relation name.

Hope that's a start.

Thanks,

Joe
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
FROMfoo; RETURN fooint; END;' LANGUAGE 'plpgsql';