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:[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
