PostgreSQL含美元引号的动态查询执行报错，求修复及优化方案

问题背景 #

需要实现以下功能：

• 给定日期范围，生成日期格式的字符串列名集合
• 在函数中调用该生成函数构建动态交叉表查询语句
• 通过EXECUTE执行动态查询并返回结果

原代码执行时触发语法错误，报错信息：

[42601] ERROR: syntax error at or near "'SELECT * FROM crosstab($ct$'" Where: PL/pgSQL function app_public.incentive_per_line(date,date) line 16 at EXECUTE

错误原因分析 #

1. 多余单引号嵌套：原FORMAT语句外层错误添加单引号，导致生成的SQL语句首尾多了一层无效单引号，PostgreSQL无法解析
2. 美元引号使用混乱：嵌套的美元引号标签重复或层级处理不当，导致字符串边界识别错误
3. 返回结果处理缺失：函数声明返回SETOF RECORD，但未通过RETURN QUERY执行EXECUTE并返回结果

修正与优化后的代码 #

1. 优化日期列名生成函数 #

用string_agg替代循环拼接，更简洁高效：

DROP EXTENSION IF EXISTS tablefunc;
CREATE EXTENSION IF NOT EXISTS tablefunc;

DROP FUNCTION IF EXISTS app_public.generate_date_columns(p_from_date DATE, p_to_date DATE);
CREATE OR REPLACE FUNCTION app_public.generate_date_columns(p_from_date DATE, p_to_date DATE)
  RETURNS TEXT AS $$
BEGIN
  RETURN string_agg(format('"%s" INT', generate_series), ', ')
  FROM generate_series(p_from_date, p_to_date, '1 day'::interval)::DATE generate_series;
END
$$ LANGUAGE plpgsql;

-- 测试列名生成
SELECT app_public.generate_date_columns('2023-05-30'::DATE, '2023-06-05'::DATE);

2. 修正动态交叉表查询函数 #

调整美元引号嵌套逻辑，移除多余单引号，添加RETURN QUERY返回结果：

DROP FUNCTION IF EXISTS app_public.incentive_per_line(DATE, DATE);
CREATE OR REPLACE FUNCTION app_public.incentive_per_line(p_from_date DATE, p_to_date DATE)
  RETURNS SETOF RECORD AS $$
DECLARE
  column_names TEXT;
  v_dynamic_query TEXT;
BEGIN
  SELECT app_public.generate_date_columns(p_from_date, p_to_date) INTO column_names;

  -- 正确构建动态SQL，使用不同美元标签避免嵌套冲突
  v_dynamic_query := format($dyn$
    SELECT * FROM crosstab(
      $ct$
        SELECT op.line_no::SMALLINT, pay.payout_date::DATE, pay.amount::INT AS incentive
        FROM app_public.operators AS op 
        INNER JOIN app_public.payouts AS pay ON pay.oid = op.id
        WHERE pay.payout_date >= %L AND pay.payout_date <= %L
      $ct$
    ) AS ct(ln SMALLINT, %s)
  $dyn$, p_from_date, p_to_date, column_names);

  RAISE NOTICE 'Generated Query: %', v_dynamic_query;
  
  -- 执行动态查询并返回结果
  RETURN QUERY EXECUTE v_dynamic_query;
END
$$ LANGUAGE plpgsql;

-- 调用函数时需指定返回结构，示例：
SELECT * FROM app_public.incentive_per_line('2023-05-30'::DATE, '2023-06-05'::DATE) 
AS (line_no SMALLINT, "2023-05-30" INT, "2023-05-31" INT, "2023-06-01" INT, "2023-06-02" INT, "2023-06-03" INT, "2023-06-04" INT, "2023-06-05" INT);

关键优化点 #

• 简化列名生成：用string_agg和generate_series直接生成列名字符串，避免循环拼接和手动移除尾逗号
• 规范美元引号使用：外层用$dyn$，内层交叉表SQL用$ct$，不同标签避免嵌套冲突
• 安全参数绑定：使用%L格式符自动处理日期的字符串转义，避免SQL注入风险
• 正确返回结果：用RETURN QUERY EXECUTE直接返回动态查询的结果集，符合SETOF RECORD的返回要求

内容的提问来源于stack exchange，提问作者kaushalyap