Set COLLATE on a session level

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Set COLLATE on a session level

Dirk Mika
Hello,

we come from the Oracle world and we have an application that, depending on a setting, sends the command ALTER SESSION SET NLS_SORT=... when connecting to the database.

Is there a similar way to set a COLLATE for a session in PostgreSQL?

I know that I can specify a COLLATE for a SELECT statement in the ORDER BY Clause, but then I would have to adjust the statements in the client and statements that are automatically generated by the database components used, would not be affected.

Regards
Dirk



--
Dirk Mika
Software Developer

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

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

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



## INEOS 1:59 memories
## https://www.facebook.com/mikatiming
Reply | Threaded
Open this post in threaded view
|

Re: Set COLLATE on a session level

Laurenz Albe
On Fri, 2020-11-20 at 07:13 +0000, Dirk Mika wrote:
> we come from the Oracle world and we have an application that, depending on a setting,
>  sends the command ALTER SESSION SET NLS_SORT=... when connecting to the database.
>
> Is there a similar way to set a COLLATE for a session in PostgreSQL?
>
> I know that I can specify a COLLATE for a SELECT statement in the ORDER BY Clause,
>  but then I would have to adjust the statements in the client and statements that are
>  automatically generated by the database components used, would not be affected.

There is no way to do that in PostgreSQL.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: Set COLLATE on a session level

Dirk Mika
Thank you for the quick reply. But how is this usually solved?

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.

Regards
Dirk


--
Dirk Mika
Software Developer

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

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

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


Reply | Threaded
Open this post in threaded view
|

Re: Set COLLATE on a session level

Pavel Stehule
Hi

pá 20. 11. 2020 v 15:28 odesílatel Dirk Mika <[hidden email]> napsal:
Thank you for the quick reply. But how is this usually solved?

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.

Regards

Pavel


Regards
Dirk


--
Dirk Mika
Software Developer

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

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

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


Reply | Threaded
Open this post in threaded view
|

Re: Set COLLATE on a session level

Karsten Hilbert
On Fri, Nov 20, 2020 at 03:32:48PM +0100, Pavel Stehule wrote:

> pá 20. 11. 2020 v 15:28 odesílatel Dirk Mika <[hidden email]>
> 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.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B