Thread: problem using twice custom comparision operator

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

Marek Florianczyk <franki@adm.tp.pl> writes:
> Hi all,
> I wanted to make custom operator to sort data like this:
> 1,2,10,1a,1b,10a

It might work better if you were more careful to ensure that the
operator were a valid sort operator, ie
! a<a            not reflexivea < b => ! b < a    not commutativea < b & b < c => a < c    transitive

It fairly obviously fails the first of these, and I don't have too much
confidence in the others.
        regards, tom lane

At 01:20 PM 1/24/2009, pgsql-sql-owner@postgresql.org wrote:
>From: Marek Florianczyk <franki@adm.tp.pl>
>Organization: TP SA
>To: pgsql-sql@postgresql.org
>Subject: problem using twice custom comparision operator
>Date: Fri, 23 Jan 2009 21:42:44 +0100
>Message-Id: <200901232142.44102.franki@adm.tp.pl>
>
>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

Hi Marek,

The following idea may be too different to fit your needs, but I got 
some help from this list a while back on how to force sorts for a 
specific query. It sounds like you want to override searching for all 
queries, so this may not be appropriate. Anyway here's an example of a 
solution that sorts things in arbitrary order for any given query:

SELECT * FROM foobar
ORDER BY CASE field
WHEN 555 then 1
WHEN 342 then 2
WHEN 111 then 3
ELSE 4

This sorts "555" then "342" then "111" then everything else.

Obviously this is oversimplified for your case, but you could write 
some comparisons in place of the static numbers (e.g. "555") that 
follow the same rules as the function you're writing. I don't know if 
performance would be anything comparable either (I'd guess that using 
the "regex" operators (like "~*" would be the way to go).

I thought I'd mention this other approach in case it was of interest 
and you haven't run across it before.

Sincerely,

Steve