From: BGoebel <b.goebel@prisma-computer.de>
To: pgsql-admin@postgresql.org
Subject: Re: german sort is wrong
Date: Fri, 23 Mar 2012 11:04:57 -0700 (PDT)
Message-ID: <1332525897349-5590321.post@n5.nabble.com> (raw)
In-Reply-To: <4F69E402.8030708@spdfraktion.de>
References: <4F689E94.2090408@spdfraktion.de>
<4F69967A02000025000464DC@gw.wicourts.gov>
<4F69E402.8030708@spdfraktion.de>
Reinhard Asmus wrote
>
> when i make the same thing in oracle i've got
>
> Ätna
> Anton
> ....
>
> is it possible to get the same with postgresql and when how?
>
>
PostgreSQLs collation support is not complete. Until now, you can not define
a sort order of your own.
Workaround:
Step1: Define a function like this
CREATE OR REPLACE FUNCTION germanorder(text)
RETURNS text AS
$BODY$ SELECT
REPLACE(REPLACE(REPLACE(REPLACE(lower($1),'ß','ss'),'ä','ae'),'ö','oe'),'ü','ue')
$BODY$
LANGUAGE sql VOLATILE
COST 100;
ALTER FUNCTION prisorder(text)
OWNER TO postgres;
step2: Create an functional index
( http://www.postgresql.org/docs/7.3/static/indexes-functional.html )
CREATE INDEX bezeichnung_germanorder_idx ON test1 (germanorder(col1));
step3: Select using the function
select * from sgvs.test order by germanorder(bezeichnung);
and you will get what you want...
If speed does not matter you can omit step2.
regards
--
View this message in context: http://postgresql.1045698.n5.nabble.com/german-sort-is-wrong-tp5582836p5590321.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: b.goebel@prisma-computer.de
Subject: Re: german sort is wrong
In-Reply-To: <1332525897349-5590321.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox