Re: Multiple Order By Criteria

Here's some C to use to create the operator classes, seems to work ok.
---

#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "utils/date.h"

/* For date sorts */

PG_FUNCTION_INFO_V1(ddd_date_revcmp);

Datum   ddd_date_revcmp(PG_FUNCTION_ARGS){
        DateADT         arg1=PG_GETARG_DATEADT(0);
        DateADT         arg2=PG_GETARG_DATEADT(1);

        PG_RETURN_INT32(arg2 - arg1);
}

/* For integer sorts */

PG_FUNCTION_INFO_V1(ddd_int_revcmp);

Datum   ddd_int_revcmp(PG_FUNCTION_ARGS){
        int32           arg1=PG_GETARG_INT32(0);
        int32           arg2=PG_GETARG_INT32(1);

        PG_RETURN_INT32(arg2 - arg1);
}

/* For string sorts */

PG_FUNCTION_INFO_V1(ddd_text_revcmp);

Datum   ddd_text_revcmp(PG_FUNCTION_ARGS){
        text*           arg1=PG_GETARG_TEXT_P(0);
        text*           arg2=PG_GETARG_TEXT_P(1);

        PG_RETURN_INT32(strcmp((char*)VARDATA(arg2),(char*)VARDATA(arg1)));
}


/*
create function ddd_date_revcmp(date,date) returns int4 as
'/data/postgres/contrib/cmplib.so', 'ddd_date_revcmp' LANGUAGE C STRICT;
create function ddd_int_revcmp(int4,int4) returns int4 as
'/data/postgres/contrib/cmplib.so', 'ddd_int_revcmp' LANGUAGE C STRICT;
create function ddd_text_revcmp(text,text) returns int4 as
'/data/postgres/contrib/cmplib.so', 'ddd_text_revcmp' LANGUAGE C STRICT;
 */

----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: <J@Planeti.Biz>
Sent: Wednesday, January 18, 2006 2:24 PM
Subject: Re: [PERFORM] Multiple Order By Criteria


> On Wed, 18 Jan 2006 J@Planeti.Biz wrote:
>
>> Could you explain to me how do create this operator class for a text data
>> type ? I think it will give me more of an understanding of what's going
>> on
>> if I could see this example.
>
> Using an SQL function (mostly because I'm too lazy to look up the C call
> syntax) I think it'd be something like:
>
> create function bttextrevcmp(text, text) returns int4 as
> 'select bttextcmp($2, $1)' language 'sql';
>
> CREATE OPERATOR CLASS text_revop
>      FOR TYPE text USING btree AS
>          OPERATOR        1       > ,
>          OPERATOR        2       >= ,
>          OPERATOR        3       = ,
>          OPERATOR        4       <= ,
>          OPERATOR        5       < ,
>          FUNCTION        1       bttextrevcmp(text,text);
>
> I believe bttextcmp is the standard text btree operator class helper
> function, so we call it with reverse arguments to try to flip its results
> (I think -bttextcmp($1,$2) would also work).
>