Re: Another plpgsql question..
От | Mitch Vincent |
---|---|
Тема | Re: Another plpgsql question.. |
Дата | |
Msg-id | 010001c08559$d91a58e0$0200000a@windows обсуждение исходный текст |
Ответ на | Another plpgsql question.. ("Mitch Vincent" <mitch@venux.net>) |
Ответы |
Re: Another plpgsql question..
|
Список | 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@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 >
В списке pgsql-general по дате отправления: