Home How to use a WITH block with dynamic sql query
 I've got a plpgsql function that needs to prepare data from 3 tables based on user input, and export the data using COPY TO. The data are road accidents, so the 3 tables are accident, casualty and vehicle, each accident links to zero or more records in the vehicle and casualty tables via an accidentid column that exists in all three tables. severity and local_authorities are input parameters (both text []). sql_query = 'SELECT COUNT(*) FROM accident WHERE severity = ANY(' || quote_literal(severity) || ') AND local_auth = ANY (' || quote_literal(local_authorities) || ')'; EXECUTE sql_query INTO result_count; IF result_count > 0 THEN -- replace Select Count(*) With Select * sql_query = Overlay(sql_query placing '*' from 8 for 8); -- copy the accident data first EXECUTE 'COPY (' || sql_query || ') TO ' || quote_literal(file_path || file_name_a) || ' CSV';  This first bit will get the relevant accidents, so I'm now looking for the most efficient way to use the accidentid's from the first query to download the related vehicle and casualty data. I thought I'd be able to use a WITH block like this: -- replace * with accidentid sql_query = Overlay(sql_query placing 'accidentid' from 8 for 1); WITH acc_ids AS (sql_query) EXECUTE 'COPY (SELECT * FROM vehicle WHERE accidentid IN (SELECT accidentid FROM acc_ids)) TO ' || out_path_and_vfilename || ' CSV'; EXECUTE 'COPY (SELECT * FROM casualty WHERE accidentid IN (SELECT accidentid FROM acc_ids)) TO ' || out_path_and_cfilename || ' CSV';  but get an error: ERROR: syntax error at or near "$1" LINE 1: WITH acc_ids AS ($1 ) EXECUTE 'COPY (SELECT * FROM accident.... I have tried the above in a non-dynamic test case e.g. WITH acc_ids AS ( SELECT accidentid FROM accident WHERE severity = ANY ('{3,2}') AND local_auth = ANY ('{E09000001,E09000002}') ) SELECT * FROM vehicle WHERE accidentid IN ( SELECT accidentid FROM acc_ids);  which works. Unfortunately the server is still running Postgres 8.4 so I can't use format() for the time being. Perhaps this isn't possible with a WITH block, but I hope it at least illustrates what I'm trying to achieve. Edit/Update The main goal is to get the relevant data from the 3 tables in 3 separate csv files, ideally without having to run the selection on the accident table 3 times