Execute a Bash script (Shell Script) from postgres function/procedure/trigger

Question

CREATE or replace FUNCTION test() RETURNS boolean AS $$
$filename = '/home/postgres';
if (-e $filename) { 
exec /home/postgres/test.sh &
return true; }
return false;
$$ LANGUAGE plperlu;

exec /home/postgres/test.sh & its showing syntax error. Could you please help how to call bash script into postgres funtion/procedure

Answer 1

Presumably, the code needs to be syntactically valid Perl. So you'll need to clean up a few bits.

CREATE or replace FUNCTION test() RETURNS boolean AS $$
  my $filename = '/home/postgres';
  if (-e $filename) { 
    system '/home/postgres/test.sh' and return 1;
  }
  return;
$$ LANGUAGE plperlu;

I've changed a few things:

• I declare the variable $filename using my
• I used system instead of exec. exec replaces the current process - effectively never returning to the calling function
• system expects to be passed a string. So I've added quotes around the command
• and is usually better in flow control statements than && (and always much better than & which is for bit-flipping, not flow control)
• Perl doesn't have true and false, so I've replaced true with 1 (which is a true value in Perl). And I've removed the false from the other return statement - the default behaviour of return is to return a false value

I don't have a Postgresql installation to test this on. If it still doesn't work, please tell us what errors you get.