From: | "Mitch Vincent" <mitch(at)venux(dot)net> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Another plpgsql question.. |
Date: | 2001-01-23 16:30:50 |
Message-ID: | 010001c08559$d91a58e0$0200000a@windows |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2001-01-23 16:37:39 | Re: Looking for info on Solaris 7 (SPARC) specific considerations |
Previous Message | Bruce Momjian | 2001-01-23 16:30:44 | Re: Outer Joins |