problem using twice custom comparision operator

From: Marek Florianczyk <franki(at)adm(dot)tp(dot)pl>
To: pgsql-sql(at)postgresql(dot)org
Subject: problem using twice custom comparision operator
Date: 2009-01-23 20:42:44
Message-ID: 200901232142.44102.franki@adm.tp.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2009-01-23 22:40:52 Re: problem using twice custom comparision operator
Previous Message Bruce Momjian 2009-01-21 17:52:55 Re: [SQL] array_to_string(anyarray, text) that was working in 8.1 is not working in 8.3