insert_username

functions for tracking who changed a table

insert_username : functions for tracking who changed a table

Overview

ID	Extension	Package	Version	Category	License	Language
4882	insert_username	insert_username	`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 moddatetime 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 insert_username;

Usage

insert_username: track who modified a table row

Provides a trigger function that stores the current user’s name into a specified text column.

CREATE EXTENSION insert_username;

Trigger Function

Function	Description
`insert_username()`	Store current username in the specified column

Parameter: name of the text column to store the username.

Examples

CREATE TABLE audit_log (
  id serial PRIMARY KEY,
  data text,
  modified_by text
);

-- Track who inserts
CREATE TRIGGER set_insert_user
  BEFORE INSERT ON audit_log
  FOR EACH ROW
  EXECUTE FUNCTION insert_username('modified_by');

-- Track who updates
CREATE TRIGGER set_update_user
  BEFORE UPDATE ON audit_log
  FOR EACH ROW
  EXECUTE FUNCTION insert_username('modified_by');

INSERT INTO audit_log (data) VALUES ('test');
SELECT modified_by FROM audit_log;  -- returns current user