PostgreSQL：动态转换透视查询返回列的数据类型

因为PostgreSQL无法在CAST的目标类型位置使用动态值（比如标量子查询），所以必须通过动态SQL实现需求——先从variables表获取变量类型，动态生成包含对应类型转换的透视查询语句，再执行该语句。

核心思路 #

1. 从variables表提取变量名和对应类型，拼接出每个变量的透视列表达式（包含类型转换）。
2. 将拼接好的列表达式嵌入完整的透视查询语句中。
3. 执行动态生成的SQL，得到宽格式结果。

具体实现 #

1. 基础动态SQL示例（包含所有变量） #

直接生成包含所有变量的透视查询并执行：

DO $$
DECLARE
  cols_text text;
  full_query text;
BEGIN
  -- 生成每个变量的透视列，包含类型转换
  SELECT string_agg(
    format(
      'MAX(CASE WHEN variable_name = %L THEN TRY_CAST(response AS %s) END) AS %I',
      variable_name, type, variable_name
    ),
    ', '
  ) INTO cols_text
  FROM variables;

  -- 拼接完整的透视查询语句
  full_query := format(
    'SELECT respondent_id, %s FROM responses GROUP BY respondent_id ORDER BY respondent_id',
    cols_text
  );

  -- 执行动态SQL
  EXECUTE full_query;
END $$;

• 使用TRY_CAST（PostgreSQL 12+支持）避免转换失败导致整个查询报错，转换失败时返回NULL。
• MAX聚合函数用于消除CASE WHEN产生的NULL值，确保每个respondent_id对应唯一行。

2. 参数化函数（支持指定变量组合） #

如果需要支持任意变量组合的参数化查询，可以封装成PL/pgSQL函数：

CREATE OR REPLACE FUNCTION get_pivoted_responses(p_target_vars text[])
RETURNS SETOF record AS $$
DECLARE
  cols_text text;
  full_query text;
BEGIN
  -- 仅生成指定变量的透视列
  SELECT string_agg(
    format(
      'MAX(CASE WHEN variable_name = %L THEN TRY_CAST(response AS %s) END) AS %I',
      v.variable_name, v.type, v.variable_name
    ),
    ', '
  ) INTO cols_text
  FROM variables v
  WHERE v.variable_name = ANY(p_target_vars);

  -- 拼接带变量过滤的查询语句
  full_query := format(
    'SELECT respondent_id, %s 
     FROM responses 
     WHERE variable_name = ANY(%L) 
     GROUP BY respondent_id 
     ORDER BY respondent_id',
    cols_text, p_target_vars
  );

  -- 执行并返回结果
  RETURN QUERY EXECUTE full_query;
END;
$$ LANGUAGE plpgsql;

调用示例

调用时需要指定返回结果的列结构（因为返回的是record类型）：

-- 查询age、gender、income三个变量的透视结果
SELECT * 
FROM get_pivoted_responses('{age, gender, income}') 
AS t(respondent_id int, age int, gender text, income numeric);

3. 注意事项 #

• 确保responses表中的response字段内容与variables表定义的类型兼容，否则TRY_CAST会返回NULL。
• 如果使用PostgreSQL 11及以下版本，没有TRY_CAST，可以用CASE结合pg_typeof做简单校验，或者捕获异常（复杂度较高）。
• 1800个变量的场景下，动态生成的SQL会很长，但PostgreSQL支持处理大SQL语句，只要内存足够即可。

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