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.
Regards,
Eric Faulhaber
