Hi all;
A couple points here.
First, you probably don't want to send email directly from a database function. This gives significant problems for which there is no good solution. Consider:
1) You sent your email and now the transaction rolls back. You *cannot* roll back the sent email.
2) Your email fails to send. Do you abort the transaction?
IMO it is always better to send email from a second process that can be notified on db commit. This avoids these issues and kicks them to a post-transaction handler.
There isn't a lot of overlap with something like pgq. This is listen/notify/queue tables based. May not ever be big and professional but it should work once the bugs are ironed out. Even before then it may give a good idea of how to implement a notification-based queue on PostgreSQL.
The idea here is that you can essentially send a message to a channel on a db event (say, from a trigger) and then have another app that either periodically checks the queue (say, from a cron job) or listens on a channel for notifications.
The whole thing was confirmed working before I made some changes. If folks are interested in helping I am sure it will be well tested and working in no time. Please read the docs first though. I wouldn't say it is production-ready yet, but it may provide an overview of how to go about implementing something like this in production.
In general I think mixing transactional and non-transactional side-effects is just asking for trouble. Don't do it any more than you have to.
Best Wishes,
Chris Travers