Обсуждение: [repost] trigger update time

I hope I'm not transgressing the boundaries of list etiquette - I didn't
get a response to my first message so I've retitled it and resubmitted
it here.

I am trying to update a timestamp value in a related table using
functions and triggers, but failing miserably! I'd be grateful for some
help.

-----------------------------------------------------------------------

CREATE TABLE "a" (
    "id" SERIAL,
    "tstamp" timestamp
);

CREATE TABLE "b" (
    "id" SERIAL,
    "a_id" integer,
    "tstamp" timestamp default current_timestamp.
);


CREATE FUNCTION "fnTU" () RETURNS opaque AS 'BEGIN
UPDATE a SET tstamp = new.tstamp;
WHERE new.a_id = id;
RETURN new;
END' LANGUAGE 'plpgsql';

CREATE TRIGGER "trgTU" AFTER INSERT ON "b"  FOR EACH ROW EXECUTE
PROCEDURE "fnTU" ();


--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

On Wed, 19 Jun 2002 09:36:34 +0100, Rory Campbell-Lange
<rory@campbell-lange.net> wrote:
>I am trying to update a timestamp value in a related table using
>functions and triggers, but failing miserably!

Rory,

timestamp is not the problem.

>CREATE FUNCTION "fnTU" () RETURNS opaque AS 'BEGIN
>UPDATE a SET tstamp = new.tstamp;
>WHERE new.a_id = id;
>RETURN new;
>END' LANGUAGE 'plpgsql';
    ^
    Insert ; here!

Servus
 Manfred

Rory Campbell-Lange <rory@campbell-lange.net> writes:
> CREATE FUNCTION "fnTU" () RETURNS opaque AS 'BEGIN
> UPDATE a SET tstamp = new.tstamp;
                                  ^ remove this semicolon
> WHERE new.a_id = id;
> RETURN new;
> END' LANGUAGE 'plpgsql';

Depending on your PG version you might also need to add a semicolon
after END.

            regards, tom lane

On 19/06/02, Manfred Koizar (mkoi-pg@aon.at) wrote:
> On Wed, 19 Jun 2002 09:36:34 +0100, Rory Campbell-Lange
> <rory@campbell-lange.net> wrote:
> >I am trying to update a timestamp value in a related table using
> >functions and triggers, but failing miserably!
>
> timestamp is not the problem.
>
> >CREATE FUNCTION "fnTU" () RETURNS opaque AS 'BEGIN
> >UPDATE a SET tstamp = new.tstamp;
> >WHERE new.a_id = id;
> >RETURN new;
> >END' LANGUAGE 'plpgsql';
>     ^
>     Insert ; here!

Apologies and Thanks!, Manfred.

However I now get the following error:
obf=# insert into messages (id_person, id_idea, content) values
obf-# (5, 7, 'but all this txting is irritating!');
ERROR:  fmgr_info: function 51144: cache lookup failed

Is this an indexing problem?

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

Rory Campbell-Lange <rory@campbell-lange.net> writes:
> However I now get the following error:
> obf=# insert into messages (id_person, id_idea, content) values
> obf-# (5, 7, 'but all this txting is irritating!');
> ERROR:  fmgr_info: function 51144: cache lookup failed

If you drop and re-create a function then the new function is a new
object with a new OID, so triggers and other references to it will be
broken.  You need to drop and re-create the trigger too.

If you are using 7.2 or later then a better way is to redefine the
function with CREATE OR REPLACE FUNCTION.  This preserves the OID
and thus avoids breaking dependencies.

            regards, tom lane