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

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