problem using twice custom comparision operator

Поиск
Список
Период
Сортировка
От Marek Florianczyk
Тема problem using twice custom comparision operator
Дата
Msg-id 200901232142.44102.franki@adm.tp.pl
обсуждение исходный текст
Ответы Re: problem using twice custom comparision operator  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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


В списке pgsql-sql по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: array_to_string(anyarray, text) that was working in 8.1 is not working in 8.3
Следующее
От: Tom Lane
Дата:
Сообщение: Re: problem using twice custom comparision operator