Обсуждение: Query-Sending mail from PostgresSQL

Hi,

We are using Postgres. Doing migration from Oracle to Postgres SQL. Now we
wanted to migrate the Sending mail operation as in oracle. Searched the
help couldn't able to find the solution.  Below is the oracle statement
used.

UTL_MAIL.send(
                sender     =>  EMAIL_DIST,
                recipients =>  EMAIL_DIST,
                cc         =>  EMAIL_DIST,
                bcc        =>  EMAIL_DIST,
                subject    =>  Load_Name ,
                message    =>  emesg
                );

Please give your suggestion how we can migrate the above statement to
Postgres.

Thanks
Ramesh Rajamanickam

Hi

2016-02-25 4:40 GMT+01:00 Ramesh Rajamanickam <rameshr.bnk@gmail.com>:

> Hi,
>
> We are using Postgres. Doing migration from Oracle to Postgres SQL. Now we
> wanted to migrate the Sending mail operation as in oracle. Searched the
> help couldn't able to find the solution.  Below is the oracle statement
> used.
>
> UTL_MAIL.send(
>                 sender     =>  EMAIL_DIST,
>                 recipients =>  EMAIL_DIST,
>                 cc         =>  EMAIL_DIST,
>                 bcc        =>  EMAIL_DIST,
>                 subject    =>  Load_Name ,
>                 message    =>  emesg
>                 );
>
> Please give your suggestion how we can migrate the above statement to
> Postgres.
>

It is not bug.

PostgreSQL has not this functionality. There is a workaround - you can
write own function in PLPerlu, where you can use some from CPAN libraries
for sending a mail.

http://search.cpan.org/~mivkovic/Mail-Sendmail-0.79/Sendmail.pm
http://postgres.cz/wiki/PL/Perlu_-_Untrusted_Perl_%28en%29

Regards

Pavel


>
> Thanks
> Ramesh Rajamanickam
>

On 24 February 2016 at 22:40, Ramesh Rajamanickam <rameshr.bnk@gmail.com>
wrote:

>
> Hi,
>
> We are using Postgres. Doing migration from Oracle to Postgres SQL. Now we
> wanted to migrate the Sending mail operation as in oracle. Searched the
> help couldn't able to find the solution.  Below is the oracle statement
> used.
>
> UTL_MAIL.send(
>                 sender     =>  EMAIL_DIST,
>                 recipients =>  EMAIL_DIST,
>                 cc         =>  EMAIL_DIST,
>                 bcc        =>  EMAIL_DIST,
>                 subject    =>  Load_Name ,
>                 message    =>  emesg
>                 );
>
> Please give your suggestion how we can migrate the above statement to
> Postgres.
>


It is not particularly reasonable to consider this to be a "bug", as that
tends to constitute some form of "incorrect behaviour."

That there is no function to submit email is not reasonably "incorrect
behaviour."

That said, I would not recommend implementing such a function in any case,
as this represents functionality that would not be able to run
transactionally (e.g. - with capability to roll back on error, and roll
back submitted email).

Instead, I would recommend implementing a function, perhaps with much the
same API, which inserts the specified data into a table in the database,
and then construct some external component to pull email from that table
and submit it.  That would have much better performance characteristics
(via conscious batch processing) than some API that opens a connection to
an SMTP server dynamically each time the function is called.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"