Let’s suppose for a moment that I piped the output of a psql instance to awk or some similar program, configured to detect the NOTIFY. That program would then spawn a process to actually perform the work, parameters being whatever is part of the NOTIFY. Both this psql instance and the awk script would be dedicated to this task.
Given this is not intended in any way to be production quality code - in fact, it’s intended to deliver XML to the client server for validation (xmllint) in a development/test environment - do you see anything that clearly won’t work? Also, this would be a very low volume connection. Perhaps one NOTIFY in five minutes - or longer.
I've been curious about having a long-running psql instance that could be controlled by an external process (named pipes/fifos I believe). It seems like you might actually have a chance to get that working if you, 1, intend to perform the notification polling automatically and, 2, don't wish to lose any notifications (i.e. you must keep the psql process that issues LISTEN running continuously). It seems you'd actually need two of these since you don't actually want the output to be sent to stdout or a normal file but rather a file that is linked to the stdin of yet another long running process.
I believe *nix provides sufficient tools but whether psql is written to a sufficient level of compatibility to leverage them is something I don't know and, last time I mentioned this though, got one other person in the same boat (thoughtful but not worth the effort to investigate and R&D) and no one speaking up to claim they've done it already.
Given your rough description I'm not seeing why you wouldn't just have the trigger write a record out to some kind of journal table and poll that table for new records whenever you wish instead of depending upon LISTEN/NOTIFY.
