2

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?

  • What's the first line of script.R? Does it start with #!/usr/bin/R or whatever? – Philᵀᴹ Nov 2 '16 at 15:56
  • just before the different libraries I put this line: #!/usr/bin/Rscript – AIA Nov 2 '16 at 15:59
  • Apparently, you have to test your script, as it does not return as expected. – dezso Nov 2 '16 at 16:35
  • when I execute my script.R, it works with no problem. But when I try to execute it through the trigger it doesn't. – AIA Nov 2 '16 at 16:42
  • Possibly the environment is not set correctly for Rscript. You may want to wrap it in a shell script and capture its standard output and error for troubleshooting. – mustaccio Nov 2 '16 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.