Обсуждение: update Timestamp updated whenever the table is updated

Поиск
Список
Период
Сортировка

update Timestamp updated whenever the table is updated

От
"Campbell, Lance"
Дата:

PostgreSQL 9.2.3

I would like to have a generic strategy for updating a timestamp field on some tables whenever the table is updated.  Is there a recommended strategy for doing this other than via the SQL UPDATE command?  I think I read something about RULES.  In the below example I have a table called test_table.  I would ideally like to update the field called “updated_timestamp” whenever an update occurs by doing:

 

UPDATE test_table SET updated_timestamp=now() WHERE id=?

 

While looking at the concept of RULES how do I say after an update happens update that particular record and not all of the records in test_table.

 

Example table:

CREATE TABLE test_table

(

  id integer NOT NULL,

  field1 character varying NOT NULL,

  field2 character varying NOT NULL,

  updated_timestamp timestamp with time zone DEFAULT now(),

  created_timestamp timestamp with time zone DEFAULT now()

);

 

 

Thanks,

 

Lance Campbell

Software Architect

Web Services at Public Affairs

217-333-0382

University of Illinois at Urbana-Champaign logo

 

 

Вложения

Re: update Timestamp updated whenever the table is updated

От
Kevin Grittner
Дата:
"Campbell, Lance" <lance@illinois.edu> wrote:

> I would like to have a generic strategy for updating a timestamp
> field on some tables whenever the table is updated.  Is there a
> recommended strategy for doing this other than via the SQL UPDATE
> command?

> Example table:
> CREATE TABLE test_table
> (
>   id integer NOT NULL,
>   field1 character varying NOT NULL,
>   field2 character varying NOT NULL,
>   updated_timestamp timestamp with time zone DEFAULT now(),
>   created_timestamp timestamp with time zone DEFAULT now()
> );

A BEFORE UPDATE trigger is probably what you want.  You could write
a single trigger function which could be attached to all tables
with the updated_timestamp column.  For example:

CREATE FUNCTION set_updated_timestamp()
  RETURNS TRIGGER
  LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_timestamp := now();
  RETURN NEW;
END;
$$;

Note that the above function depends on a column name, but not a
table name.  You link it to each table like this:

CREATE TRIGGER test_table_update_timestamp
  BEFORE UPDATE ON test_table
  FOR EACH ROW EXECUTE PROCEDURE set_updated_timestamp();

> I think I read something about RULES.

You generally want to avoid RULES, especially where a trigger works
so well.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company