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?
