Hello.

Why pgplsql setof ... function cannot be used in field list of select?
It is possible to use C functions, plperl functions and sql functions,
but pgplsql returns:

ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "my_gen_ser" line 6 at return next

One can use sql function wrapper like this:

create or replace function my_gen_ser(int,int) returns setof int as '
declare
i int;
begin
i:=$1;
while i<=$2 loop
return next i;
i:=i+1;
end loop;
return;
end;
'
language plpgsql immutable;

/*
select t,my_gen_ser(t,t+t) from generate_series(2,5) as t;
fails with
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "my_gen_ser" line 6 at return next
*/

create or replace function get_setint(int,int) returns setof int as '
select * from my_gen_ser($1,$2)
'
language sql immutable;

/* but this works good */
select t,get_setint(t,t+t) from generate_series(2,5) as t;

But I think it's awful, don't you?

Sokolov Yura mailto:falcon@intercable.ru



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly