Thread: Sending email from PL/pgSQL

Sending email from PL/pgSQL

From
Jack Kaufman
Date:

PostgreSQL 9.2

Hello,

We are a new PostgreSQL site.  I want to send emails from within a PL/pgSQL function.  Email addresses, subject lines, etc., would be retrieved from fields in a record that had been previously populated by users in a GUI.

I have found the following file on the server:

/mes/mds/tinmon/proc/sendmail/sendmail.jar

I have used "sendmail" in the past but it was in different environment; and it wasn't a ".jar" file, either.  So I'm not sure how to proceed.  It doesn't have to be "sendmail", either.  I've used "mutt", too, but I can't find "mutt" on the PostgreSQL server.  Just about any email client would be fine.

Would someone provide an example of how to send email from a PL/pgSQL function?

Thanks in advance.

Jack


--
Jack Kaufman
MDS Application Devl (US)
Sanmina-SCI Corporation - Owego site
Email: jack.kaufman@sanmina.com
Skype: jack_kaufman_sanm
607-723-0507

CONFIDENTIALITY
This e-mail message and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail message, you are hereby notified that any dissemination, distribution or copying of this e-mail message, and any attachments thereto, is strictly prohibited.  If you have received this e-mail message in error, please immediately notify the sender and permanently delete the original and any copies of this email and any prints thereof.
ABSENT AN EXPRESS STATEMENT TO THE CONTRARY HEREINABOVE, THIS E-MAIL IS NOT INTENDED AS A SUBSTITUTE FOR A WRITING.  Notwithstanding the Uniform Electronic Transactions Act or the applicability of any other law of similar substance and effect, absent an express statement to the contrary hereinabove, this e-mail message its contents, and any attachments hereto are not intended to represent an offer or acceptance to enter into a contract and are not otherwise intended to bind the sender, Sanmina Corporation (or any of its subsidiaries), or any other person or entity.

Re: Sending email from PL/pgSQL

From
Alan Hodgson
Date:
On Wednesday, August 28, 2013 12:39:14 PM Jack Kaufman wrote:
> Would someone provide an example of how to send email from a PL/pgSQL
> function?
>
> Thanks in advance.
>
> Jack


I don't believe you can access anything external to PostgreSQL using PL/pgSQL.
You can create untrusted functions using other languages, though, like PL/Perl
or PL/Python, and use those languages' native libraries to send mail.

I wouldn't actually recommend doing so, though. I would suggest putting the
messages to be sent into a separate table and have an external script poll
that and send the mail as required.

Also, .jar files are for Java or Javascript, and are unlikely to have much to
do with your PostgreSQL installation.


Re: Sending email from PL/pgSQL

From
Andreas Kretschmer
Date:
Jack Kaufman <jack.kaufman@sanmina.com> wrote:

> Would someone provide an example of how to send email from a PL/pgSQL function?

You can't do that. Pl/pgsql is a so called 'trusted language', you can't
call functions outside the database. What you can do:

- fill a table with tasks (address, content and so) and use, for
  instance, a cron-job to retrieve data from this table, send the mail
  and delete the record
- use untrusted languages like pl/perlU, pl/sh or other
- use a listen/notify - mechanism to do that

Within a pl/pgsql - function all is in a transaction - but you can't fetch
back an email.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


Re: Sending email from PL/pgSQL

From
Jack Kaufman
Date:

Alan and Andreas,

Thank you!  You both said essentially the same thing:  That I will need to write the email attributes to a table and have a script on the server call the email client.  So that is the way I will proceed.  Thank you both for your replies and for helping me move along toward solving this problem.

Take care, Jack

On Wed, Aug 28, 2013 at 12:56 PM, Andreas Kretschmer <akretschmer@spamfence.net> wrote:

Jack Kaufman <jack.kaufman@sanmina.com> wrote:

> Would someone provide an example of how to send email from a PL/pgSQL function?

You can't do that. Pl/pgsql is a so called 'trusted language', you can't
call functions outside the database. What you can do:

- fill a table with tasks (address, content and so) and use, for
  instance, a cron-job to retrieve data from this table, send the mail
  and delete the record
- use untrusted languages like pl/perlU, pl/sh or other
- use a listen/notify - mechanism to do that

Within a pl/pgsql - function all is in a transaction - but you can't fetch
back an email.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice



