Re: Set COLLATE on a session level

On 2020-11-30 10:11:38 +0000, Dirk Mika wrote:
> > > pá 20. 11. 2020 v 15:28 odesílatel Dirk Mika <Dirk(dot)Mika(at)mikatiming(dot)de>
> > > napsal:
> > > > Let's assume there is an app that accesses the same database from
> > > > different countries. And in this app data should be displayed ordered. And
> > > > the sort order is not identical in all countries.
> > > >
> > > > Does the app have to send different SQL commands depending on the country?
> > > > Not nice.
> > > > Do the data have to be sorted in the app? Not nice either.
> > > >
> > >
> > > the query is the same - you just use a different COLLATE clause. For
> > > Postgres there is not any other way.
[...]
> Suppose the SQL statements are generated by a database layer such as
> Hibernate. It seems to me that it is not possible to use a function
> that adds the COLLATE clause.

It seems to me that this is a defect in the ORM. Sorting by current
locale rules is important for many applications, so that is something an
ORM should support. How the ORM does it (setting a session parameter,
modifying the query, ...) may be backend-specific and not something the
programmer should worry about.

That said, I don't even know if Django (the only ORM I've used in any
depth) does that.

I also agree, that logically, the collation order should be a session
parameter. It is language-specific and therefore user-specific if you
have international users. (I acknowledge the potential performance
problems, but they are the same with an explicit collation clause).

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"