Re: Multiple Order By Criteria

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 ?



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux