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

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(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>