Re: Collations versus user-defined functions

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Sat, Mar 12, 2011 at 12:17:11PM -0500, Tom Lane wrote:
>> I've thought of another area that AFAICT the current patch fails to
>> address at all: what should happen in user-defined functions?

> The POLA suggests that the collation derivation of the original query
> should not be affected by the implementation of a function. In the case
> of SQL functions this means that the expansion of the function must
> not change the results. This would mean introducing a CollateNode above
> the SQL function after expansion, though you may be able to acheive
> this by doing the collation derivation prior to expansion of the SQL
> function, but I don't know if that's feasable.

CollateExpr as presently defined wouldn't get the job done, but I think
it's not a problem because those nodes aren't actually needed at runtime
--- collation assignment for operators/functions above the inlined
function should have been done when they were parsed, so it won't change
as a result of expanding an inlined function.

> Similarly, inside the function the parameters should be considered to
> be IMPLICIT collation, to avoid strange errors depending on how its
> called.

Not convinced by this.  If we say that that's how it works, then no
user-defined function should react to COLLATE in its arguments at all,
which seems pretty weird and restrictive --- especially if the COLLATE
property is expected to propagate up through the function call so
far as the calling expression is concerned.  It seems just bizarre to
me to say that a function's internal operations don't react to an
input collation spec but then its result is thought to still be affected
by that.

This would actually seem more sensible if we went with something even
simpler than the current patch's behavior, namely that COLLATE only
affects the operator it is an *immediate* input of, and nothing
propagates upward in expressions ever.  I remain unconvinced that the
SQL spec is calling for propagation ...

> I think you need to consider the collation to be a variation of the
> type. plpgsql makes new plans for each type when dealing with any
> parameters, this should fit right in.

Yeah, the same occurred to me a little bit later --- we can actually
make that work fairly easily by treating collatable input datatypes
as if they were polymorphic.  But the question is whether we should.
You seem to be arguing above that user-defined functions ought not
pay attention to COLLATE specs on their inputs.
        regards, tom lane