Thread: Dynamic update of a date field

Dynamic update of a date field

From
"Musial, Jan (GIUB)"
Date:
Dear all,

I have a question concerning default value/trigger function which supposed to update/fill field called time_stamp
whenevera 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
couldsomehow fetch it to_timestamp() function in the Default definition of the field (while creating a table) or create
atrigger function which doesn't require the table name (or retrieve it dynamically) and which is executed whenever a
rowis added to any table. 

As I am new to postgres/plpgsql any suggestions are more than welcome.

Thank you in advance,

Jan Musial

Re: Dynamic update of a date field

From
Andreas Kretschmer
Date:
Musial, Jan (GIUB) <jan.musial@giub.unibe.ch> 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

you can use 'default now()' or an insert-trigger


> smallint,month smallint,day smallint,time_stamp date); I would like to

That's silly, use one (and only one) field, timestamp (or timestamptz)
Don't use never ever multiple columns for the same information!



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Dynamic update of a date field

From
Adrian Klaver
Date:
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@gmail.com

Re: Dynamic update of a date field

From
"Musial, Jan (GIUB)"
Date:
Dear Adrian & Andreas,

Thank you very much for this pieces of advice. I end up with creating a insert-triger function, which does the trick.
As far as the redundant time information within my database is concerned this is an data import issue. Simply it is
easierfor me to import year,month day separately than create a timestamp and drop the columns. 
All the best,

Jan
________________________________________
Von: Adrian Klaver [adrian.klaver@gmail.com]
Gesendet: Donnerstag, 16. Februar 2012 15:56
An: pgsql-general@postgresql.org
Cc: Musial, Jan (GIUB)
Betreff: Re: [GENERAL] 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.klaver@gmail.com