Re: Dynamic update of a date field

On Thursday, February 16, 2012 6:05:40 am Musial, Jan (GIUB) wrote:
> Dear all,
>
> I have a question concerning default value/trigger function which supposed
> to update/fill field called time_stamp whenever a row is inserted. Let say
> that we have a table: CREATE TABLE dummy (year smallint,month smallint,day
> smallint,time_stamp date); I would like to update "time_stamp" dynamically
> without knowledge of a table name and using the values placed in the
> columns: year,month,day. The trick is that I have ~2000 tables which I
> populate with some time information, so either I could somehow fetch it
> to_timestamp() function in the Default definition of the field (while
> creating a table) or create a trigger function which doesn't require the
> table name (or retrieve it dynamically) and which is executed whenever a
> row is added to any table.
>
> As I am new to postgres/plpgsql any suggestions are more than welcome.

I would agree with Andreas, there is no need to replicate the date/time
information across fields. You can pull that information out of a timestamp. If
the timestamp field name is going to be the same on all the tables you can create
a generic function in plpgsl and point a trigger on each table to it. The INSERT
value can be handled by a default value on the field. It is the update you will
need a function for. So something like:

CREATE OR REPLACE FUNCTION public.ts_update()
RETURNS trigger AS

$Body$
BEGIN
new.ts_update:=now();
RETURN NEW;
END;
$Body$
LANGUAGE 'plpgsql' VOLATILE;

and associated trigger

CREATE TRIGGER some_table_ts_update
BEFORE UPDATE
ON some_table
FOR EACH ROW
EXECUTE PROCEDURE public.ts_update()

>
> Thank you in advance,
>
> Jan Musial

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com