Обсуждение: 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