am Tue, dem 23.01.2007, um 23:47:08 -0800 mailte Saravanan Bellan folgendes: > We have database table with the following columns, > > foo > ------------------ > name VARCHAR(20) > bar NUMERIC(20,0) > > We were running version 7.2.1 until now. > > The following SQL used to work fine in 7.2.1, > > SELECT name FROM foo WHERE (bar & 64) <> 0; > > > Now we upgraded to version 8.1.5 and getting the error, > > ERROR: operator does not exist: numeric & integer > HINT: No operator matches the given name and argument type(s). You may > need to add explicit type casts. > > NUMERIC(20,0) is probably not the best way to define a column to be used > for bit arithmetic, but we cant change the column type because of > legacy. > > Is there anyway I can get the existing SQL to work without any changes > on the application side. Try to create a now operator like this: test=# create table foo (name varchar(20), bar NUMERIC(20,0)); CREATE TABLE test=*# commit; COMMIT test=# create function f_foo(numeric, int) returns int as $$ begin return ($1::int) & $2; end; $$ language 'plpgsql'; CREATE FUNCTION test=*# create operator & (leftarg = numeric, rightarg = int, procedure = f_foo, commutator = & ); CREATE OPERATOR test=*# SELECT name FROM foo WHERE (bar & 64) <> 0; name ------ (0 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
