As someone else pointed out you can upgrade to 7.1. Or you can add your
own operators/functions.
I've included the C source and SQL create script. You'll need to do this
as the postgres superuser. And you'll probably need to edit the SQL
script and adjust the path of the .so file. The .c file has what I use
for compilation (you'll need to tweak that as well).
The only gotcha is that you can't remove the NOT operator (it's a bug in
7.0.3) using the DROP OPERATOR statement.
-philip
On Mon, 2 Apr 2001, Alexander Lohse wrote:
> Hi,
>
> I am having serious troubles to move my DBs from MySQL to Postgres.
>
> In MySQL I use following bitwise operation on an INT field:
>
> $q = "select id from my_tbl where aid & 2";
>
> In Postgres I tried the same, but I found no working solution.
>
> My basic questions:
>
> Is it better(or even possible) to use bitwise operator on an int4
> field or shall I prefer the varbit?
> (In my logic I would prefer int4!)
>
> Using the above as in MySQL, I get an ERROR:
> WHERE clause must return type bool, not type int4
>
> Can anyone point me to a right doc or kickstart myself?
>
> Thanx,
>
> Alex
>
>
>
> --
> ___________________________
> Alexander Lohse
> Human Touch Medienproduktion GmbH
> Am See 1
> 17440 Klein Jasedow
>
> Tel: (038374) 75211
> Fax: (038374) 75223
> eMail: [EMAIL PROTECTED]
> http://www.humantouch.de
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>
/*
gcc -I/local/src/postgresql-7.0.3/src/include
-I/local/src/postgresql-7.0.3/src/backend \
-O2 -m486 -pipe -Wall -Wmissing-prototypes -Wmissing-declarations
-I/local/src/postgresql-7.0/src/interfaces/libpq \
-I/local/src/postgresql-7.0/src/include -fpic -DPIC -c -o \
XXX.o XXX.c
ld -x -shared -o XXX.so XXX.o
rm XXX.o
*/
#include <stdio.h>
#include "postgres.h"
#include "utils/builtins.h"
int am_int_bit_and(int, int);
int am_int_bit_or(int, int);
int am_int_bit_xor(int, int);
int am_int_bit_not(int);
/* $a & $b And Bits that are set in both $a and $b are set. */
int am_int_bit_and(int arg1, int arg2) {
return(arg1 & arg2);
}
/* $a | $b Or Bits that are set in either $a or $b are set. */
int am_int_bit_or(int arg1, int arg2) {
return(arg1 | arg2);
}
/* $a ^ $b Xor Bits that are set in $a or $b but not both are set. */
int am_int_bit_xor(int arg1, int arg2) {
return(arg1 ^ arg2);
}
/* ~ $a Not Bits that are set in $a are not set, and vice versa. */
int am_int_bit_not(int arg1) {
return(~ arg1);
}
DROP OPERATOR & (INT2, INT2);
DROP OPERATOR | (INT2, INT2);
DROP OPERATOR ^ (INT2, INT2);
DROP OPERATOR ~ (NONE, INT2);
DROP OPERATOR & (INT4, INT4);
DROP OPERATOR | (INT4, INT4);
DROP OPERATOR ^ (INT4, INT4);
DROP OPERATOR ~ (NONE, INT4);
DROP FUNCTION am_int_bit_and(INT2, INT2);
DROP FUNCTION am_int_bit_or(INT2, INT2);
DROP FUNCTION am_int_bit_xor(INT2, INT2);
DROP FUNCTION am_int_bit_not(INT2);
DROP FUNCTION am_int_bit_and(INT4, INT4);
DROP FUNCTION am_int_bit_or(INT4, INT4);
DROP FUNCTION am_int_bit_xor(INT4, INT4);
DROP FUNCTION am_int_bit_not(INT4);
------------------------------------------------------------------------
CREATE FUNCTION am_int_bit_and(int2, int2) RETURNS int2
AS '/local/www/sites/odin/sql/int_bit_operators/am_int_bit_operators.so',
'am_int_bit_and'
LANGUAGE 'C';
CREATE FUNCTION am_int_bit_or(int2, int2) RETURNS int2
AS '/local/www/sites/odin/sql/int_bit_operators/am_int_bit_operators.so',
'am_int_bit_or'
LANGUAGE 'C';
CREATE FUNCTION am_int_bit_xor(int2, int2) RETURNS int2
AS '/local/www/sites/odin/sql/int_bit_operators/am_int_bit_operators.so',
'am_int_bit_xor'
LANGUAGE 'C';
CREATE FUNCTION am_int_bit_not(int2) RETURNS int2
AS '/local/www/sites/odin/sql/int_bit_operators/am_int_bit_operators.so',
'am_int_bit_not'
LANGUAGE 'C';
------------------------------------------------------------------------
CREATE OPERATOR & (
LEFTARG = INT2,
RIGHTARG = INT2,
PROCEDURE = am_int_bit_and
);
CREATE OPERATOR | (
LEFTARG = INT2,
RIGHTARG = INT2,
PROCEDURE = am_int_bit_or
);
CREATE OPERATOR ^ (
LEFTARG = INT2,
RIGHTARG = INT2,
PROCEDURE = am_int_bit_xor
);
CREATE OPERATOR ~ (
RIGHTARG = INT2,
PROCEDURE = am_int_bit_not
);
-----------------------------------------------------------------
DROP AGGREGATE am_abitor INT2;
DROP AGGREGATE am_abitand INT2;
DROP AGGREGATE am_abitxor INT2;
DROP AGGREGATE am_abitor INT4;
DROP AGGREGATE am_abitand INT4;
DROP AGGREGATE am_abitxor INT4;
CREATE AGGREGATE am_abitor ( BASETYPE = INT2, SFUNC1 = am_int_bit_or, STYPE1 = INT2);
CREATE AGGREGATE am_abitand ( BASETYPE = INT2, SFUNC1 = am_int_bit_and, STYPE1 =
INT2);
CREATE AGGREGATE am_abitxor ( BASETYPE = INT2, SFUNC1 = am_int_bit_xor, STYPE1 =
INT2);
CREATE AGGREGATE am_abitor ( BASETYPE = INT4, SFUNC1 = am_int_bit_or, STYPE1 = INT4);
CREATE AGGREGATE am_abitand ( BASETYPE = INT4, SFUNC1 = am_int_bit_and, STYPE1 =
INT4);
CREATE AGGREGATE am_abitxor ( BASETYPE = INT4, SFUNC1 = am_int_bit_xor, STYPE1 =
INT4);
-----------------------------------------------------------------
CREATE FUNCTION am_int_bit_and(int4, int4) RETURNS int4
AS '/local/www/sites/odin/sql/int_bit_operators/am_int_bit_operators.so',
'am_int_bit_and'
LANGUAGE 'C';
CREATE FUNCTION am_int_bit_or(int4, int4) RETURNS int4
AS '/local/www/sites/odin/sql/int_bit_operators/am_int_bit_operators.so',
'am_int_bit_or'
LANGUAGE 'C';
CREATE FUNCTION am_int_bit_xor(int4, int4) RETURNS int4
AS '/local/www/sites/odin/sql/int_bit_operators/am_int_bit_operators.so',
'am_int_bit_xor'
LANGUAGE 'C';
CREATE FUNCTION am_int_bit_not(int4) RETURNS int4
AS '/local/www/sites/odin/sql/int_bit_operators/am_int_bit_operators.so',
'am_int_bit_not'
LANGUAGE 'C';
CREATE OPERATOR & (
LEFTARG = INT4,
RIGHTARG = INT4,
PROCEDURE = am_int_bit_and
);
CREATE OPERATOR | (
LEFTARG = INT4,
RIGHTARG = INT4,
PROCEDURE = am_int_bit_or
);
CREATE OPERATOR ^ (
LEFTARG = INT4,
RIGHTARG = INT4,
PROCEDURE = am_int_bit_xor
);
CREATE OPERATOR ~ (
RIGHTARG = INT4,
PROCEDURE = am_int_bit_not
);
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
