Re: Collations versus user-defined functions

Greg Stark <gsstark(at)mit(dot)edu> writes:
> On Sat, Mar 12, 2011 at 5:17 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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