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@sss.pgh.pa.us>
To: "Mitch Vincent" <mitch@venux.net>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, January 23, 2001 11:05 AM
Subject: Re: [GENERAL] Another plpgsql question..


> "Mitch Vincent" <mitch@venux.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
>