Need psql send email

Check this article:
http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/

2012/9/20 pavithra <pavithra.ibt@gmail.com>

Hi All, I am new to postgresql. I want to send email by using pl pgsql. I
want to know how to set up the configurations for mail server. Can any one
help me in solving this?. [hidden email]<http://user/SendEmail.jtp?type=node&amp;node=5724700&amp;i=0&gt;

--
Victor Y. Yegorov

On 20/09/2012 13:07, pavithra wrote:

Hi All, I am new to postgresql. I want to send email by using pl pgsql.
I want to know how to set up the configurations for mail server. Can any
one help me in solving this?. [hidden email]

Hi there,

It's not possible to send email directly from pl/pgsql; it might be
possible in the untrusted form of pl/perl, but I'm not sure.

A possible alternative would be to have an external process poll a queue
table, take its data from there and send the emails.

HTH,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On Thu, Sep 20, 2012 at 05:07:18AM -0700, pavithra wrote:

Hi All,I am new to postgresql. I want to send email by using pl pgsql. I want
to know how to set up the configurations for mail server.Can any one help me
in solving this?. pavithra.ibt@gmail.com

http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

I am more wondered where we need to give the port address and smtpserver.

Can you give me the details of these?.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Need-psql-send-email-tp5724700p5724705.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi All,I am new to postgresql. I want to send email by using pl

pgsql. I want

to know how to set up the configurations for mail server.Can any one

help me

in solving this?. pavithra.ibt@gmail.com

http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/

Best regards,

depesz

Alternatively:

CREATE OR REPLACE FUNCTION sendmail(p_from text, p_to text, p_subject
text, p_content text)
RETURNS void AS
$BODY$
use strict;
use warnings;
my ($from, $to, $subject, $content) = @_;

open(MAIL, "|/usr/sbin/sendmail -t") or die 'Cannot send mail';
print MAIL "From: $from\n";
print MAIL "To: $to\n";
print MAIL "Subject: $subject\n\n";
print MAIL "$content";

close(MAIL);
$BODY$
LANGUAGE plperlu;

Works ok provided sendmail is configured.

or:

CREATE OR REPLACE FUNCTION send_smtp(p_mail_host text,
p_from text,
p_to text,
p_subject text,
p_content text,
p_timeout integer DEFAULT 60,
p_debug integer DEFAULT 0,
p_exactaddr integer DEFAULT 1,
p_skipbad integer DEFAULT 1)
RETURNS void AS
$BODY$
use strict;
use warnings;
use Net::SMTP;
no strict 'refs';

my ($host, $sender, $recipient, $subject, $body, $timeout, $debug, $exact,
$skipbad) = @_;
(!defined($host) || !($host)) && die 'No SMTP host provided.';
(!defined($sender) || !($sender)) && die 'No sender address/name
provided.';
(!defined($recipient) || !($recipient)) && die 'No recipient address
specified.';

my $mail = Net::SMTP->new(
Host => $host,
Debug => $debug,
Timeout => $timeout,
ExactAddresses => $exact
) or die 'Net::SMTP->new() Failed';

$mail->mail($sender);
$mail->recipient($recipient, { SkipBad => $skipbad });

$mail->data();
$mail->datasend("MIME-Version: 1.0\n");
$mail->datasend("From:" . $sender . "\n");
$mail->datasend("To:" . $recipient . "\n");
$mail->datasend("Reply-To: ". $sender . "\n");
$mail->datasend("Subject:" . $subject . "\n\n");
$mail->dataend();
$mail->quit();
$BODY$
LANGUAGE plperlu;

Feel free to hack away as much as required.

Both of these work fine provided PL/PerlU is installed and the server is
properly configured on the network, and that there is a valid SMTP mail
host to receive.

Cheers

Martin

=============================================

Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England

Telephone numbers:
+44 (0)115 951 88 00 (main)

For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================

