"Eric D. Nielsen" <nielsene@MIT.EDU> writes:
> I'm in the process of implementing a "monitor this" type feature on a
> web-application. When something changes on the monitored item an email to the
> subscriber is generated. I'd like to do this via triggers instead of
> application logic. As far as I can tell pl/pgsql does not include any method
> for e-mailing as a function side-effect.
None of the "trusted" languages will allow anything like this for security
reasons. They have to be things that would be safe for a database admin to
allow untrusted users to use. You'll need to use something like PerlU or
PythonU.
> I guess I could alternatively just code up a simple mail function in another PL
> and then call that function from pl/pgsql. Is there any merit to this approach
> over the "whole-trigger" in another PL method?
Well depending on your application this may be a reasonable approach. However
you should at least think carefully before taking this route. It means the
email processing is put into the critical path of performing the original
update.
I would suggest you consider another model, where you have a second process
that connects to the database and checks for updates. It can either stay
connected all the time and the trigger can use NOTIFY to wake it up. Or it can
just check periodically. This has the advantage that you can write in any
language that has a postgres driver, including PHP.
It also means you can perform your database updates without having them depend
on some large external system. This is a big advantage. It means when the mail
system's borked you can keep your web application running and have it catch up
when things are fixed. And it means when things are slow or erroneous you have
one fewer moving parts to confuse you when debugging.
--
greg