Skip site navigation (1) Skip section navigation (2)

Re: timestamp in log table after update in another table

From: Bartosz Dmytrak <bdmytrak(at)eranet(dot)pl>
To: Falk Grossmann <fgrossmann(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: timestamp in log table after update in another table
Date: 2012-02-10 15:07:03
Message-ID: CAD8_UcZc6OKZmkbnxHuwS43AHSfRXB3YZwoX=tnjTFDEGZHQxw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,
You can use NEW record in trigger function e.g

CREATE OR REPLACE FUNCTION public."update_log_tbl"()
RETURNS TRIGGER AS
$$
BEGIN

UPDATE data.log
SET    last_update = now()
WHERE   unit_id = NEW.unit_id;

RETURN NEW;

end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

My assumptions are:

   1. trigger will be fired after INSERT and/or UPDATE, because for DELETE
   there is no NEW record, for DELETE should be modified;
   2. This is row level trigger (not statement);
   3. Trigger(s) will be defined on tables db_raw_data;
   4. You need log transaction start time not an time when record was
   modified during query execution. This is the difference between now() and
   clock_timestamp() functions;
   5. SECURITY INVOKER is intended - user who inserts/modify data
   in db_raw_data has to have privilages to modify data in data.log table (is
   is secure?).


NEW record represents inserted or modified record (new version) - is NULL
for DELETE
OLD record represents previous (existing) version of modified record - is
NULL for INSERT.

You can find it useful:
http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html


Regards,
Bartek


2012/2/10 Falk Grossmann <fgrossmann(at)gmail(dot)com>

> 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;
>

In response to

pgsql-novice by date

Next:From: Andy HalsallDate: 2012-02-10 17:07:21
Subject: Functions and query analysis - EXPLAIN
Previous:From: Falk GrossmannDate: 2012-02-10 09:19:48
Subject: timestamp in log table after update in another table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group