From: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
---|---|
To: | Postgresql Novice List <pgsql-novice(at)postgresql(dot)org> |
Subject: | function/trigger problems |
Date: | 2002-06-18 09:02:09 |
Message-ID: | 20020618090209.GA5600@campbell-lange.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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.
I'm trying to update the tstamp value in a whenever an insert is done
on b where b.a_id = a.id. So
INSERT INTO b (a_id) values (1)
should update the tstamp value in a using the trigger.
My function is failing at the 'WHERE' clause.
-----------------------------------------------------------------------
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" ();
COPY "a" FROM stdin;
1 2002-06-18 09:47:44.734527+01
2 2002-06-18 09:47:44.734527+01
COPY "b" FROM stdin;
1 1 2002-06-18 09:47:44.734527+01
--
Rory Campbell-Lange
<rory(at)campbell-lange(dot)net>
<www.campbell-lange.net>
From | Date | Subject | |
---|---|---|---|
Next Message | Rory Campbell-Lange | 2002-06-18 09:14:26 | function delete problems |
Previous Message | Tony Griffiths | 2002-06-18 08:24:14 | Client-side libraries for fmgr invocation |