Bash: Calling bash script from postgresql trigger function

Question

I am calling a bash script from the trigger function in postgresql. The script is called after update of the value(log_interval) in the table in SQL.

The read the changed value(log_interval) from the table in the script that is called and store in a file (interval.txt). If I open the file I see the old value of log_interval and not the new value in the interval.txt file.

Below is the code snippet that I am using:

Trigger function:

CREATE OR REPLACE FUNCTION update_upload_interval()
RETURNS trigger AS
$BODY$
#!/bin/sh
exec /home/User/ReadInterval.sh &
$BODY$
LANGUAGE plsh VOLATILE

Trigger:

CREATE TRIGGER assign_new_interval
AFTER UPDATE OF log_interval
ON table_interval
FOR EACH ROW
WHEN ((old.log_interval IS DISTINCT FROM new.log_interval))
EXECUTE PROCEDURE update_upload_interval();

Script code:

function readinterval() {
logperiod=$(psql -c "select log_interval from table_interval where id=1;" -t  $database_name)
echo "$logperiod" > /home/User/interval.txt
}

Am new to scripting and using SQL. Let me know the solution. Thanks in advance.

Answer 1

Unless there's more code that isn't being shown here, just calling ReadInterval.sh won't do anything because it's nothing but a function declaration. In addition, there's a variable $database_name that isn't being set.

CREATE OR REPLACE FUNCTION update_upload_interval()
RETURNS trigger AS
$BODY$
#!/bin/sh
database_name=???
psql -c "select log_interval from table_interval where id=1;" -t  $database_name > /home/User/interval.txt
$BODY$
LANGUAGE plsh VOLATILE