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
