[SQL] problem using twice custom comparision operator

[Marek Florianczyk]

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 (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql