Thread: Overwriting Operator for numeric and float8 fails

Hi,

I've got a really stupid question. My application does queries like this:

SELECT t6.* FROM RECHNUNG  t6 WHERE t6.bruttosumm = 00000034.70 ORDER BY t6.nummer DESC

I get the following error: Unable to identify an operator '=' for types 'numeric' and 'float8'...
and of course the same for the operator '<>'. Ok, quotes missing.

Before I just let the ODBC-driver patch the quotes. Not so smart.
Now I tried the smarter way by overwriting the operator:

create function numeric_eq(numeric,float8)
returns bool
as ''
language 'internal';

create operator = (
leftarg=numeric,
rightarg=float8,
procedure=numeric_eq,
commutator='=',
negator='<>',
restrict=eqsel,
join=eqjoinsel
);

The Problem is: It doesn't work and the backend process crashes and get's restarted.
I tried it with 7.03 and I guess 7.1.2 behaves the same.
What am I doing wrong? Can somebody give me hint?

Thanks in advance


Johann Zuschlag
zuschlag@online.de

hey.

i have a question about 'LIKE', 'ILIKE'  and  the regular expression match
operators '~, ~*, !~, !~*'.  i realize the regular expression operators
are those set by the posix standard, but i dont believe they are part of
the sql standard. is 'LIKE'/'ILIKE' a standard sql operator?

i looked for a while but i could not find the actual sql standards.

if someone could point me to a website with the standards for sql that
would be great.

my concern here is portability of applications.

thanks
john

Johann Zuschlag wrote:
>
> Hi,
>
> I've got a really stupid question. My application does queries like this:
>
> SELECT t6.* FROM RECHNUNG  t6 WHERE t6.bruttosumm = 00000034.70 ORDER BY t6.nummer DESC
>

Can't you just force conversion of the constant to numeric?

SELECT t6.* FROM RECHNUNG  t6 WHERE t6.bruttosumm = numeric(00000034.70)
ORDER BY t6.nummer DESC

Cheers,
                    Andrew.
--
_____________________________________________________________________
        Andrew McMillan, e-mail: Andrew @ catalyst . net . nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(27)246-7091, Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709

Take a look at pgsql documentation...user guide, page 10-15 I think.
there's a list of SQL reserved words (if that's what you search)
-> LIKE is a SQL92 or SQL3 reserved word

Gaetan

Le Mardi 14 Août 2001 00:16, vous avez écrit :
> hey.
>
> i have a question about 'LIKE', 'ILIKE'  and  the regular expression match
> operators '~, ~*, !~, !~*'.  i realize the regular expression operators
> are those set by the posix standard, but i dont believe they are part of
> the sql standard. is 'LIKE'/'ILIKE' a standard sql operator?
>
> i looked for a while but i could not find the actual sql standards.
>
> if someone could point me to a website with the standards for sql that
> would be great.
>
> my concern here is portability of applications.
>
> thanks
> john
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

On Tue, 14 Aug 2001 16:24:43 +1200, Andrew McMillan wrote:

>> SELECT t6.* FROM RECHNUNG  t6 WHERE t6.bruttosumm = 00000034.70 ORDER BY t6.nummer DESC
>
>Can't you just force conversion of the constant to numeric?
>
>SELECT t6.* FROM RECHNUNG  t6 WHERE t6.bruttosumm = numeric(00000034.70)
>ORDER BY t6.nummer DESC
>
As I said, my application is doing it. Only thing I did, was patching the ODBC-driver
to put quotes. That is the same like your proposal. But since I can't change my app.
I'm, looking for a smarter way.

Thanks anyway

Johann

Johann Zuschlag
zuschlag@online.de