Hello!
One of least liked by me features of PostgreSQL is a need to specify
LC_CTYPE an LC_COLLATE at initdb time. Especially if you intend to
put into DB texts in
different languages (say, Polish, French, German and Russian) and use
functions like lower() or ORDER BY these texts. :)
I guess the need to select these two locales at initdb time is to
avoid problems with corrupted indexes (column first indexed
with lower() function when setlocale('C'), then accessed when
setlocale('ru_RU')... oops. etc.). Probably there are more of
those. :)
To solve this I thought about creating function lower(text, locale), say:
lower ('Name', 'pl_PL.utf8');
Simple enough, I used plperl (plperlu actually) to make it happen and
while doing so I've noticed that perl does unicode-lowercasing/uppercasing on
its own accord, doesn't need locales to it, and does it pretty well.
So the resulting function is:
CREATE DOMAIN unitext text;
CREATE OR REPLACE FUNCTION lower(unitext) RETURNS unitext AS $$
utf8::decode($_[0]);
return lc($_[0]);
$$ LANGUAGE plperlu IMMUTABLE;
And it seems to work fine regardless of locale set.
So... I thoght, why not use this unitext to sort texts?
So I've created functions, operators and operator class,
This time setlocale() was needed to get the behaviour
I needed (database initdb'ed to 'C', my order set to 'pl_PL',
or whatever locale I need at given moment).
I've attached a 'draft' of unitext,sql, which should create
unitext datatype capable of sorting according Polish locale.
It does not work as it should and I don't know how to make
it work. For example:
SELECT * FROM uni_tab ORDER BY uni_column;
...sorts according to 'C' (initdb's) locale.
I can force my way by issuing:
SELECT * FROM uni_tab ORDER BY uni_column USING <;
...but I would like to force ORDER BY using operators
provided by me without this 'USING <' clause.
Any hints how to do it?
Regards,
Dawid
PS: I like perl's lc() and uc() behaviour in utf8 mode. I'm thinking about
trying to "port" it from perl source as a C-language extension for PostgreSQL.
What do you think about it?
SET search_path = public;
CREATE DOMAIN unitext text;
BEGIN;
CREATE OR REPLACE FUNCTION lower(unitext) RETURNS unitext AS $$
utf8::decode($_[0]);
return lc($_[0]);
$$ LANGUAGE plperlu IMMUTABLE;
CREATE OR REPLACE FUNCTION upper(unitext) RETURNS unitext AS $$
utf8::decode($_[0]);
return uc($_[0]);
$$ LANGUAGE plperlu IMMUTABLE;
CREATE OR REPLACE FUNCTION unitext_lt(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] lt $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu;
CREATE OR REPLACE FUNCTION unitext_le(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] le $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu STABLE;
CREATE OR REPLACE FUNCTION unitext_gt(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] gt $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu STABLE;
CREATE OR REPLACE FUNCTION unitext_ge(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] ge $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu STABLE;
CREATE OR REPLACE FUNCTION unitext_eq(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] eq $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu STABLE;
CREATE OR REPLACE FUNCTION unitext_ne(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] ne $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu;
CREATE OR REPLACE FUNCTION unitext_cmp(unitext,unitext) RETURNS integer AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = $_[0] cmp $_[1];
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu;
COMMIT;
CREATE OPERATOR < (
LEFTARG = unitext,
RIGHTARG = unitext,
-- COMMUTATOR = >,
-- NEGATOR = >=,
PROCEDURE = unitext_lt,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);
CREATE OPERATOR <= (
LEFTARG = unitext,
RIGHTARG = unitext,
-- COMMUTATOR = >=,
-- NEGATOR = >,
PROCEDURE = unitext_le,
RESTRICT = scalarltsel,
JOIN = scalarltjoinsel
);
CREATE OPERATOR >= (
LEFTARG = unitext,
RIGHTARG = unitext,
COMMUTATOR = <=,
NEGATOR = <,
PROCEDURE = unitext_ge,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);
CREATE OPERATOR > (
LEFTARG = unitext,
RIGHTARG = unitext,
COMMUTATOR = <,
NEGATOR = <=,
PROCEDURE = unitext_gt,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);
CREATE OPERATOR <> (
LEFTARG = unitext,
RIGHTARG = unitext,
COMMUTATOR = <>,
-- NEGATOR = =,
PROCEDURE = unitext_ne,
RESTRICT = neqsel,
JOIN = neqjoinsel
);
CREATE OPERATOR = (
LEFTARG = unitext,
RIGHTARG = unitext,
COMMUTATOR = =,
NEGATOR = <>,
HASHES, MERGES,
RESTRICT = eqsel,
JOIN = eqjoinsel,
SORT1 = <,
SORT2 = <,
LTCMP = <,
GTCMP = >,
PROCEDURE = unitext_eq,
RESTRICT = eqsel,
JOIN = eqjoinsel
);
CREATE OPERATOR CLASS unitext_ops
DEFAULT FOR TYPE unitext USING btree
AS
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 unitext_cmp(unitext, unitext);
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
