Re: Final result (display) collation?

On Wed, 2024-12-18 at 08:26 +0100, Laurenz Albe wrote:
> Yes, that approach seems strange.

My thought was it would be more like a client option than anything
else: a way for the application to request the same result in a more
convenient order. A bit strange, but depending on how you look at it,
is not very radical. More like setting lc_numeric.

>   Also, wouldn't that prevent
> plans that calculate the final sort using a nested loop or merge
> join further down?

If the purpose is for a final result to be displayed to a human (rather
than fed into some other data processing system), then it's going to be
a small result and the final sort should be very cheap.

> But I like the idea of a parameter that determines the collation.
> I am aware that it is anathema here to have a GUC that influences
> query semantics, but it wouldn't be any worse than creating a
> database with a different collation, so I think it would be fine.

That appears to be how the SET COLLATION statement is specified in SQL.
Perhaps we should explore that in more detail?

I think we'd have to be careful that it only applies to the top-level
query and not SPI queries or CHECK constraints. There are probably some
technical challenges in that, but it might not be too bad.

> FWIW, Oracle has a parameter NLS_SORT that does just that.

Ah, interesting.

Regards,
Jeff Davis