Thread: calling a function that takes a row type and returns a set of rows

calling a function that takes a row type and returns a set of rows

From

"Robert Haas"

Date:

10 October 2008, 19:22:20

So, say I have something like this - the actual example is something a
bit more useful:

CREATE TABLE foo (a integer, b integer);
INSERT INTO foo VALUES (1, 1);   -- must have some data to generate the failure

CREATE FUNCTION bar (foo) RETURNS SETOF foo AS $$
DECLARE
    f foo;
BEGIN
    f.a := 1;
    RETURN NEXT f;
    f.a := 2;
    RETURN NEXT f;
END
$$ LANGUAGE plpgsql;

I can't find any legal way of calling this function.

SELECT bar(f) FROM foo f;
ERROR:  set-valued function called in context that cannot accept a set

SELECT * FROM foo f, bar(f);
ERROR:  function expression in FROM may not refer to other relations
of same query level

Any help appreciated.

Thanks,

...Robert

Re: calling a function that takes a row type and returns a set of rows

From

"Pavel Stehule"

Date:

10 October 2008, 20:01:12

Hello

PostgreSQL doesn't support pipe functions, so you cannot do what you
wont.  But you should to use SQL SETOF functions, that should be
called in normal context. I dislike this feature, but it should be
useful for you,

try:

create or replace function bar1(foo)
returns setof foo as $$
  select 1, $1.b
  union all
  select 2, $1.b;
$$ language sql;

postgres=# select (bar1(foo)).* from foo;
 a | b
---+---
 1 | 1
 2 | 1
(2 rows)

I thing, so much better and cleaner version is using explicit or
implicit cursor in function

-- implicit cursor
create or replace function bar() returns setof foo as $$
declare r record;
begin
  for r in select * from foo loop
    r.a := 1;
    return next r;
    r.a := 2;
    return next r;
  end loop;
  return;
end;
$$ language plpgsql;

postgres=# select * from bar();
 a | b
---+---
 1 | 1
 2 | 1
(2 rows)

-- using explicit cursor (it's more complicated variant, and I thing,
so it's better don't use it)
create or replace function bar(c refcursor) returns setof foo as $$
declare r record;
begin
  loop
    fetch c into r;
    exit when not found;
    r.a := 1;
    return next r;
    r.a := 2;
    return next r;
  end loop;
  return;
end;
$$ language plpgsql;

begin;
declare x cursor for select * from foo;
select * from bar('x'::refcursor);
commit;

postgres=# declare x cursor for select * from foo;
DECLARE CURSOR
postgres=# select * from bar('x'::refcursor);
 a | b
---+---
 1 | 1
 2 | 1
(2 rows)

postgres=# commit;
COMMIT

Regards
Pavel Stehule


2008/10/10 Robert Haas <robertmhaas@gmail.com>:
> So, say I have something like this - the actual example is something a
> bit more useful:
>
> CREATE TABLE foo (a integer, b integer);
> INSERT INTO foo VALUES (1, 1);   -- must have some data to generate the failure
>
> CREATE FUNCTION bar (foo) RETURNS SETOF foo AS $$
> DECLARE
>    f foo;
> BEGIN
>    f.a := 1;
>    RETURN NEXT f;
>    f.a := 2;
>    RETURN NEXT f;
> END
> $$ LANGUAGE plpgsql;
>
> I can't find any legal way of calling this function.
>
> SELECT bar(f) FROM foo f;
> ERROR:  set-valued function called in context that cannot accept a set
>
> SELECT * FROM foo f, bar(f);
> ERROR:  function expression in FROM may not refer to other relations
> of same query level
>
> Any help appreciated.
>
> Thanks,
>
> ...Robert
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: calling a function that takes a row type and returns a set of rows

From

"Robert Haas"

Date:

10 October 2008, 20:08:40

Hmm, the implicit cursor approach approach won't work for me because I
want to be able to call the function on an arbitrary slice of the rows
in the table, but the explicit cursor approach looks like it might
work.  I'll give that a try, thanks.

...Robert

On Fri, Oct 10, 2008 at 4:01 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
> PostgreSQL doesn't support pipe functions, so you cannot do what you
> wont.  But you should to use SQL SETOF functions, that should be
> called in normal context. I dislike this feature, but it should be
> useful for you,
>
> try:
>
> create or replace function bar1(foo)
> returns setof foo as $$
>  select 1, $1.b
>  union all
>  select 2, $1.b;
> $$ language sql;
>
> postgres=# select (bar1(foo)).* from foo;
>  a | b
> ---+---
>  1 | 1
>  2 | 1
> (2 rows)
>
> I thing, so much better and cleaner version is using explicit or
> implicit cursor in function
>
> -- implicit cursor
> create or replace function bar() returns setof foo as $$
> declare r record;
> begin
>  for r in select * from foo loop
>    r.a := 1;
>    return next r;
>    r.a := 2;
>    return next r;
>  end loop;
>  return;
> end;
> $$ language plpgsql;
>
> postgres=# select * from bar();
>  a | b
> ---+---
>  1 | 1
>  2 | 1
> (2 rows)
>
> -- using explicit cursor (it's more complicated variant, and I thing,
> so it's better don't use it)
> create or replace function bar(c refcursor) returns setof foo as $$
> declare r record;
> begin
>  loop
>    fetch c into r;
>    exit when not found;
>    r.a := 1;
>    return next r;
>    r.a := 2;
>    return next r;
>  end loop;
>  return;
> end;
> $$ language plpgsql;
>
> begin;
> declare x cursor for select * from foo;
> select * from bar('x'::refcursor);
> commit;
>
> postgres=# declare x cursor for select * from foo;
> DECLARE CURSOR
> postgres=# select * from bar('x'::refcursor);
>  a | b
> ---+---
>  1 | 1
>  2 | 1
> (2 rows)
>
> postgres=# commit;
> COMMIT
>
> Regards
> Pavel Stehule
>
>
> 2008/10/10 Robert Haas <robertmhaas@gmail.com>:
>> So, say I have something like this - the actual example is something a
>> bit more useful:
>>
>> CREATE TABLE foo (a integer, b integer);
>> INSERT INTO foo VALUES (1, 1);   -- must have some data to generate the failure
>>
>> CREATE FUNCTION bar (foo) RETURNS SETOF foo AS $$
>> DECLARE
>>    f foo;
>> BEGIN
>>    f.a := 1;
>>    RETURN NEXT f;
>>    f.a := 2;
>>    RETURN NEXT f;
>> END
>> $$ LANGUAGE plpgsql;
>>
>> I can't find any legal way of calling this function.
>>
>> SELECT bar(f) FROM foo f;
>> ERROR:  set-valued function called in context that cannot accept a set
>>
>> SELECT * FROM foo f, bar(f);
>> ERROR:  function expression in FROM may not refer to other relations
>> of same query level
>>
>> Any help appreciated.
>>
>> Thanks,
>>
>> ...Robert
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>

