Custom column ordering - Mailing list pgsql-general

<font face=3D"Default Sans Serif,Verdana,Arial,Helvetica,sans-serif" size=
=3D"2">Hi all,I'm trying to order some rows based on port names, a =
text column, using some domain-specific knowledge for Netdisco, an open-sou=
rce application. In particular, I'm trying to do this without havin=
g to redo the entire design for the database. Note that in this database, t=
here are no foreign key constraints, mostly because they weren't considered=
 in the original inception of the software. The rough idea of the s=
olution I've come up with is to create a new composite data type called "<f=
ont face=3D"Default Monospace,Courier New,Courier,monospace">port" w=
ith a single text column. Then I created the relevant comparison functions,=
 operators and operator classes under the "<font face=3D"Default Monospace,=
Courier New,Courier,monospace">port=5Fops" operator family. Then, I =
created a function "<font face=3D"Default Monospace,Courier New,Courier,mon=
ospace">cast=5Fto=5Fport(text)" that casts "<font face=3D"Default Mo=
nospace,Courier New,Courier,monospace">text" data types to "<font fa=
ce=3D"Default Monospace,Courier New,Courier,monospace">port" (which =
simply creates a tuple with the single text value, see the code for details=
). Finally, I created an index on the "<font face=3D"Default Monospace,Cour=
ier New,Courier,monospace">device=5Fport" table with "<font face=3D"=
Default Monospace,Courier New,Courier,monospace">cast=5Fto=5Fport(port)</fo=
nt>" as the indexed column using  "port=5Fops". However, when I run "<font =
face=3D"Default Monospace,Courier New,Courier,monospace">select port from d=
evice=5Fport order by cast=5Fto=5Fport(port)", it doesn't use the in=
dex I created and doesn't even order using the operators I created. Instead=
, it orders by the lexicographical ordering of the original text column.<br=
>Questions:  - Why is PostgreSQL not using the functional inde=
x I created and why is it not being ordered correctly?  - Is creat=
ing a separate data type and using a functional index on the casts to this =
data type the right approach to a custom ordering?Steven<br=
>Details:Creating the "<font face=3D"Default Monospace,Courier New,Cour=
ier,monospace">port" type:<font face=3D"Default Monospace,Courie=
r New,Courier,monospace">  create type port as (f1 text);<b=
r>Creating the comparator function, substituted with much shorter code as a=
n example. I can include the original code, but it's much shorter to provid=
e this.It returns -1 if the first argument is "less than" the second ar=
gument, 0 if they are equal and 1 otherwise. In this example, "less than" m=
eans lexicographically greater, because of the negation.<font face=3D"D=
efault Monospace,Courier New,Courier,monospace">  create or replace fu=
nction port=5Fcmp(port,port)      RETURNS integer as=
  $$    my ($a, $b) =3D @=5F;    retu=
rn -( $a cmp $b ); // comparison function does the opposite of cmp =
; $$ language plperl;Creating the "<font face=3D"Default Mon=
ospace,Courier New,Courier,monospace">port=5Fops" operators and oper=
ator classes for the "<font face=3D"Default Monospace,Courier New,Courier,m=
onospace">port" type:<font face=3D"Default Monospace,Courier New=
,Courier,monospace">CREATE OR REPLACE FUNCTION port=5Flt(port, port)&nb=
sp;   RETURNS boolean AS$$    BEGIN&nb=
sp;       RETURN port=5Fcmp($1, $2) < 0;&nb=
sp;   END;$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCT=
ION port=5Fgt(port, port)    RETURNS boolean AS$$<br=
>    BEGIN        RETURN p=
ort=5Fcmp($1, $2) > 0;    END;$$ LANGUAGE plpgsql=
;CREATE OR REPLACE FUNCTION port=5Flte(port, port)  &=
nbsp; RETURNS boolean AS$$    BEGIN  &=
nbsp;     RETURN port=5Fcmp($1, $2) <=3D 0; &nbs=
p;  END;$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION por=
t=5Fgte(port, port)    RETURNS boolean AS$$ =
;   BEGIN        RETURN port=5F=
cmp($1, $2) >=3D 0;    END;$$ LANGUAGE plpgsql;<b=
r>CREATE OR REPLACE FUNCTION port=5Feq(port, port)   =
; RETURNS boolean AS$$    BEGIN   =
;     RETURN port=5Fcmp($1, $2) =3D 0;   =
 END;$$ LANGUAGE plpgsql;CREATE OPERATOR < ( PROCEDURE=3Dpor=
t=5Flt, LEFTARG=3Dport, RIGHTARG=3Dport);CREATE OPERATOR <=3D ( PROC=
EDURE=3Dport=5Flte, LEFTARG=3Dport, RIGHTARG=3Dport);CREATE OPERATOR &g=
t;=3D ( PROCEDURE=3Dport=5Fgte, LEFTARG=3Dport,  RIGHTARG=3Dport);=
CREATE OPERATOR > ( PROCEDURE=3Dport=5Fgte, LEFTARG=3Dport, RIGHTARG=3Dp=
ort);<font face=3D"Default Monospace,Courier New,Courier,monospa=
ce">CREATE OPERATOR =3D ( PROCEDURE=3Dport=5Feq, LEFTARG=3Dport, RIGHTARG=
=3Dport);CREATE OPERATOR CLASS port=5Fops DEFAULTFOR TYPE port =
USING btreeASOPERATOR        1&n=
bsp;      <  ,OPERATOR  &nbs=
p;     2       <=3D ,<=
br>OPERATOR        3   &n=
bsp;   =3D  ,OPERATOR      =
;  4       >=3D ,OPERATOR &n=
bsp;      5       &g=
t;  ,FUNCTION        1 &nb=
sp;     port=5Fcmp(port, port);Creat=
ing the function to cast "<font face=3D"Default Monospace,Courier New,Couri=
er,monospace">text" data to "<font face=3D"Default Monospace,Courier=
 New,Courier,monospace">port" data:<font face=3D"Default Monospa=
ce,Courier New,Courier,monospace">CREATE OR REPLACE FUNCTION cast=5Fto=5Fpo=
rt(text) RETURNS port LANGUAGE sqlAS $function$&n=
bsp; SELECT ($1);$function$ IMMUTABLE;Creating the index=
:CREATE =
INDEX idx=5Fdevice=5Fport=5Fport=5Fproper on device=5Fport (cast=5Fto=5Fpor=
t(port) port=5Fops desc);Sample table structure, shortened f=
or brevity:<font face=3D"Default Monospace,Courier New,Courier,monospac=
e">            =
         Table "public.device=5Fpor=
t"    Column    |    =
        Type    &nbs=
p;        |     =
;  Modifiers--------------+-----------------------------+---------=
--------------- ip        =
   | inet         &n=
bsp;            =
;  | not null port       &=
nbsp; | text          &nb=
sp;            =
 | not null