moddatetime

functions for tracking last modification time

moddatetime : functions for tracking last modification time

Overview

ID	Extension	Package	Version	Category	License	Language
4883	moddatetime	moddatetime	`1.0`	FUNC	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s-d--	No	Yes	No	Yes	no	no

Relationships
See Also	tcn refint autoinc insert_username table_log

Packages

PG18	PG17	PG16	PG15	PG14
1.0	1.0	1.0	1.0	1.0

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

CREATE EXTENSION moddatetime;

Usage

moddatetime: track modification timestamp

Provides a trigger function that stores the current timestamp when a row is modified.

CREATE EXTENSION moddatetime;

Trigger Function

Function	Description
`moddatetime()`	Store current timestamp in the specified column on UPDATE

Parameter: name of the timestamp or timestamp with time zone column to update.

Examples

CREATE TABLE documents (
  id serial PRIMARY KEY,
  content text,
  modified_at timestamp with time zone
);

CREATE TRIGGER set_modified
  BEFORE UPDATE ON documents
  FOR EACH ROW
  EXECUTE FUNCTION moddatetime('modified_at');

UPDATE documents SET content = 'new content' WHERE id = 1;
-- modified_at is automatically set to current timestamp