set-valued function difference in 8.1.0 vs 8.0.2
I just wanted to make sure this change in behavior is
a feature and not a bug.
This block of code with a (mis?)use of a set-valued function:
CREATE OR REPLACE FUNCTION foo() RETURNS SETOF varchar AS $$ {
my $res = [];
push @$res,'a';
push @$res,'b';
return $res;
} $$ LANGUAGE plperl STABLE;
select * from foo();
select foo();
worked "fine" in 8.0.2 but gives an
ERROR: set-valued function called in context that cannot accept a set
error in 8.1.0.
The 8.0.2 behavior of expanding the set-valued function when used
in the left part of the select clause was convenient in some
functions where I had used it like this:
select addr,geocode_alternatives(addr) from (
select addr from table where ambiguous=true
) as a;
where geocode_alternatives was a set-valued function that returned
all the alternatives for the ambiguous addresses.
Basically the results with 8.0.2 were something like:
addr | geocode_alternative
-----------+----------------
1 main st | 1 N main st
1 main st | 1 S main st
1 main st | 1 main ave
30 mlk dr | 2 Martin Luther King dr
30 mlk dr | 2 milk dr
And now I'm having a hard time coming up with a way of
re-writing it without a similar error. Is there an
easy way of rewriting this construct where the results
of a function can expand the result set that works
nicely in 8.1?
Thanks,
Ron
rm_pg@cheapcomplexdevices.com writes:
I just wanted to make sure this change in behavior is
a feature and not a bug.
Afraid so --- the plperl SRF support was rewritten in 8.1, and
it probably behaves just like plpgsql now, which has also got
this restriction.
Is there an easy way of rewriting this construct where the results of
a function can expand the result set that works nicely in 8.1?
A kluge some people have used with plpgsql is to put a SQL-language
wrapper function around the PL function, ie
create function foo(...) returns ... as
'select * from pl_foo(...)'
language sql;
Should work for plperl too.
regards, tom lane