Re: plpgsql - cont'd

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(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 12:01 PM
Subject: Re: plpgsql - cont'd

> "Mitch Vincent" <mitch(at)venux(dot)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
>