Hackers, Currently the only way that I know of to consume async notifications via SQL (as opposed to a client application) is via dblink_get_notify. This method isn't very good; it requires some extra support coding, eats a connection and a backend, and doesn't have any timeout facilities. The lack a good facility to do this will become more troublesome if/when Peter's recent fantastic work to implement stored procedures in the database gets accepted; asynchronous notifications could be a more efficient mechanic for backend processes to signal each other than the current method of signalling via fields in a table.
A good interface might look something like: pg_get_notifications( TimeOut INT DEFAULT 0, notify_name OUT TEXT, payload OUT TEXT, pid OUT INT) RETURNS SETF RECORD AS... The function would return immediately by default, or until TimeOut seconds transpired. We'd still have to poll internally, so that signals could be checked etc, but this would be a nice way to consume notifications without any dependencies -- what do you think? merlin