Thread: Calling external programs

Hello,

I would like to call an external program from Pg.  Specifically, I'd
like to send an email every time a row with certain attribute values is
inserted into a table.  I already have a trigger that fires upon
insertion into the table.  Is the preferred method to have a trigger
call a procedure written in say PLPERL?  Is PLPERL capable of calling
external programs or better yet forming the mail and calling sendmail
itself?

Thanks,
Fran

Check out PGMail

len morgan

-----Original Message-----
From: Fran Fabrizio <ffabrizio@Exchange.WebMD.net>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Date: Tuesday, June 05, 2001 12:14 PM
Subject: [GENERAL] Calling external programs


>
>Hello,
>
>I would like to call an external program from Pg.  Specifically, I'd
>like to send an email every time a row with certain attribute values is
>inserted into a table.  I already have a trigger that fires upon
>insertion into the table.  Is the preferred method to have a trigger
>call a procedure written in say PLPERL?  Is PLPERL capable of calling
>external programs or better yet forming the mail and calling sendmail
>itself?
>
>Thanks,
>Fran
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>

The suggestions of PgMail and pgsendmail were appreciated.  I see that the
former is TCL and the latter is C.  I thought that external filesystem
access was denied with these PL* languages, but I see in the case of PGMail
you explicitly install one that is unrestricted.  (I'm not sure how the
pgsendmail is getting around it).   Since everything else in my site is
written in Perl, I'd like to stick to that, and I have no problem writing
one myself.  I just thought I wasn't going to be able to because of the need
to access /usr/lib/sendmail and also probably to access an external email
template.  The email bodies I want to send are too lengthy to be sticking in
gracefully as a varchar field to a function call.  I'll probably want to
open a file template, and make some db calls to fill in some parameters.

If I can write my own in PLPERL I will cheerfully do so, but is there a way
to workaround the security restrictions in a Perl context?

Thanks,
Fran

Fran Fabrizio <ffabrizio@Exchange.WebMD.net> writes:
> If I can write my own in PLPERL I will cheerfully do so, but is there a way
> to workaround the security restrictions in a Perl context?

An unsecured variant of plperl (corresponding to pltclu) seems like a
fine idea ... but no one's gotten around to making it happen.  Want to
do the legwork?

plperl in general is only at a proof-of-concept stage.  It will probably
stay there unless interested users work on it.

            regards, tom lane