pg_notify()

A function for sending notifications to other sessions

pg_notify() is a system function for sending notifications to other sessions connected to the same database listening on a channel specified with the LISTEN command.

pg_notify() was added in PostgreSQL 9.0.

Usage

pg_notify (text, text) → void

pg_notify() provides functionality equivalent to the NOTIFY command, but allows the channel name (first parameter) and (optional) message (second parameter) to be derived from a table, expression or other data source. It also allows multiple notifications to be sent with a single query.

See NOTIFY for additional details.

Change history

Examples

Basic pg_notify() usage example (for simplification showing the session listening on the same channel):

postgres=# LISTEN foo;
LISTEN

postgres=# SELECT pg_notify('foo', 'hello world');
 pg_notify 
-----------
 
(1 row)

Asynchronous notification "foo" with payload "hello world" received from server process with PID 4145118.

This is equivalent to executing NOTIFY 'foo', 'hello world'.

If the message is specified as NULL or '' (empty string), the notification will be sent without a message:

postgres=# LISTEN foo;
LISTEN

postgres=# LISTEN bar;
LISTEN

postgres=# SELECT pg_notify('foo', NULL), pg_notify('bar', '');
 pg_notify | pg_notify 
-----------+-----------
           | 
(1 row)

Asynchronous notification "foo" received from server process with PID 4145118.
Asynchronous notification "bar" received from server process with PID 4145118.

The above function invocations are equivalent to executing NOTIFY foo and NOTIFY bar in a transaction.

Sending messages generated from a table:

postgres=# CREATE TABLE notifications(channel NAME NOT NULL, message TEXT);
CREATE TABLE

postgres=# INSERT INTO notifications VALUES
           ('foo', 'bar'),
           ('foo', 'the date is ' || current_date);
INSERT 0 2

postgres=# SELECT pg_notify(channel, message) FROM notifications;
 pg_notify 
-----------
 
 
(2 rows)

Asynchronous notification "foo" with payload "bar" received from server process with PID 4154810.
Asynchronous notification "foo" with payload "the date is 2021-06-18" received from server process with PID 4154810.

Categories

Asynchronous notifications, System function

See also

NOTIFY, pg_listening_channels()