Re: Another plpgsql question..

Ok, it appears now I have an error.. Unless I'm going crazy, this started
after I had to do a restore because of one of those cache lookup errors.. I
changed nothing, still, this is what I get..

CREATE FUNCTION invoice_payment() RETURNS OPAQUE AS '
BEGIN

UPDATE invoice_master SET total = total - NEW.amount,updated = now(),
is_paid=(CASE WHEN (total - NEW.amount) = 0.00 THEN ''t'' ELSE ''f'' END)
WHERE invoice_id = NEW.invoice_id;

RETURN NEW;

END;
' LANGUAGE 'plpgsql';

Now I get

brw=# INSERT into invoice_payments VALUES
(1,1000,'now',100,'now',100,1,1,150.00);
ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'
You will have to retype this query using an explicit cast
DEBUG: Last error occured while executing PL/pgSQL function invoice_payment
DEBUG: line 2 at SQL statement
ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'
You will have to retype this query using an explicit cast
brw=#

--- amount and total are both numeric(9,2), I've tried casting everything
(total,amount and 0.00) to float and everything to numeric with the same
error popping up.. What needs casting here?

I can determine if an invoice has been paid or not a number of ways, really
what I should do there is NEW.amount >= total -- I tried and got the above
error again..

Thanks!

-Mitch

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mitch Vincent" <mitch(at)venux(dot)net>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, January 23, 2001 11:05 AM
Subject: Re: [GENERAL] Another plpgsql question..

> "Mitch Vincent" <mitch(at)venux(dot)net> writes:
> > is_paid is never updated...
>
> It's not possible that is_paid is never updated; that command *will*
> replace the total, updated, and is_paid columns with *something*.
> It may be that in the cases you've checked, it gets updated to the
> same value it had before. That's why I want to see the test cases.
>
> regards, tom lane
>