Re: Multiple Order By Criteria

I have the answer I've been looking for and I'd like to share with all.
After help from you guys, it appeared that the real issue was using an index
for my order by X DESC clauses. For some reason that doesn't make good
sense, postgres doesn't support this, when it kinda should automatically.

Take the following end of an SQL statement.

order by
    col1 DESC
    col2 ASC
    col3 ASC

The first thing I learned is that you need an index that contains all these
columns in it, in this order. If one of them has DESC then you have to
create a function / operator class for each data type, in this case let's
assume it's an int4. So, first thing you do is create a function that you're
going to use in your operator:

create function
    int4_revcmp(int4,int4)  // --> cal the function whatever you want
    returns int4
    as 'select $2 - $1'
language sql;

Then you make your operator class.
CREATE OPERATOR CLASS int4_revop
      FOR TYPE int4 USING btree AS
          OPERATOR        1       > ,
          OPERATOR        2       >= ,
          OPERATOR        3       = ,
          OPERATOR        4       <= ,
          OPERATOR        5       < ,
          FUNCTION        1       int4_revcmp(int4, int4); // --> must be
the name of your function you created.

Then when you make your index

create index rev_idx on table
    using btree(
        col1 int4_revop,    // --> must be name of operator class you
defined.
        col2,
        col3
);

What I don't understand is how to make this function / operator class work
with a text datatype. I tried interchanging the int4 with char and text and
postgres didn't like the (as 'select $2 - $1') in the function, which I can
kinda understand. Since I'm slighlty above my head at this point, I don't
really know how to do it. Does any smart people here know how ?