I’m sure most of you have been hearing about Node.js lately, it has been causing quite a buzz. If you’re wondering what Node.js is all about I recommend you watch one of Ryan Dahls presentations on the subject.
Interestingly Postgres has a feature that goes very well with the asynchronously nature of Node.js. The Postgres commands LISTEN and NOTIFY along with triggers you can have NOTIFY events fire when certain queries are performed on specific tables.
I wrote a short Node.js script that basically “watches” a table in a Postgres database. Say we have the following table:
We then create a function which will perform the notification to a channel we can later watch in our Node.js application.
The interesting line here is the call to
arguments here are the channel name to send the notification to and the
second part is the message. In this case the message consists of a
comma-separated string including the table name that the
notification came from (in our case this will be
bar) and then the id of the
NEW is the record that fired the trigger so we could use
NEW.name here as well to get the value of the column
channel-name can also consist of variables. For example if we had
pg_notify('watch_' || lower(NEW.name), 'id,' || NEW.id) and inserted a
new record with the column
name set to
bar it would notify
watch_bar with the message
X is the new
records id. This could be used to listen for insertions where some
column has a specific value. It is worth noting that the message part in
notifications was introduced in Postgres 9.x, 8.x will only support
notifying a channel without passing any message.
Some of you might have noticed that we need to do one more thing in Postgres to get this to work. We need to set up a trigger to fire this newly created function when a insert-query is performed on our table.
This will set up the postgres-client to listen on the
and when a notification comes in it will just print it to the console.
If you are trying this on your own machine you will probably have to
pgConString to reflect your Postgres-setup.
Comments? Questions? @bjorngylling