Thread: [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