pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

update Timestamp updated whenever the table is updated
2+ messages / 2 participants
[nested] [flat]

* update Timestamp updated whenever the table is updated
@ 2013-02-12 21:30  Campbell, Lance <lance@illinois.edu>
  0 siblings, 1 reply; 2+ messages in thread

From: Campbell, Lance @ 2013-02-12 21:30 UTC (permalink / raw)
  To: pgsql-admin

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]<http://illinois.edu/;

Attachments:

image003.png (image/png, 5.9K) download
  view image

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: update Timestamp updated whenever the table is updated
@ 2013-02-12 22:00  Kevin Grittner <kgrittn@ymail.com>
  parent: Campbell, Lance <lance@illinois.edu>
  0 siblings, 0 replies; 2+ messages in thread

From: Kevin Grittner @ 2013-02-12 22:00 UTC (permalink / raw)
  To: Campbell, Lance <lance@illinois.edu>; pgsql-admin

"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


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2013-02-12 22:00 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-12 21:30 update Timestamp updated whenever the table is updated Campbell, Lance <lance@illinois.edu>
2013-02-12 22:00 ` Kevin Grittner <kgrittn@ymail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox