Re: Need psql send email

> > 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(dot)ibt(at)gmail(dot)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(at)romaxtech(dot)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.
=================================================