Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 1CA62C14E5D
	for <pgsql-admin@postgresql.org>; Fri, 23 Mar 2012 15:05:13 -0300 (ADT)
Received: from sam.nabble.com ([216.139.236.26])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <b.goebel@prisma-computer.de>) id 1SB8rP-0006tj-Ar
	for pgsql-admin@postgresql.org; Fri, 23 Mar 2012 18:05:12 +0000
Received: from [192.168.236.26] (helo=sam.nabble.com)
	by sam.nabble.com with esmtp (Exim 4.72)
	(envelope-from <b.goebel@prisma-computer.de>) id 1SB8rB-0003ev-BV
	for pgsql-admin@postgresql.org; Fri, 23 Mar 2012 11:04:57 -0700
Date: Fri, 23 Mar 2012 11:04:57 -0700 (PDT)
From: BGoebel <b.goebel@prisma-computer.de>
To: pgsql-admin@postgresql.org
Message-ID: <1332525897349-5590321.post@n5.nabble.com>
In-Reply-To: <4F69E402.8030708@spdfraktion.de>
References: <4F689E94.2090408@spdfraktion.de>
	<4F69967A02000025000464DC@gw.wicourts.gov>
	<4F69E402.8030708@spdfraktion.de>
Subject: Re: german sort is wrong
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -0.8 (/)
X-Archive-Number: 201203/123
X-Sequence-Number: 39311


Reinhard Asmus wrote
>=20
> when i make the same thing in oracle i've got
>=20
> =C3=84tna
> Anton
> ....
>=20
> is it possible to get the same with postgresql and when how?
>=20
>=20

PostgreSQLs collation support is not complete. Until now, you can not defin=
e
a sort order of your own.

Workaround:=20

Step1: Define a function like this

  CREATE OR REPLACE FUNCTION germanorder(text)
    RETURNS text AS
  $BODY$ SELECT
REPLACE(REPLACE(REPLACE(REPLACE(lower($1),'=C3=9F','ss'),'=C3=A4','ae'),'=
=C3=B6','oe'),'=C3=BC','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/germa=
n-sort-is-wrong-tp5582836p5590321.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.