================
Confidentiality Statement
This transmission is for the addressee only and contains information that
is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf
of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your
system and contact the sender. Thank you for your cooperation.
=================================================

On 09/20/2012 08:40 PM, Martin French wrote:

Both of these work fine provided PL/PerlU is installed and the server is
properly configured on the network, and that there is a valid SMTP mail
host to receive.

The 1st one seems OK in a scary-from-a-security-standpoint kind of way.

The 2nd, not so much. See
http://stackoverflow.com/questions/12002662/psql-trigger-send-email

Imagine if the DNS goes wonky. Do you want all your backends tied up in
DNS lookups? Or timing-out TCP connections?

BTW, pavithra, check out http://brandolabs.com/pgmail if you really want
to do it in the database.

--
Craig Ringer

many is the time when spammers have used Open Relay SMTP servers to send their junk mail so i would advise against using sendmail on Open Relay SMTP servers
I would narrow access by SSH or open a secure tunnel thru your firewall to your own internal DatabaseManagementSystem/J2EEServer/ApacheHTTPServer then allow
those scripts (PL-SQL or Perl or Java) to invoke sendmail to the SMTPMailServer inside the firewall

If you are sponsoring your own email-server and I hope you are DISALLOW OPEN RELAY
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

To: depesz@depesz.com
CC: pavithra.ibt@gmail.com; pgsql-general@postgresql.org; pgsql-general-owner@postgresql.org
Subject: Re: [GENERAL] Need psql send email
From: Martin.French@romaxtech.com
Date: Thu, 20 Sep 2012 13:40:58 +0100

Hi All,I am new to postgresql. I want to send email by using

pl

pgsql. I want

to know how to set up the configurations for mail server.Can

any one help me

in solving this?. pavithra.ibt@gmail.com

http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/

Best regards,

depesz

Alternatively:

CREATE OR REPLACE FUNCTION sendmail(p_from text, p_to
text, p_subject text, p_content text)

RETURNS void AS

$BODY$

use strict;

use warnings;

my ($from, $to, $subject, $content) = @_;

open(MAIL, "|/usr/sbin/sendmail -t") or
die 'Cannot send mail';

print MAIL "From: $from\n";

print MAIL "To: $to\n";

print MAIL "Subject: $subject\n\n";

print MAIL "$content";

close(MAIL);

$BODY$

LANGUAGE plperlu;

Works ok provided sendmail is configured.

or:

CREATE OR REPLACE FUNCTION send_smtp(p_mail_host text,

p_from text,

p_to text,

p_subject text,

p_content text,

p_timeout integer
DEFAULT 60,

p_debug integer
DEFAULT 0,

p_exactaddr integer
DEFAULT 1,

p_skipbad integer
DEFAULT 1)

RETURNS void AS

$BODY$

use strict;

use warnings;

use Net::SMTP;

no strict 'refs';

my ($host, $sender, $recipient, $subject, $body, $timeout,
$debug, $exact, $skipbad) = @_;

(!defined($host) || !($host)) && die 'No SMTP
host provided.';

(!defined($sender) || !($sender)) && die
'No sender address/name provided.';

(!defined($recipient) || !($recipient)) &&
die 'No recipient address specified.';

my $mail = Net::SMTP->new(

Host => $host,

Debug => $debug,

Timeout => $timeout,

ExactAddresses => $exact

) or die 'Net::SMTP->new()
Failed';

$mail->mail($sender);

$mail->recipient($recipient, { SkipBad => $skipbad
});

$mail->data();

$mail->datasend("MIME-Version: 1.0\n");

$mail->datasend("From:" . $sender . "\n");

$mail->datasend("To:" . $recipient .
"\n");

$mail->datasend("Reply-To: ". $sender
. "\n");

$mail->datasend("Subject:" . $subject
. "\n\n");

$mail->dataend();

$mail->quit();

$BODY$

LANGUAGE plperlu;

Feel free to hack away as much as required.

