My concern is how, after LISTENing in psql, I can tell it what to do when the NOTItFY is received.
As far as I am aware you cannot. The docs for psql, and its feature set, with respect to LISTEN, are minimal and basically say psql will print out notifications to stdout (this I'm forced to assume or read the code) and will poll for notifications whenever it sends a query to the server.
I’ve tested using LISTEN and NOTIFY in psql. First I issued the LISTEN, then when my prompt came back, issued the NOTIFY from psql. This is an example from the psql docs, I believe. In any case, that the NOTIFY had occurred was reported in the psql window.
"Whenever a command is executed, psql also polls for asynchronous notification events generated by LISTEN and NOTIFY."
I suspect the feature request would be something like:
\set NOTIFY_PROGRAM './process-notify-request.bash' (or an equivalent meta-command)
Since the database would issue the NOTIFY, not sure \set would be used, but otherwise, yes. Or, I may just send information about the inserted row. It depends on what psql would need.
And psql would invoke said program and pass the content of the notification payload to it via stdin.
I’d rather have psql know the program name to invoke, but since I don’t really know how all this works, I may have no choice.
Given what we allow for "\copy" I don't see any technical or conceptual problems with such a feature. Just needs someone to be its primary author.
