Hello David, hello Thomas,
a couple of weeks ago I had a similar problem, I solved it by a little
shell-script, (sorry, no pg-sql) which registers the missing operators.
As I thought it4s going to be fixed in the next version, I didn4t post
it. But untill then, perhaps it helps ....
First you need the int8cmp-Operator (I something like a
signature-function of the difference). Than you should register this
operator.
Kind regards
Michael Contzen
-------------- int8cmp.c ---------------
/*-------------------------------------------------------------------------
** int8cmp.c--* Internal 64-bit integer
operations**-------------------------------------------------------------------------
*/
#include <stdio.h> /* for sprintf proto,
etc. */
#include <stdlib.h> /* for strtod, etc. */
#include <string.h>
#include <ctype.h>
#include <time.h>
#include <math.h>
#include <float.h>
#include <limits.h>
#include "postgres.h"
#include "utils/palloc.h"
#define MAXINT8LEN 25
#if defined(__alpha) || defined(__GNUC__)
#define HAVE_64BIT_INTS 1
#endif
#ifndef HAVE_64BIT_INTS
typedef char[8] int64;
#elif defined(__alpha)
typedef long int int64;
#define INT64_FORMAT "%ld"
#elif defined(__GNUC__)
typedef long long int int64;
#define INT64_FORMAT "%Ld"
#else
typedef long int int64;
#define INT64_FORMAT "%ld"
#endif
int32 int8cmp(int64 * val1, int64 * val2);
int32
int8cmp(int64 * val1, int64 * val2)
{ if (*val1 > *val2) return 1; if (*val1 < *val2) return -1; return 0;
} /* int8cmp() */
------------------ End of int8cmp.c ------------------
------------------ initlongbtree.sh -----------------
initlongbtree.sh:
SQL="psql -t MYDATABASE "
OPS=`$SQL -c "SELECT am.amname, opc.opcname, opr.oprname FROM pg_am am, pg_amop amop, pg_opclass opc,
pg_operatoropr WHERE amop.amopid = am.oid AND amop.amopclaid = opc.oid AND amop.amopopr = opr.oid AND
opc.opcname='int8_ops' ORDER BY am.amname, opc.opcname"`
if [ "$OPS" != "" ]; thenecho "int8-btree ops already registerd."exit 0
fi
TYPE=`$SQL -c "select oid from pg_type where typname='int8'"`
echo "TYP=$TYPE"
if [ "$TYPE" = "" ]; thenecho "No int8-type found!"exit -1
fi
$SQL </usr/local/pgsql/lib/int8cmp.sql
$SQL -c "delete from pg_opclass where opcname='int8_ops'"
OPCLOID=`$SQL -c "insert into pg_opclass values('int8_ops', $TYPE)" |
cut -f2 -d" "`
OP1=`$SQL -c "select oid from pg_operator where oprname='<' and
oprleft=$TYPE and oprright=$TYPE"`
OP2=`$SQL -c "select oid from pg_operator where oprname='<=' and
oprleft=$TYPE and oprright=$TYPE"`
OP3=`$SQL -c "select oid from pg_operator where oprname='=' and
oprleft=$TYPE and oprright=$TYPE"`
OP4=`$SQL -c "select oid from pg_operator where oprname='>=' and
oprleft=$TYPE and oprright=$TYPE"`
OP5=`$SQL -c "select oid from pg_operator where oprname='>' and
oprleft=$TYPE and oprright=$TYPE"`
OP6=`$SQL -c "select oid from pg_proc where proname='int8cmp' "`
AMOID=`$SQL -c "select oid from pg_am where amname='btree'"`
echo "OPCLOID=$OPCLOID AMOID=$AMOID OP1..5: $OP1 $OP2 $OP3 $OP4 $OP5
cmp=$OP6"
if [ "$OP6" = "" ]; thenecho "No int8cmp found!"exit
fi
$SQL -c "insert into pg_amop values ($AMOID, $OPCLOID, $OP1, 1,
'btreesel', 'btreenpage')"
$SQL -c "insert into pg_amop values ($AMOID, $OPCLOID, $OP2, 2,
'btreesel', 'btreenpage')"
$SQL -c "insert into pg_amop values ($AMOID, $OPCLOID, $OP3, 3,
'btreesel', 'btreenpage')"
$SQL -c "insert into pg_amop values ($AMOID, $OPCLOID, $OP4, 4,
'btreesel', 'btreenpage')"
$SQL -c "insert into pg_amop values ($AMOID, $OPCLOID, $OP5, 5,
'btreesel', 'btreenpage')"
$SQL -c "insert into pg_amproc(amid, amopclaid, amproc, amprocnum) values ($AMOID, $OPCLOID, $OP6, 1)"
------------------- end initlongbtree.sh ---------------