Greg Stark <gsstark@mit.edu> writes:
> On Sat, Mar 12, 2011 at 5:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> create function my_lt(x text, y text) returns bool as
>> $
>> begin
>> return x < y;
>> end
>> $ language plpgsql;
>>
>> select my_lt('foo', 'bar' collate "de_DE");
>> select my_lt('foo', 'bar' collate "fr_FR");
>>
>> I think it's at least arguably desirable that the results of the two
>> calls respond to the collation clauses, but it does not look to me
>> like that will happen: plpgsql isn't doing anything to propagate
>> its call-site collation value into expressions it evaluates, and
>> if it did, it'd still get the wrong answer on the second call because it
>> would have cached an expression plan tree containing the collation info
>> from the first call.
> I don't think it's obvious that this is the right behaviour.
I'm not sure of that either, but ...
> I think
> functions should provide the same answer on the same inputs regardless
> of context unless they're really intended to be volatile.
... that argument convinces me not at all, because they are *not* the
same inputs. The collate clauses are different. If I believed your
argument, then the built-in "<" function shouldn't respond to COLLATE
either.
> If you want to affect the way a plpgsql function orders things in its
> code you should pass an extra argument for collation and then the
> plpgsql function should use COLLATE colarg -- though I'm not sure if
> that works, can you put parameters in COLLATE arguments?
No, you cannot, the SQL committee has blown it on that. COLLATE's
argument is an identifier not a variable. There is no way to do
runtime selection of collation like that.
regards, tom lane
