From: J@Planeti.Biz
Message-ID: <021e01c61c66$a0899190$81300d05@fatchubby>
To: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
Cc: <pgsql-performance@postgresql.org>
References: <001401c61bf3$aec1fef0$7f00a8c0@kicommunication.com>
	<00d801c61c38$52863030$81300d05@fatchubby>
	<20060118065824.K33528@megazone.bigpanda.com>
	<018a01c61c45$a49bc170$81300d05@fatchubby>
	<20060118090826.Y44833@megazone.bigpanda.com>
	<01a101c61c54$0c484010$81300d05@fatchubby>
	<20060118111040.J54519@megazone.bigpanda.com>
Subject: Re: Multiple Order By Criteria
Date: Wed, 18 Jan 2006 14:36:09 -0500
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit

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