This is my first question here, so excuse me if it does not look well organized:
I want to call a R script from a trigger (postgresql) so this script will be executed after an INSERT on my table (with a specific condition on the insert), here is what I did (based on an example that I found here):
1- I created a table to save the result
CREATE TABLE trigger_test ( tt_id serial PRIMARY KEY NOT NULL, command_output text );
2- I created the function:
CREATE OR REPLACE FUNCTION execute_rscript() RETURNS TRIGGER LANGUAGE plpgsql AS $BODY$ BEGIN IF NEW.idniveau = 5 THEN COPY trigger_test (command_output) FROM PROGRAM 'Rscript /home/.../script.R'; END IF; RETURN NULL; END; $BODY$;
3- the trigger
CREATE TRIGGER trigger_execute_rscript AFTER INSERT ON event_table FOR EACH ROW EXECUTE PROCEDURE execute_rscript();
but it does not work:
ERROR: program "/home/.../script.R" failed DETAIL: child process exited with exit code 1 CONTEXT: SQL statement "COPY trigger_test (command_output) FROM PROGRAM '/home/.../script.R'"
any ideas please to solve this problem?