Re: Final result (display) collation?

On Tue, 2024-12-17 at 20:36 -0500, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > Crazy idea: what if we treated the top-level ORDER BY as special?
>
> This seems like an amazing kluge, and it's not even clear that
> there's any field demand for it.

I had a discussion with Vik, and he suggested something less crazy:
what if we had a "COLLATE SESSION" (or some special name) that would
mean that it should get the actual collation from a GUC? (Obviously
this would only work on expressions and not column definitions.)

That would solve one fairly annoying problem: we don't guarantee that
any particular collation actually exists (aside from the memcmp-based
ones), so it's hard to configure applications to use the COLLATE clause
at all.

The Django docs[1] reference "et-x-icu", but it's hard to know which
ones are available. We have documentation about how to construct ICU
language tags, but not about which specific language/region
combinations are available, because those comes from ICU, which doesn't
seem to document them either.

Granted, if you want to use US English, someone will have to know the
name "en-US-x-icu" or "en_US" at some point. But it's more reasonable
to expect a user doing "ALTER USER ... SET" to know the available
Postgres collations on that particular server than an application
developer who might expect it to work on any Postgres instance. And
GUCs are a much more flexible solution than an application
configuration file, or worse, hard-coded in application code[2].

Aside: we'd need to differentiate this enough from the SQL standard
"SET COLLATION", which (IIUC) affects all expressions in the query.

Regards,
Jeff Davis

[1]
https://docs.djangoproject.com/en/5.1/ref/models/database-functions/

[2]
https://stackoverflow.com/questions/60318707/how-to-use-collate-as-with-sequel-and-ruby