Re: Multiple Order By Criteria

On Wed, 18 Jan 2006 J(at)Planeti(dot)Biz wrote:

> 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.

Well, the problem is that we do order with the index simply by through
following index order. Standard index order is going to give you a sorted
order only in some particular order and its inverse. IIRC, there are ways
to use an index in all ascending order to do mixed orders, but I think
those may involve traversing parts of the index multiple times and hasn't
been implemented.

> 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 ?

I think having the function call the helper function for the normal
operator class for the type function with the arguments in reverse order
may work (or negating its output).

If you have any interest, there's an outstanding call for C versions of
the helper functions that we could then package up in contrib with the
operator class definitions.