[Fwd: How to use LISTEN / NOTIFY in a Perl program]

On Mon, Jan 03, 2005 at 09:43:20AM +0100, Thierry Missimilly wrote:

I have spend some time to read the very interesting feature LISTEN /
NOTIFY. I have found a lot of documentation on how to use in psql but
nothing on how to use in Perl DBI program. I don't know if it works.
I'll be very happy if someone had used it in a Perl script and send me
an example.

Here's a simple example. Set $dbsource, $dbuser, and $dbpass to
appropriate values and start the script, then open a separate
connection to the database and execute "NOTIFY foo". Each time you
do that, the script should print that it received a notification.

#!/usr/bin/perl -T

use strict;
use warnings;
use DBI;
use IO::Select;

$| = 1;

my $dbsource = "dbi:Pg:dbname=test";
my $dbuser = "testuser";
my $dbpass = "testpassword";
my $dbattr = {RaiseError => 1, AutoCommit => 1};

my $dbh = DBI->connect($dbsource, $dbuser, $dbpass, $dbattr);

$dbh->do("LISTEN foo");

my $fd = $dbh->func("getfd");
my $sel = IO::Select->new($fd);

while (1) {
print "waiting...";
$sel->can_read;
my $notify = $dbh->func("pg_notifies");
if ($notify) {
my ($relname, $pid) = @$notify;
my $row = $dbh->selectrow_hashref("SELECT now()");
print "$relname from PID $pid at $row->{now}\n";
}
}

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/