I've read about and tested a way for a query which calls a user function which returns a temp table that gets its data from an external program. Example...
CREATE OR REPLACE FUNCTION run_linux_script() RETURNS TABLE (script_output text) LANGUAGE plpgsql AS $$ BEGIN -- Create a temporary table for this session CREATE TEMPORARY TABLE IF NOT EXISTS temp_script_results ( output_line text );
-- Truncate in case it was used previously in the same session TRUNCATE temp_script_results;
-- Execute the script and insert the output into the table COPY temp_script_results (output_line) FROM PROGRAM '/path/to/my/script/linux_script.pl';
-- Return the results to the calling user RETURN QUERY SELECT output_line FROM temp_script_results;
-- Clean up DROP TABLE temp_script_results; END; $$;
SELECT * FROM run_linux_script();
It runs, sort of, but fails because it can't find the "linux_script.pl" script because it's looking for it on the server side.
My question is about whether or not I can get something like this to run on the client side (where the script can be found).
