postgresql - 如何在动态 sql 查询中使用 WITH block

Question

我有一个 plpgsql 函数需要根据用户输入从 3 个表中准备数据，并使用 COPY TO 导出数据。数据是道路交通事故，所以 3 个表是 accident，casualty 和 vehicle，每个事故链接到 vehicle 中的零个或多个记录并且通过存在于所有三个表中的 accidentid 列创建伤亡表。 severity 和 local_authorities 是输入参数(都是 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';

这第一位会得到相关的事故，所以我现在正在寻找最有效的方法来使用第一个查询中的 accidentid 来下载相关的车辆和伤亡数据。

我想我可以像这样使用 WITH block :

-- 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';

但是报错:

ERROR: syntax error at or near "$1"

LINE 1: WITH acc_ids AS ( $1 ) EXECUTE 'COPY (SELECT * FROM accident....

我已经在非动态测试用例中尝试了上述方法，例如

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);

哪个有效。不幸的是，服务器仍在运行 Postgres 8.4，所以我暂时无法使用 format()。

也许这对于 WITH block 是不可能的，但我希望它至少说明了我正在努力实现的目标。

编辑/更新

主要目标是从 3 个单独的 csv 文件中的 3 个表中获取相关数据，理想情况下无需在 accident 表上运行选择 3 次

Answer 1

如果要运行存储在字符串变量中的查询(部分)，则需要像这样的动态查询

EXECUTE 'WITH acc_ids AS (' || sql_query || ')'
   'SELECT ... ';

要么整个查询是由EXECUTE 执行的字符串，要么整个查询是静态SQL。你不能混合它们。

您需要 CTE 吗？如果您可以将查询表达为连接，优化器将有更多选择。