Home How to use a WITH block with dynamic sql query
Reply: 0

How to use a WITH block with dynamic sql query

user2764
1#
user2764 Published in May 24, 2018, 3:54 pm

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.432006 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO