Thread: 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