Search Postgresql Archives

Re: Dynamic update of a date field

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux