Thread: Run a unix or perl command in a trigger

Hi Postgres users,

I wonder to know if it is possible to launch a Perl program or a unix
comand in a trigger function.
I have tried to do that in a C trigger developed with the SPI function.
But my examples does not work and i don't have any error messages in the
postgres logfile.
So, i ask you your opinion and an example of code if your trigger works.

Regards,
Thierry Missimilly

> I wonder to know if it is possible to launch a Perl program or a unix
> comand in a trigger function.
> I have tried to do that in a C trigger developed with the SPI function.
> But my examples does not work and i don't have any error messages in the
> postgres logfile.
> So, i ask you your opinion and an example of code if your trigger works.

I guess I'm not sure what you mean by 'launch'.  Do you need to query the
results?

Here's a plperlu function that runs an external shell script.

I've not tried it in a trigger, but it should work.  This function
needs to be created as a superuser since it uses 'untrusted' perl.

It creates a security hole in that anyone who has write access to the
postgres user home directory can run ANYTHING.
--
Mike Nolan

create or replace function submit_batch(varchar, varchar)
returns varchar
security invoker
as '

# perl body goes here
# parameters:  user ID
#              batch id

$command = "/home/postgres/submit_batch.job "
. "$_[0]" . " " .  "$_[1]" ;

$x = system($command);

return $x;
' language plperlu;