--
Jack Kaufman
MDS Application Devl (US)
Sanmina-SCI Corporation - Owego site
Email: jack.kaufman@sanmina.com
Skype: jack_kaufman_sanm
607-723-0507

CONFIDENTIALITY
This e-mail message and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail message, you are hereby notified that any dissemination, distribution or copying of this e-mail message, and any attachments thereto, is strictly prohibited.  If you have received this e-mail message in error, please immediately notify the sender and permanently delete the original and any copies of this email and any prints thereof.
ABSENT AN EXPRESS STATEMENT TO THE CONTRARY HEREINABOVE, THIS E-MAIL IS NOT INTENDED AS A SUBSTITUTE FOR A WRITING.  Notwithstanding the Uniform Electronic Transactions Act or the applicability of any other law of similar substance and effect, absent an express statement to the contrary hereinabove, this e-mail message its contents, and any attachments hereto are not intended to represent an offer or acceptance to enter into a contract and are not otherwise intended to bind the sender, Sanmina Corporation (or any of its subsidiaries), or any other person or entity.

Re: Sending email from PL/pgSQL

From
Luca Ferrari
Date:
On Wed, Aug 28, 2013 at 9:03 PM, Jack Kaufman <jack.kaufman@sanmina.com> wrote:
> Alan and Andreas,
>
> Thank you!  You both said essentially the same thing:  That I will need to
> write the email attributes to a table and have a script on the server call
> the email client.  So that is the way I will proceed.  Thank you both for
> your replies and for helping me move along toward solving this problem.

Here you can find a very rudimental pl/perl stored procedure that will
send simple emails:
https://github.com/fluca1978/fluca1978-pg-utils/blob/master/bsdmag/03-server-side-programming/15-email-perl.sql

However, depending on the aim of your mail messaging, I would consider
having a shell script fecthing data out of PostgreSQL on a per-cron
basis and arranging a message to pipe thru a mail program (mutt,
sendmail, postfix, whatever). This can be much more general and will
let you reuse the mailing part for other jobs.

Luca


Re: Sending email from PL/pgSQL

From
Jack Kaufman
Date:
Luca,

Thank you for the information.  However, we do not have pl/perl (or another pl/ besides pgsql); so it would appear that a cron-controlled script is the way to go.

Thanks for the feedback.  Jack


On Thu, Aug 29, 2013 at 2:37 AM, Luca Ferrari <fluca1978@infinito.it> wrote:
On Wed, Aug 28, 2013 at 9:03 PM, Jack Kaufman <jack.kaufman@sanmina.com> wrote:
> Alan and Andreas,
>
> Thank you!  You both said essentially the same thing:  That I will need to
> write the email attributes to a table and have a script on the server call
> the email client.  So that is the way I will proceed.  Thank you both for
> your replies and for helping me move along toward solving this problem.

Here you can find a very rudimental pl/perl stored procedure that will
send simple emails:
https://github.com/fluca1978/fluca1978-pg-utils/blob/master/bsdmag/03-server-side-programming/15-email-perl.sql

However, depending on the aim of your mail messaging, I would consider
having a shell script fecthing data out of PostgreSQL on a per-cron
basis and arranging a message to pipe thru a mail program (mutt,
sendmail, postfix, whatever). This can be much more general and will
let you reuse the mailing part for other jobs.

Luca



--
Jack Kaufman
MDS Application Devl (US)
Sanmina-SCI Corporation - Owego site
Email: jack.kaufman@sanmina.com
Skype: jack_kaufman_sanm
607-723-0507

CONFIDENTIALITY
This e-mail message and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail message, you are hereby notified that any dissemination, distribution or copying of this e-mail message, and any attachments thereto, is strictly prohibited.  If you have received this e-mail message in error, please immediately notify the sender and permanently delete the original and any copies of this email and any prints thereof.
ABSENT AN EXPRESS STATEMENT TO THE CONTRARY HEREINABOVE, THIS E-MAIL IS NOT INTENDED AS A SUBSTITUTE FOR A WRITING.  Notwithstanding the Uniform Electronic Transactions Act or the applicability of any other law of similar substance and effect, absent an express statement to the contrary hereinabove, this e-mail message its contents, and any attachments hereto are not intended to represent an offer or acceptance to enter into a contract and are not otherwise intended to bind the sender, Sanmina Corporation (or any of its subsidiaries), or any other person or entity.