Greg Stark <gsst...@mit.edu> writes: > On Sat, Mar 12, 2011 at 5:17 PM, Tom Lane <t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers