Ok, after some more playing, this works.
CREATE FUNCTION invoice_payment() RETURNS OPAQUE AS '
BEGIN
UPDATE invoice_master SET total = total - NEW.amount,updated = now(),
is_paid=(CASE WHEN total::numeric = NEW.amount::numeric THEN TRUE::bool ELSE
FALSE::bool END) WHERE invoice_id = NEW.invoice_id;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
Thanks again for answering my stupid little questions, Tom :-)
-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 12:01 PM
Subject: Re: plpgsql - cont'd
> "Mitch Vincent" <mitch@venux.net> writes:
> > Hmm, this is the third time this has happened.. I am using 7.1 Bert 3,
so I
> > expected some things like this... When deleting and adding functions
back,
> > when I run them I get :
>
> > ERROR: plpgsql: cache lookup for proc 49237 failed
>
> > -- What's happening there and is there anyway to fix this without having
to
> > dump/restore (which is what I've had to do thus far.. ) ?
>
> dump/restore is the hard way. If you delete and recreate a function,
> the new incarnation has a new OID, so anything that referred to the old
> OID is now broken, and has to be deleted/recreated itself. Triggers
> are one such reference. Cached query plans are another, although I
> don't think that's the issue here. How are you invoking the functions,
> anyway?
>
> regards, tom lane
>
