Thread: How to execute external script from a TRIGGER or FUNCTION ?

Hello everyone,

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...

Denis

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