Re: problem using twice custom comparision operator

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