From: | Falk Grossmann <fgrossmann(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | timestamp in log table after update in another table |
Date: | 2012-02-10 09:19:48 |
Message-ID: | CAFBdZX3_r1mue=fOaLnLjqAopv3D2CD0RMqa__mtKSUv4Ryaxg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi,
I have three tables which are frequently updated with new data. If any of
the tables are updated I need an automated way to create a timestamp
(indicating which unit_id has been updated/when) in a separate log table.
The tables have the following rows
Data tables (db_raw_data1,2,3):
aquis_data | aquis_time | unit_id | ... + a number of attributes specific
to each table.
Log table:
unit_id | timestamp| Unit_id is a primary key and = to unit_id in the
raw data table
My approach was to use a trigger in the data tables which would feed a
timestamp to the corresponding unit_id entry in the log table. I have been
thinking of a function along the following lines (which obviously hasn't
been successful):
I have googled this issue but haven't found a reference to updating a
timestamp in another table. Any suggestions?
CREATE OR REPLACE FUNCTION public."update_log_tbl"()
RETURNS TRIGGER AS
$$
BEGIN
UPDATE data.log
SET last_update = timestamp
WHERE db_raw_data.unit_id = log.unit_id;
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
ALTER FUNCTION public."log_tbl"()
OWNER TO postgres;
From | Date | Subject | |
---|---|---|---|
Next Message | Bartosz Dmytrak | 2012-02-10 15:07:03 | Re: timestamp in log table after update in another table |
Previous Message | Bartosz Dmytrak | 2012-02-09 21:06:11 | Re: Two Tables That Share Data? |