Both of these work fine provided PL/PerlU is installed and the server is
properly configured on the network, and that there is a valid SMTP mail
host to receive.

Cheers

Martin

=============================================

Romax Technology Limited

Rutherford House

Nottingham Science & Technology Park

Nottingham,

NG7 2PZ

England

Telephone numbers:

+44 (0)115 951 88 00 (main)

For other office locations see:

http://www.romaxtech.com/Contact

=================================

===============

E-mail: info@romaxtech.com

Website: www.romaxtech.com

=================================

================

Confidentiality Statement

This transmission is for the addressee only and contains information that
is confidential and privileged.

Unless you are the named addressee, or authorised to receive it on behalf
of the addressee

you may not copy or use it, or disclose it to anyone else.

If you have received this transmission in error please delete from your
system and contact the sender. Thank you for your cooperation.

=================================================

The 1st one seems OK in a scary-from-a-security-standpoint kind of way.

Agree, it needs to be weighed up and assessed from a security stand point
I guess.

The 2nd, not so much. See
http://stackoverflow.com/questions/12002662/psql-trigger-send-email

Imagine if the DNS goes wonky. Do you want all your backends tied up in
DNS lookups? Or timing-out TCP connections?

Agree 100%, which is why I noted: "the server is properly configured on
the network"... I suppose you could always provide an IP address as the
mail host. This function is only a "Quick Knock Together" job, that works
readily enough.

IMHO There's always an inherent risk with any form of sending mail from an
RDBMS, whether it be abuse or otherwise, however; it's one of those
situations where "needs must", and more often than not must be done
quickly.

I would guess that having SMTP built into the DB engine itself would be no
less susceptible to abuse or problems than any other method (For example
UTL_SMTP in Oracle, which I've had hang before due to issues with SMTP
servers).

I guess it's one of those where you just have to weigh up the options and
choose the best one for your situation/application.

Cheers

Martin
=============================================

Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England

Telephone numbers:
+44 (0)115 951 88 00 (main)

For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================

================
Confidentiality Statement
This transmission is for the addressee only and contains information that
is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf
of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your
system and contact the sender. Thank you for your cooperation.
=================================================

Em 20/09/2012 09:07, pavithra escreveu:

Hi All, I am new to postgresql. I want to send email by using pl
pgsql. I want to know how to set up the configurations for mail
server. Can any one help me in solving this?. [hidden email]
</user/SendEmail.jtp?type=node&node=5724700&i=0>
------------------------------------------------------------------------
View this message in context: Need psql send email
<http://postgresql.1045698.n5.nabble.com/Need-psql-send-email-tp5724700.html&gt;
Sent from the PostgreSQL - general mailing list archive
<http://postgresql.1045698.n5.nabble.com/PostgreSQL-general-f1843780.html&gt;
at Nabble.com.

Dear friend,

I don't know if it is possible. But my experience with MS SQL Server
(integration with OutLook) introduces hundreds of flaws (including
crashes) into the database.
How did I accomplish this task:

a) To notify backups and so, I've configured my Cron task to do that (it
is fairly easy and well documented)

b) To notify about business tasks of my applications, my applications
send the e-mail (in my case, I do use Java, so I use standard JavaMail
API that does everything in a snap without any flaws for years now). I
believe every language in the world has similar stable APIs for sending
e-mails

c) If I need to send e-mail based on database events (like a trigger), I
use a "Queue Table" where I insert messages that need to be sent, and
have external application that (from time to time) checks this table for
new messages to be sent.

I hope this ideas help you.

Regards,

Edson.

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.

As luck would have it, I recently set a project up on Google Code to help
address this (and other application integration) issues. See
http://code.google.com/p/pg-message-queue/

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.

Also for more info on how to do this with a LISTEN/NOTIFY approach outside
of the above, see
http://ledgersmbdev.blogspot.com/2012/09/objectrelational-interlude-messaging-in.html

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