tcn
tcn
tcn : Triggered change notifications
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 4920 | tcn | tcn | 1.0 | FUNC | PostgreSQL | C |
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-dt- | No | Yes | No | Yes | no | yes |
| Relationships | |
|---|---|
| See Also | pg_idkit pgx_ulid pg_uuidv7 permuteseq pg_hashids sequential_uuids topn quantile |
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 tcn;Usage
Provides a trigger function that sends NOTIFY events with information about changed rows, enabling asynchronous change tracking.
CREATE EXTENSION tcn;Trigger Function
| Function | Description |
|---|---|
triggered_change_notification() | Send NOTIFY on row changes with primary key info |
Optional parameter: custom channel name (defaults to tcn).
Notification Payload Format
"table_name",operation,"column"='value',"column"='value'Operations: I (INSERT), U (UPDATE), D (DELETE).
Examples
CREATE TABLE tcndata (
a int NOT NULL,
b date NOT NULL,
c text,
PRIMARY KEY (a, b)
);
-- Attach the trigger
CREATE TRIGGER tcndata_tcn
AFTER INSERT OR UPDATE OR DELETE ON tcndata
FOR EACH ROW
EXECUTE FUNCTION triggered_change_notification();
-- Listen for notifications
LISTEN tcn;
-- Changes trigger notifications:
INSERT INTO tcndata VALUES (1, '2024-01-01', 'test');
-- Notification: "tcndata",I,"a"='1',"b"='2024-01-01'
UPDATE tcndata SET c = 'updated' WHERE a = 1;
-- Notification: "tcndata",U,"a"='1',"b"='2024-01-01'
DELETE FROM tcndata WHERE a = 1;
-- Notification: "tcndata",D,"a"='1',"b"='2024-01-01'
-- Use a custom channel name
CREATE TRIGGER my_trigger
AFTER INSERT OR UPDATE OR DELETE ON my_table
FOR EACH ROW
EXECUTE FUNCTION triggered_change_notification('my_channel');Last updated on