Re: calling a function that takes a row type and returns a set of rows

From

Dimitri Fontaine

Date:

10 October 2008, 20:39:46

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Le 10 oct. 08 à 21:22, Robert Haas a écrit :
> I can't find any legal way of calling this function.
>
> SELECT bar(f) FROM foo f;
> ERROR:  set-valued function called in context that cannot accept a set
>
> SELECT * FROM foo f, bar(f);
> ERROR:  function expression in FROM may not refer to other relations
> of same query level
>
> Any help appreciated.


You need LATERAL support for this:
   SELECT * FROM foo f LATERAL bar(f);

I'm not sure about the syntax, but LATERAL is a standard JOIN type
wherein upper "nodes" are visible.
- --
dim

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAkjvvYMACgkQlBXRlnbh1blatgCgnaDoSY2RGzv224QWqA8OYEjx
fbMAoK31dHoFjOVRdomvhl/qilndRZJ5
=3xjL
-----END PGP SIGNATURE-----

Re: calling a function that takes a row type and returns a set of rows

From

"Robert Haas"

Date:

10 October 2008, 20:44:01

> You need LATERAL support for this:
>  SELECT * FROM foo f LATERAL bar(f);
>
> I'm not sure about the syntax, but LATERAL is a standard JOIN type wherein
> upper "nodes" are visible.

That would be really nice.  Then you could presumably also do:

SELECT f.id, f.name, f.apple, f.banana, bar.apple AS bar_apple,
bar.banana AS bar_banana FROM foo f LATERAL bar(f);

...which I frequently wish to do, and can't.

...Robert

Re: calling a function that takes a row type and returns a set of rows

From

"Pavel Stehule"

Date:

11 October 2008, 06:18:27

2008/10/10 Dimitri Fontaine <dfontaine@hi-media.com>:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi,
>
> Le 10 oct. 08 à 21:22, Robert Haas a écrit :
>>
>> I can't find any legal way of calling this function.
>>
>> SELECT bar(f) FROM foo f;
>> ERROR:  set-valued function called in context that cannot accept a set
>>
>> SELECT * FROM foo f, bar(f);
>> ERROR:  function expression in FROM may not refer to other relations
>> of same query level
>>
>> Any help appreciated.
>
>
> You need LATERAL support for this:
>  SELECT * FROM foo f LATERAL bar(f);
>
> I'm not sure about the syntax, but LATERAL is a standard JOIN type wherein
> upper "nodes" are visible.
> - --
> dim
>

no, this strange syntax is far to any standard. Solution is using
dynamic cursor ala DB2 (that isn't supported in postgres) - select *
from fce(cursor(select .... from tab))

Regards
Pavel Stehule

> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (Darwin)
>
> iEYEARECAAYFAkjvvYMACgkQlBXRlnbh1blatgCgnaDoSY2RGzv224QWqA8OYEjx
> fbMAoK31dHoFjOVRdomvhl/qilndRZJ5
> =3xjL
> -----END PGP SIGNATURE-----
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: calling a function that takes a row type and returns a set of rows

From

Dimitri Fontaine

Date:

11 October 2008, 20:06:07

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Le 11 oct. 08 à 08:18, Pavel Stehule a écrit :
>> I'm not sure about the syntax, but LATERAL is a standard JOIN type
>> wherein
>> upper "nodes" are visible.

> no, this strange syntax is far to any standard. Solution is using
> dynamic cursor ala DB2 (that isn't supported in postgres) - select *
> from fce(cursor(select .... from tab))


Yes it's standard notation. SQL2008 has it in Foundation document, see
its grammar reference at page 343 of my PDF reader, labelled page 321
(Query Expressions):

  7.6 <table reference>
<table primary> ::=
...
| <lateral derived table> [ AS ] <correlation name>
     [ <left paren><derived column list><right paren> ]
...

<lateral derived table> ::=
LATERAL<table subquery>

You'll find out more from http://wiscorp.com/SQLStandards.html

Regards,
- --
dim



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAkjxByYACgkQlBXRlnbh1blh8wCeNpirQ5902oVgH8Xh8rhYr3qF
rOMAn1opkDflbRn9PPuD1fmMGblLvzgQ
=7wSp
-----END PGP SIGNATURE-----