From: | rm_pg(at)cheapcomplexdevices(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | rm_pg(at)cheapcomplexdevices(dot)com |
Subject: | set-valued function difference in 8.1.0 vs 8.0.2 |
Date: | 2005-11-09 21:20:27 |
Message-ID: | Pine.LNX.4.58.0511091259080.1282@greenie.cheapcomplexdevices.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dean Gibson (DB Administrator) | 2005-11-09 21:23:44 | Re: Best way to use indexes for partial match at beginning |
Previous Message | Jaime Casanova | 2005-11-09 21:15:32 | Re: Best way to use indexes for partial match at beginning |