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.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
