CODEWITHSUNDEEP
postgresqlplpgsqlcommon-table-expressiondynamic-sql
Alex
Alex

Reputation: 546

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

Upvotes: 0

Views: 817

Answers (2)

Alex
Alex

Reputation: 546

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;

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247270

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.

Upvotes: 2

Related Questions