Skip site navigation (1) Skip section navigation (2)

Reverse order sort in multi-column indexes

From: Eric Faulhaber <ecf(at)goldencode(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Reverse order sort in multi-column indexes
Date: 2005-10-31 21:19:16
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
In searching the archives, I have found postings by people migrating 
from other databases, who have expressed a need to support the semantic 
of descending sort direction in a multi-column index, as in:

   create index test_idx on test_table (column_a asc, column_b desc)

I understand this is not the convention in PostgreSQL and that this 
syntax is not likely to be supported, as direction is only meaningful 
for B-tree index types.  So, I gather that the way to do this for a 
B-tree index in PostgreSQL is by defining an opclass which knows how to 
sort a data type in reverse order.  A previous post suggests:

 > A useful descending-order opclass
 > simply rearranges the logical relationships of the standard comparison
 > operators.  You do need a new comparison function, but nothing else:
 >   CREATE OPERATOR CLASS int4_reverse_order_ops
 >      FOR TYPE int4 USING btree AS
 >          OPERATOR        1       > ,
 >          OPERATOR        2       >= ,
 >          OPERATOR        3       = ,
 >          OPERATOR        4       <= ,
 >          OPERATOR        5       < ,
 >          FUNCTION        1       int4_reverse_order_cmp(int4, int4);
 > Now you can just use ASC/DESC in your ORDER BY ...
 >             regards, tom lane

What I'm having trouble understanding is the bit about the new 
comparison function:

1a)  If we've already "reversed" the operators' meanings as described 
above by reassigning them to different strategy numbers, why is it also 
necessary to define a new comparison function?  Wouldn't a reference to 
the standard comparison function for the target data type cause it to 
pick up the new, "reverse" operations automatically for this opclass?

1b)  I suppose the converse question is:  if we provide a new comparison 
function which implements the reverse comparison strategy, why is it 
necessary to re-assign all the operators to different strategy numbers?

2)  I couldn't find any posting describing how such a comparison 
function would actually be defined.  From Chapter 33 of the 7.4.2 user 
manual, it is possible to define SQL, procedural, internal, and 
C-language functions.  Which is most appropriate here?  Can anyone point 
to an example of the simplest way to do this?

TIA for any help.

Eric Faulhaber


pgsql-novice by date

Next:From: Tom LaneDate: 2005-10-31 21:39:40
Subject: Re: Reverse order sort in multi-column indexes
Previous:From: Kevin CrenshawDate: 2005-10-31 18:11:27
Subject: Re: Problem inserting a row containing GUIDs

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group