How to listen from within a psql process and get the payloads?
Do Postgresql have any such a mechanism where in a session a process send a "listen <name>" sql command and then gets a message if someone in the other session issued a "notify <name>".
Please provide an example of how to do this.
I am looking for listen to return some thing when a notify has been issued.
Here is an example in perl. Basically you issue the LISTEN command. Then you sit and watch the file descriptor to become ready. You do that outside of a transaction. Once the descriptor is ready you call your driver's version of notification read function until it comes back empty. That is when you go back and wait for the next arrival.
$dbh->do("LISTEN channel"); my $sel = IO::Select->new; $sel->add($dbh->{pg_socket}); while ($sel->can_read) { while (my $notify = $dbh->pg_notifies) { my ($name, $pid, $payload) = @$notify; do_something($payload); } }