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(at)megazone(dot)bigpanda(dot)com>
To: <J(at)Planeti(dot)Biz>
Sent: Wednesday, January 18, 2006 2:24 PM
Subject: Re: [PERFORM] Multiple Order By Criteria

> On Wed, 18 Jan 2006 J(at)Planeti(dot)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).
>