Thread: Running Command Line From Trigger?

On Thu, 2006-04-06 at 14:48, Yudie Pg wrote:
> I want to execute a command line from trigger function.
> The reason is I want to execute my perl script from stored procedure.
>

Why not just install perl as a procedural language?  It's one of a dozen
or more available to you.  Then you can write the whole trigger in perl
instead of calling an external program.

Note that perl, like many other languages, is available in a "trusted"
and an "untrusted" version.  The trusted versions aren't allowed to do
dangerous things like access the file system and run external programs.
Untrusted will do anything that perl can do from the command line.

Note that plpgsql cannot run external programs on purpose.  It's a
security and safety issue.

On Thursday 06 April 2006 03:48 pm, Yudie Pg saith:
> I want to execute a command line from trigger function.
> The reason is I want to execute my perl script from stored procedure.
>
> any idea?
>
>
> Yudie

Yudie,

You might want to write your perl script as a trusted perl funcion and call
from your trigger as you would any other backend function.

Example of one we use:

CREATE OR REPLACE FUNCTION fmtstr (TEXT, INTEGER) RETURNS TEXT AS '
    my ($str, $len) = @_;
    return (pack ("A$len", $str));
' LANGUAGE 'plperlu';

On Fri, 2006-04-07 at 10:42, Yudie Pg wrote:
>
>
> I'm running postgresql 7.4.1. is it can't really be used for trigger
> function?
>
> http://www.postgresql.org/docs/7.4/static/plperl-missing.html
>

Two points:

1:  Upgrade to 7.4.12 (or whatever the latest version is) immediately.
There are known data loss bugs in 7.4.1, and usually, the closer you are
to 7.4.0, the more likely those kinds of bugs are to show up.  I.e. a
data loss bug discovered in 7.4.11 and fixed in 7.4.12 is probably
REALLY rare, but one found in 7.4.0 and fixed in 7.4.2 is much more
likely to be a common occurrence, since those are  found early on.

2:  Try using plphp.  It can be used for triggers.  So can pltcl if
you'd rather use that.

On Fri, 2006-04-07 at 10:42, Yudie Pg wrote:
>
>
> I'm running postgresql 7.4.1. is it can't really be used for trigger
> function?
>
> http://www.postgresql.org/docs/7.4/static/plperl-missing.html
>

Quick followup, the latest plPHP does not work on Postgresql versions <
8.0.  So you'd either need to upgrade your postgresql to run it, or look
for pltcl.  pltcl has been around a while, so I can't imagine there not
being a good, solid, working version for pg 7.4

Yudie Pg wrote:
>
>
>
> I'm running postgresql 7.4.1. is it can't really be used for trigger
> function?
>
> http://www.postgresql.org/docs/7.4/static/plperl-missing.html
>
>
>
>
>
That is true, but you can call a plperl function from a plpgsql trigger
function.
Wouldn't that work?

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

On Friday 07 April 2006 01:32 pm, Yudie Pg saith:
> Back to my original question where is it possible to run a command line
> from a function?

From an earlier post:
"Note that plpgsql cannot run external programs on purpose.  It's a
security and safety issue."

On Fri, 2006-04-07 at 12:38, Terry Lee Tucker wrote:
> On Friday 07 April 2006 01:32 pm, Yudie Pg saith:
> > Back to my original question where is it possible to run a command line
> > from a function?
>
> From an earlier post:
> "Note that plpgsql cannot run external programs on purpose.  It's a
> security and safety issue."

Which is why I had advised using another language.  Help!  We're all
trapped in a loop!  ahhhhhhhhhhhhhhh!

Reminds me of an old Star Trek - Next Generation episode. They were stuck in a
time loop reliving the same series of events over and over. They had to leave
themselves a clue so they could figure it out next time around. Maybe your
post is it ;o)

On Friday 07 April 2006 02:12 pm, Scott Marlowe saith:
> On Fri, 2006-04-07 at 12:38, Terry Lee Tucker wrote:
> > On Friday 07 April 2006 01:32 pm, Yudie Pg saith:
> > > Back to my original question where is it possible to run a command line
> > > from a function?
> >
> > From an earlier post:
> > "Note that plpgsql cannot run external programs on purpose.  It's a
> > security and safety issue."
>
> Which is why I had advised using another language.  Help!  We're all
> trapped in a loop!  ahhhhhhhhhhhhhhh!
>

On Friday 07 April 2006 03:52 pm, Yudie Pg saith:
>  Help!
> Try to install plperl
> ./createlang plperl mydb
>
> createlang: language installation failed: ERROR:  could not access file
> "$libdir/plperl": No such file or directory

Do you have the Perl module on your machine? If not, you can get it here:
http://search.cpan.org/search?query=Pg&mode=all