Skip to content

Instantly share code, notes, and snippets.

@2ec0b4
Created December 13, 2019 10:38
Show Gist options
  • Save 2ec0b4/52f8faed9208b2f3e9ea96fd68b9a812 to your computer and use it in GitHub Desktop.
Save 2ec0b4/52f8faed9208b2f3e9ea96fd68b9a812 to your computer and use it in GitHub Desktop.
PostgreSQL Notifier with a PHP Listener
<?php
set_time_limit(0);
$db = new PDO(
'pgsql:dbname=dbname host=host port=5432;options=--application_name=APPLICATION_NAME',
'user',
'password',
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
$db->exec('LISTEN channel_name');
while (true) {
while ($db->pgsqlGetNotify(PDO::FETCH_ASSOC, 30000)) {
echo json_encode($result).PHP_EOL;
}
}
CREATE OR REPLACE FUNCTION public.notify_channel()
RETURNS trigger
AS $function$
BEGIN
PERFORM pg_notify('channel_name', row_to_json(NEW)::text);
RETURN NULL;
END;
$function$
LANGUAGE plpgsql;
CREATE TRIGGER trigger_on_insert AFTER INSERT ON mytable
FOR EACH ROW EXECUTE PROCEDURE notify_channel();