Re: Numeric to integer Type conversion

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