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.
LISTENandNOTIFYto get a helper daemon to do the work instead. – Craig Ringer Jun 12 at 2:54