how to send postgreSQL database mail using functions

Question

How do I send mail through PostgreSQL database?

I have installed the TCL untrusted language pltclu.

What should I do next?

Answer 1

Below code is working

CREATE OR REPLACE FUNCTION public.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 VOLATILE;

Answer 2

You can use py_pgmail from: https://github.com/lcalisto/py_pgmail

Then you can just call:

select py_pgmail('sentFromEmail',array['destination emails'],array['cc'],array['bcc'],'Subject','<USERNAME>','<PASSWORD>','Text message','HTML message','<MAIL.MYSERVER.COM:PORT>')

array['cc'] and array['bcc'] can be empty arrays like array['']