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
>>
>
