Send a email from a function/procedure?

The easiest way to accomplish this is to use PLperl.

You must use the untrusted version called plperlu.

See here how to install plperlu.

We must also install the perl mail:sendmail library which you can get from CPAN here: http://cpan.uwinnipeg.ca/dist/Mail-Sendmail.

Just follow the install instructions in the tar.gz file.

Finally just create this procedure in your PLperlu enabled Database:

CREATE or REPLACE FUNCTION public.psendmail(text,text,text,text,text)

RETURNS pg_catalog.varchar AS

$BODY$

use Mail::Sendmail;

%mail = ( From => $_[0],

To => $_[1],

Cc => $_[2],

Subject => $_[3],

Message => $_[4]

);

sendmail(%mail) or die $Mail::Sendmail::error;

$BODY$

LANGUAGE 'plperlu' VOLATILE;

This function works on the assumption that you have the mail:sendmail library set to your relay server.

You can also specify your relay server in the function itself, see the mail:sendmail docs.

If the message is sent OK you will recieve a 1 as the result.