How to execute external script from a TRIGGER or FUNCTION ?

Hello,

Denis BUCHER a �crit :

I need to execute an external script from Postgresql, it could be in
perl, in bash/shell, in whatever...

Any help would be appreciated ! I just need a very simple example if
possible...

I already searched on the web but found nothing...

After hours of search, I searched just some more and I think I found the
solution, hope it can be useful to someone else :

CREATE LANGUAGE plperlu;

CREATE FUNCTION test_perl_external (integer) RETURNS boolean AS $$
$filename = '/tmp/somefile';
if (-e $filename) { return true; }
return false;
$$ LANGUAGE plperlu;

SELECT test_perl_external(1);

Denis

Denis BUCHER wrote:

Hello,

Denis BUCHER a �crit :

I need to execute an external script from Postgresql, it could be in
perl, in bash/shell, in whatever...

Any help would be appreciated ! I just need a very simple example if
possible...

I already searched on the web but found nothing...

After hours of search, I searched just some more and I think I found the
solution, hope it can be useful to someone else :

CREATE LANGUAGE plperlu;

CREATE FUNCTION test_perl_external (integer) RETURNS boolean AS $$
$filename = '/tmp/somefile';
if (-e $filename) { return true; }
return false;
$$ LANGUAGE plperlu;

SELECT test_perl_external(1);

Denis

You want to run a script? The above would test to see if it exists, but not run it.

Use qx/cmd args/ or system('cmd args') to run it. (In perl anyway, I dunno if plperl supports it)

-Andy

After hours of search, I searched just some more and I think I found the
solution, hope it can be useful to someone else :

CREATE LANGUAGE plperlu;

CREATE FUNCTION test_perl_external (integer) RETURNS boolean AS $$
$filename = '/tmp/somefile';
if (-e $filename) { return true; }
return false;
$$ LANGUAGE plperlu;

SELECT test_perl_external(1);

Just remember that anything run like that, won't rollback in a transaction.

Andy Colson a �crit :

I need to execute an external script from Postgresql, it could be in
perl, in bash/shell, in whatever...

Any help would be appreciated ! I just need a very simple example if
possible...

I already searched on the web but found nothing...

After hours of search, I searched just some more and I think I found the
solution, hope it can be useful to someone else :

CREATE LANGUAGE plperlu;

CREATE FUNCTION test_perl_external (integer) RETURNS boolean AS $$
$filename = '/tmp/somefile';
if (-e $filename) { return true; }
return false;
$$ LANGUAGE plperlu;

SELECT test_perl_external(1);

You want to run a script? The above would test to see if it exists, but
not run it.

Use qx/cmd args/ or system('cmd args') to run it. (In perl anyway, I
dunno if plperl supports it)

Yes sorry that's correct, my example was just to show a solution to
access the outside world ! This is my real final version :

CREATE OR REPLACE FUNCTION hds_verifycustomer (integer) RETURNS boolean
AS $$
my $no_client = @_[0];
# Verify if customer exists in AS 400 system
$checkexitcode = system
("~postgres/scripts/checklive-as400-customer.pl $no_client >/dev/null
2>/dev/null");
if ($checkexitcode > 0) { return false; }
# Ok update has been done
return true;
$$ LANGUAGE plperlu;

Note, "LANGUAGE" is "plperlu" (u=unsafe) and not "plperl", because
otherwise you can't access the "outside world"...

This function is used in a very complex function that makes everything
transparent to use the "local" postgresql customer database :

SELECT * FROM hds_findcustomer(10234);

This function :
1. Returns the customer if present in postgres
2. Otherwise executes the external script (check and update)
3. And returns the customer if updated

Denis