Home How to use a WITH block with dynamic sql query

# How to use a WITH block with dynamic sql query

Alex
1#
Alex Published in 2017-12-07 16:34:00Z
 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
Laurenz Albe
2#
Laurenz Albe Reply to 2017-12-07 17:50:37Z
 If you want to run a query (part) that is stored in a string variable, you need a dynamic query like EXECUTE 'WITH acc_ids AS (' || sql_query || ')' 'SELECT ... ';  Either the whole query is a string executed by EXECUTE, or the whole query is static SQL. You cannot mix them. Do you need a CTE? If you can express the query as a join, the optimizer has more options.
Alex
3#
 This does what I need to do without CTE but I can't see this being the most efficient way of solving this since I have to perform the same query on the accident table 3 times: sql_query = sql_query || which_tab || ' WHERE severity = ANY ('|| quote_literal(severity) ||') AND ' || date_start || ' AND ' || date_end || ' AND local_auth = ANY (' || quote_literal(local_authorities) || ')'; -- replace * with COUNT(*) sql_query = Overlay(sql_query placing 'COUNT(*)' from 8 for 1); EXECUTE sql_query INTO result_count; IF result_count > 0 THEN -- replace COUNT(*) with * 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'; sql_query = Overlay(sql_query placing 'accidentid' from 8 for 1); -- vehicles EXECUTE 'COPY (SELECT * FROM vehicle WHERE accidentid IN ( SELECT accidentid FROM accident WHERE severity = ANY (' || quote_literal(severity) || ') AND local_auth = ANY (' || quote_literal(local_authorities) ||'))) TO ' || quote_literal(file_path || file_name_v) || ' CSV'; -- casualties EXECUTE 'COPY (SELECT * FROM casualty WHERE accidentid IN ( SELECT accidentid FROM accident WHERE severity = ANY (' || quote_literal(severity) || ') AND local_auth = ANY (' || quote_literal(local_authorities) ||'))) TO ' || quote_literal(file_path || file_name_c) || ' CSV'; END IF;