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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Mon, 9 May 2005, Dennis Bjorklund wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Dennis Bjorklund <db (AT) zigo (DOT) dhs.org> writes:
Yup.

Unimplemented feature.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Mon, 9 May 2005, Tom Lane wrote:

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Dennis Bjorklund <db (AT) zigo (DOT) dhs.org> writes:
No.

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/s....html#AEN29503

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match