set returning function
An issue came up on irc. How come that this work:
SELECT generate_series(0,1);
but
SELECT foo(0,1);
does not, where foo is my own set returning function, like this example:
CREATE FUNCTION foo(a int, b int)
RETURNS setof int
AS 'BEGIN RETURN NEXT a;
RETURN NEXT b;
RETURN;
END'
LANGUAGE plpgsql;
As far as I can tell the generate_series() example is not supposed to
work either.
Kris Jurka showed me another example that do work in the same way as
generate_series:
CREATE FUNCTION bar(int, int)
RETURNS setof int
AS 'SELECT $1 UNION ALL SELECT $2;'
LANGUAGE sql;
So it seems that it's not just the type that decide how the function can
be used, it's also the language the function is defined in.
Bug?
--
/Dennis Bj�rklund
On Mon, 9 May 2005, Dennis Bjorklund wrote:
[ I can call sql or C SRFs without FROM, but not plpgsql.]
Trying this in pltcl (while knowing nothing about tcl and the docs not
mentioning any srf support) shows:
CREATE FUNCTION tclset() RETURNS SETOF int AS 'return 0' LANGUAGE pltcl;
SELECT * FROM tclset(); -- works
SELECT tclset(); -- goes into an infinite loop
Kris Jurka
Dennis Bjorklund <db@zigo.dhs.org> writes:
So it seems that it's not just the type that decide how the function can
be used, it's also the language the function is defined in.
Yup.
Bug?
Unimplemented feature.
regards, tom lane
On Mon, 9 May 2005, Tom Lane wrote:
Bug?
Unimplemented feature.
Is
SELECT 42, srf();
the same as
SELECT 42, * FROM srf();
?
In my view the first version is an error. It's not like you can put a
normal table in the select list, so why can we put a set returning
function there? Ie, is it really a feature?
--
/Dennis Bj�rklund
Dennis Bjorklund <db@zigo.dhs.org> writes:
Is
SELECT 42, srf();
the same as
SELECT 42, * FROM srf();
?
No.
In my view the first version is an error. It's not like you can put a
normal table in the select list, so why can we put a set returning
function there? Ie, is it really a feature?
To some extent it's a hangover from PostQUEL ... but there are things
you can do with it that you can't currently do with SRFs in FROM.
See for instance the "listchildren" example in the manual:
http://www.postgresql.org/docs/8.0/static/xfunc-sql.html#AEN29503
regards, tom lane