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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match