insert_username

functions for tracking who changed a table

https://www.postgresql.org/docs/current/contrib-spi.html#CONTRIB-SPI-INSERT-USERNAME

Overview

Package	Version	Category	License	Language
`insert_username`	`1.0`	FUNC	PostgreSQL	C

ID	Extension	Bin	Lib	Load	Create	Trust	Reloc	Schema
4882	`insert_username`	No	Yes	No	Yes	No	No	-

Related	`tcn` `refint` `autoinc` `moddatetime` `table_log`

Version

PG18	PG17	PG16	PG15	PG14
1.0	1.0	1.0	1.0	1.0

Install

Note: This is a built-in contrib extension of PostgreSQL

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

insert_username

Overview

Version

Install

Usage

Trigger Function

Examples

Was this page helpful?