Re: Any way to send email from "within" PostgreSQL?

On Tue, 30 May 2000, Philip Hallstrom wrote:

> Hi -
>     Is there anyway to send email from within PostgreSQL (like from a
> trigger definition?)
> Thanks!
> -philip

Phil,

I use perl for this.  It is not from 'within postgres' but from the
interface script thus it may not be what you're looking for but
anyway...maybe you could apply the same idea, maybe not!  ;-)

Here is what I do:

CGI stuff (perl) leads up to calling this sub after the user submits
the form:

sub do_insert_query {
$dbh = DBI->connect("dbi:Pg:dbname=tracker;host=localhost;port=5432",
                     '', '', {RaiseError => 1});
$rv1 =
$dbh->do( "
    INSERT INTO workorder (staff_id, unit_id, order_id, r_date,
                           addr, zip, note1, note1_date, high_risk,
                           timer, w_type, completed, dept_id)
    VALUES                ($userid, $unitid, $order_id, '$today',
                          '$addr', '$zip', '$notetext', '$today',
                          '$notetype',
                          '$unit_time', '$w_type', 'N', $deptid)
                         ");
if (!defined $rv1) {
print("INSERT INTO workorder FAILED --- $DBI::errstr\n");
     } else {
$success = 1;
    }
$dbh->disconnect;
}

If the query succeeds, the var values are passed to this sub:

sub web_mail {
open MAIL, "| mail $mailer\@localhost -s 'Work Order Entered'
-c $usename\@localhost";
print MAIL
"
NOTICE: New Work Order Entered.  Please Review Attached.
------------------------------------------------
Input By:    $staffname
Note Date:   $today
High Risk:   $notetype
Timestamp:   $unit_time
Work Type:   $w_type
Department:  $deptid
Unit:        $unitid
Address:     $addr
Zip:         $zip
------------------------------------------------
Description:
$notetext
------------------------------------------------
";
close MAIL;
}

Hope this helps a little!
Cheers,
Tom

---------------------------------------------------------------------------
               North Richmond Community Mental Health Center
---------------------------------------------------------------------------
Thomas Good, MIS Coordinator                tomg@ { admin | q8 } .nrnet.org
                                                        Phone: 718-354-5528
                                                        Fax:   718-354-5056
---------------------------------------------------------------------------
 North Richmond Systems       PostgreSQL                 s l a c k w a r e
   Are Powered By:              RDBMS                   |---------- linux
---------------------------------------------------------------------------