Re: Set COLLATE on a session level

> > 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.
>
> One might use a function producing a SELECT taking the locale as a parameter.
>
> Or views in schemas per locale. Selecting the search path
> per locale pulls in the right view.

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.

And one view per locale would mean that I would have to create a whole schema including all views for each locale I want to support. I would have to roll out a new version of the data model, just for an additional locale.

This all seems unnecessarily complicated to me.

Dirk

--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk(dot)mika(at)mikatiming(dot)de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika