Hi all,
I wanted to make custom operator to sort data like this:
1,2,10,1a,1b,10a
in to order:
1,1a,1b,2,10,10a
so I did function:
create or replace function compare_lt(character varying(10),character
varying(10)) returns boolean as $$
my $v1=shift;
my $v2=shift;
undef($v1_num);
undef($v2_num);
undef($v1_letter);
undef($v2_letter);
if(!defined($v1)) {
return TRUE;
}
if(!defined($v2)) {
return FALSE;
}
$v1_num=$v1;
$v2_num=$v2;
if($v1=~/^(\d+)([a-z]+)$/) {
$v1_num=$1;
$v1_letter=$2;
}
if($v2=~/^(\d+)([a-z])$/) {
$v2_num=$1;
$v2_letter=$2;
}
if($v1_num<$v2_num) {
return TRUE;
}
if($v2_num<$v1_num) {
return FALSE;
}
if($v1_num==$v2_num) {
if(!defined($v1_letter)) {
return TRUE;
}
if(!defined($v2_letter)) {
return FALSE;
}
if($v1_letter lt $v2_letter) {
return TRUE;
}
if($v2_letter lt $v1_letter) {
return FALSE;
}
return FALSE;
}
return FALSE;
$$ language plperl;
and then custom operator like this:
create operator << (procedure=compare_lt,
leftarg=varchar(10),rightarg=varchar(10));
when I use this operator once in query, result is sorted properly:
select field1 from my_table order by field1 using <<;
But I have two fileds in my_table with such data, and I would like to sort
both of them like this
field1 field2
1 1a
1 1b
2 2
2 2a
10 10
10 10a
with query:
select field1,field2 from my_table order by field1 using <<, field2 using <<;
But the second column (field2) is not sorted at all, even with default
operator, it goes "as is" from table.
So why my custom operator determines sort order so hard, that others operators
don't work ? and how to change this?
many thanks in advance
Marek
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
