Here’s a cohesive example that registers an interest in a table insertion, waits for notification (or timeout) and responds to the caller. We use a timestamp preceded by the letter ‘C’ to identify the notification channel since Postgres requires the channel name to be a proper identifier.
Postgres SQL
/* We want to know when items of interest get added to this table.
Asynchronous insertions possible from different process or server */
DROP TABLE IF EXISTS History;
CREATE TABLE History (
HistoryId INT PRIMARY KEY,
MYKEY CHAR(17),
Description TEXT,
TimeStamp BIGINT
);
/* Table of registered interest in a notification */
DROP TABLE IF EXISTS Notifications;
CREATE TABLE Notifications (
NotificationId INT PRIMARY KEY,
Channel VARCHAR(20),
MYKEY CHAR(17)
);
/* Function to process a single insertion to History table */
CREATE OR REPLACE FUNCTION notify_me()
RETURNS trigger AS
$BODY$
DECLARE ch varchar(20);
BEGIN
FOR ch IN
SELECT DISTINCT Channel FROM Notifications
WHERE MYKEY=NEW.MYKEY
LOOP
/* NOTIFY ch, 'from notify_me trigger'; */
EXECUTE 'NOTIFY C' || ch || ', ' || quote_literal('from notify_me') || ';';
DELETE FROM Notifications WHERE Channel=ch;
END LOOP;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql';
/* Trigger to process all insertions to History table */
DROP TRIGGER IF EXISTS HistNotify ON History CASCADE;
CREATE TRIGGER HistNotify AFTER INSERT ON History
FOR EACH ROW EXECUTE PROCEDURE notify_me();
PHP code
// $conn is a PDO connection handle to the Postgres DB
// $MYKEY is a key field of interest
$TimeStamp = time(); // UNIX time (seconds since 1970) of the request
$timeout = 120; // Maximum seconds before responding
// Register our interest in new history log activity
$rg = $conn->prepare("INSERT INTO Notifications (MYKEY, Channel) VALUES (?,?)");
$rg->execute(array($MYKEY, $TimeStamp));
// Wait until something to report
$conn->exec('LISTEN C'.$TimeStamp.';'); // Prepend ‘C’ to get notification channel
$conn->exec('COMMIT;'); // Postgres may need this to start listening
$conn->pgsqlGetNotify (PDO::FETCH_ASSOC, $timeout*1000); // Convert from sec to ms
// Unregister our interest
$st = $conn->prepare("DELETE FROM Notifications WHERE Channel=?");
$st->execute(array($TimeStamp));
