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
>