Thread: Set-returning function syntax
For those who want to play on the bleeding edge of CVS, can someone provide the syntax for the recently-checked-in set-returningfunctions? I've got it figured out when I'm returning a many rows of single column, but not for many rows ofseveral columns. If someone can do this, and no one has put together docs on this feature, I'll volunteer to write this up. Thanks! - J. -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant
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';
Does your SRF function allow to return a setof composite data type using C function? If so, how can I write such that C function? I couldn't find any example or explanation so far. You referred dblink, but in my understanding it does not have any function that returns a setof composite data type. -- Tatsuo Ishii > 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
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> Does your SRF function allow to return a setof composite data type
> using C function? If so, how can I write such that C function?
The "setof" part is documented in src/backend/utils/fmgr/README.
There's no good documentation for returning tuples at the moment,
but basically you return a pointer to a TupleTableSlot. (Re-use
the same slot on each call to avoid memory leakage.) There's an
example in src/backend/executor/functions.c --- look at the uses
of funcSlot.
One reason this isn't documented is that it's really ugly. It might
be a good idea to change it before we start having lots of user-written
code that depends on it ...
regards, tom lane
Tatsuo Ishii wrote: > Does your SRF function allow to return a setof composite data type > using C function? If so, how can I write such that C function? I > couldn't find any example or explanation so far. You referred dblink, > but in my understanding it does not have any function that returns a > setof composite data type. > I haven't written a C function yet that returns a composite type. You are correct that dblink does not have an example which returns composite type, because that wasn't even possible when I wrote the dblink code ;-) At least initially, a C function returning a composite type will have to do alot of dirty work -- i.e. something like: - manually form a tuple based on the return type relation attributes - save the tuple in a tuple table slot - return a pointer to the slot as a datum I don't know what other complications may be lurking, but I will try to get a working example sometime this coming week and post it to HACKERS. Joe
Tatsuo Ishii wrote: > Does your SRF function allow to return a setof composite data type > using C function? If so, how can I write such that C function? I Just to follow-up, here's a quick look at what works and what doesn't, at least using my test script. SELECT * FROM myfunc(); Language RetSet RetType Status --------------- ------- ------- --------------------- C t b OK C t c Not tested C f b OK C f c Not tested SQL t b OK SQL t c OK SQL f b OK SQL f c OK PL/pgSQL t b No retset support PL/pgSQL t c No retset support PL/pgSQL f b OK PL/pgSQL f c OK ----------------------------------------------------- RetSet: t = function declared to return setof something RetType: b = base type; c = composite type Same cases work when a view is defined as SELECT * FROM myfunc(). Joe
> The "setof" part is documented in src/backend/utils/fmgr/README. > There's no good documentation for returning tuples at the moment, > but basically you return a pointer to a TupleTableSlot. (Re-use > the same slot on each call to avoid memory leakage.) There's an > example in src/backend/executor/functions.c --- look at the uses > of funcSlot. That was almost same as I guessed:-) > One reason this isn't documented is that it's really ugly. It might > be a good idea to change it before we start having lots of user-written > code that depends on it ... Sounds like a good idea. -- Tatsuo Ishii