set-valued function difference in 8.1.0 vs 8.0.2

Started by Ron Mayerover 20 years ago2 messagesgeneral
Jump to latest
#1Ron Mayer
rm_pg@cheapcomplexdevices.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ron Mayer (#1)
Re: set-valued function difference in 8.1.0 vs 8.0.2

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