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

["Pavel Stehule" <pavel.stehule@xxxxxxxxx>]

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@xxxxxxxxx>:
> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>