Another plpgsql question..

"Mitch Vincent" <mitch@venux.net> writes:

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';

Ok, thanks for the heads up on now().. That works but I add the last part of
the query in there and everything but the last part works.. Can I not do
CASE WHEN statements in functions like that? That query works when I run it
straight from psql but so did the other, so I'm wondering if the same kind
of problem is happening here too..

It looks fine to me. Please define "everything but the last part works"
more precisely.

regards, tom lane

is_paid is never updated...

-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 10:56 AM
Subject: Re: [GENERAL] Another plpgsql question..

"Mitch Vincent" <mitch@venux.net> writes:

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';

Ok, thanks for the heads up on now().. That works but I add the last

part of

the query in there and everything but the last part works.. Can I not do
CASE WHEN statements in functions like that? That query works when I run

it

straight from psql but so did the other, so I'm wondering if the same

kind

"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

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:

ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'
You will have to retype this query using an explicit cast

--- amount and total are both numeric(9,2),

Hm, then "(total - NEW.amount) = 0.00::numeric" should work, although
I don't see why you don't just write it as "total = NEW.amount".

regards, tom lane