Thread: Set-returning functions only allowed if written in language 'sql'

Hello dear PostgreSQL developers,

I noticed the following strange behaviour with set-returning functions.
If sets are allowed seems to depend on the language the function is
written in, what makes conpletely no sense to me. See the following
functions x() and y(). x() is written in 'sql' and works, y() is written
is plpgsql and fails. Any reasons for this I do not realize?

With best regards,
Daniel Migowski

----------------------------

CREATE FUNCTION x() RETURNS SETOF int4 AS
$$
    SELECT 1
    UNION
    SELECT 2
$$
LANGUAGE 'sql';
SELECT x(); -- fine with two result rows.

CREATE FUNCTION y() RETURNS SETOF int4 AS
$$
BEGIN
    RETURN NEXT 1;
    RETURN NEXT 2;
END
$$
LANGUAGE 'plpgsql';
SELECT y(); -- fails with:
FEHLER: Funktion mit Mengenergebnis in einem Zusammenhang aufgerufen,
der keine Mengenergebnisse verarbeiten kann
SQL Status:0A000
Kontext:PL/pgSQL function "y" line 2 at RETURN NEXT

On Tue, Feb 10, 2009 at 01:04:02AM +0100, Daniel Migowski wrote:
> SELECT y(); -- fails with:

when you return set, you should use:
select * from y();

select function() is additional feature of sql functions.

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Hello

this limit will be removed at 8.4

in older version you have to use table notation like

select * from srf()

regards
Pavel Stehule

2009/2/10 Daniel Migowski <dmigowski@ikoffice.de>:
> Hello dear PostgreSQL developers,
>
> I noticed the following strange behaviour with set-returning functions. If
> sets are allowed seems to depend on the language the function is written in,
> what makes conpletely no sense to me. See the following functions x() and
> y(). x() is written in 'sql' and works, y() is written is plpgsql and fails.
> Any reasons for this I do not realize?
>
> With best regards,
> Daniel Migowski
>
> ----------------------------
>
> CREATE FUNCTION x() RETURNS SETOF int4 AS
> $$
>   SELECT 1
>   UNION
>   SELECT 2
> $$
> LANGUAGE 'sql';
> SELECT x(); -- fine with two result rows.
>
> CREATE FUNCTION y() RETURNS SETOF int4 AS
> $$
> BEGIN
>   RETURN NEXT 1;
>   RETURN NEXT 2;
> END
> $$
> LANGUAGE 'plpgsql';
> SELECT y(); -- fails with:
> FEHLER: Funktion mit Mengenergebnis in einem Zusammenhang aufgerufen, der
> keine Mengenergebnisse verarbeiten kann
> SQL Status:0A000
> Kontext:PL/pgSQL function "y" line 2 at RETURN NEXT
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Pavel Stehule wrote:

> Hello
>
> this limit will be removed at 8.4
>
> in older version you have to use table notation like
>
> select * from srf()
>
> regards
> Pavel Stehule

hi,

that's funny. the 8.3 documentation i read said that in the
future you'd probably have to use "select * from srf()"
and *not* "select srf()" since that method is deprecated:

http://www.postgresql.org/docs/8.3/interactive/xfunc-sql.html#AEN40353

  "Currently, functions returning sets can also be called in
  the select list of a query. For each row that the query
  generates by itself, the function returning set is invoked,
  and an output row is generated for each element of the
  function's result set. Note, however, that this capability
  is deprecated and might be removed in future releases."

so i use "select * from srf()" all the time
(it works better for multi-column sets anyway).

does this mean that "select srf()" is no longer deprecated?

cheers,
raf

> 2009/2/10 Daniel Migowski <dmigowski@ikoffice.de>:
> > Hello dear PostgreSQL developers,
> >
> > I noticed the following strange behaviour with set-returning functions. If
> > sets are allowed seems to depend on the language the function is written in,
> > what makes conpletely no sense to me. See the following functions x() and
> > y(). x() is written in 'sql' and works, y() is written is plpgsql and fails.
> > Any reasons for this I do not realize?
> >
> > With best regards,
> > Daniel Migowski
> >
> > ----------------------------
> >
> > CREATE FUNCTION x() RETURNS SETOF int4 AS
> > $$
> >   SELECT 1
> >   UNION
> >   SELECT 2
> > $$
> > LANGUAGE 'sql';
> > SELECT x(); -- fine with two result rows.
> >
> > CREATE FUNCTION y() RETURNS SETOF int4 AS
> > $$
> > BEGIN
> >   RETURN NEXT 1;
> >   RETURN NEXT 2;
> > END
> > $$
> > LANGUAGE 'plpgsql';
> > SELECT y(); -- fails with:
> > FEHLER: Funktion mit Mengenergebnis in einem Zusammenhang aufgerufen, der
> > keine Mengenergebnisse verarbeiten kann
> > SQL Status:0A000
> > Kontext:PL/pgSQL function "y" line 2 at RETURN NEXT

raf <raf@raf.org> writes:
> does this mean that "select srf()" is no longer deprecated?

Well, it's got various issues --- in particular it's not real clear what
should happen if there's more than one SRF in a select list.  It's
unlikely to go away though, especially not before we have an adequate
replacement.

            regards, tom lane