Thread: How to send an email when data is inserted into a table
Hi, I have a table which stores bugs which can be submitted by users of our client program. I would like a way that when a new bug is inserted an email is sent to the developers informing them of this. I tried using a python trigger function to execute sendmail and pipe SMTP headers to it but the problem seems to be that the python functions are interpretted in a restrictive environment and don't allow access to local resources (files /processes etc). When I execute the python function outside of postgres it works fine but not when used as a trigger functions. I know I can do it by writing a C function but I would rather not use C if possible in the case. Python would be easier to maintain. Any ideas? Do I have to use C or is there a way to do this from pyhton? Thanks in advance for any help, Nicholas Allen.
I'm using pgMail for this type of capability. Go to http://sourceforge.net and search for pgMail -----Original Message----- From: Nicholas Allen [mailto:Allen.Nicholas@lycos.co.uk] Sent: Thursday, May 22, 2003 8:42 AM To: pgsql-novice@postgresql.org Subject: [NOVICE] How to send an email when data is inserted into a table Hi, I have a table which stores bugs which can be submitted by users of our client program. I would like a way that when a new bug is inserted an email is sent to the developers informing them of this. I tried using a python trigger function to execute sendmail and pipe SMTP headers to it but the problem seems to be that the python functions are interpretted in a restrictive environment and don't allow access to local resources (files /processes etc). When I execute the python function outside of postgres it works fine but not when used as a trigger functions. I know I can do it by writing a C function but I would rather not use C if possible in the case. Python would be easier to maintain. Any ideas? Do I have to use C or is there a way to do this from pyhton? Thanks in advance for any help, Nicholas Allen. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Nicholas Allen <Allen.Nicholas@lycos.co.uk> writes: > Hi, > > I have a table which stores bugs which can be submitted by users of > our client program. I would like a way that when a new bug is > inserted an email is sent to the developers informing them of this. > > I tried using a python trigger function to execute sendmail and pipe > SMTP headers to it but the problem seems to be that the python > functions are interpretted in a restrictive environment and don't > allow access to local resources (files /processes etc). When I > execute the python function outside of postgres it works fine but > not when used as a trigger functions. > > I know I can do it by writing a C function but I would rather not > use C if possible in the case. Python would be easier to maintain. > > Any ideas? Do I have to use C or is there a way to do this from > pyhton? > > Thanks in advance for any help, > > Nicholas Allen. I do something similar, but I do it *outside* of PostgreSQL. I simply have a Python script that is started from cron. The fact of the matter is that you probably don't want to muck around with tricky Python (or C) trigger functions when you can write a simple script and launch it from cron. Not to mention the fact that with a script launched from cron you can let PostgreSQL's transactions work for you instead of against you. What happens to your Python trigger function if your system is unable to send mail, for example? Does the transaction get rolled back possibly losing you important trouble ticket information? With an external script these sorts of issues become easy to work with. Your trouble ticket inserts don't have to worry about sending mail. Your mailer script worries about that. Simply open a transaction, send your mail, and if you get an error you roll the transaction back and try to tell someone about it. Jason
> I have a table which stores bugs which can be submitted by users of our client > program. I would like a way that when a new bug is inserted an email is sent > to the developers informing them of this. > > I tried using a python trigger function to execute sendmail and pipe SMTP > headers to it but the problem seems to be that the python functions are > interpretted in a restrictive environment and don't allow access to local > resources (files /processes etc). When I execute the python function outside > of postgres it works fine but not when used as a trigger functions. > > I know I can do it by writing a C function but I would rather not use C if > possible in the case. Python would be easier to maintain. > > Any ideas? Do I have to use C or is there a way to do this from pyhton? Try, http://sourceforge.org/projects/pgmail/. pgMail is a small TCL based script that you can store within any PostgreSQL database. Once it is stored there, you can create any number of specialized procedures to send email from directly within PostgreSQL. i.e. emailing a customer when product ships regards, bhuvaneswaran