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

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