无PLPGSQL环境下Postgres动态SQL执行方案咨询

我正在参与一项编程挑战，不需要挑战本身的帮助——我已经设计出解决方案，但没法在Postgres的技术限制下运行。这个挑战需要动态查询，因为单个SQL查询输出的列数量和名称每次运行都会变，输入数据的行决定了输出列，不同场景输入不同。

示例场景 #

输入1：

CountOfSales, ProductName
1,            Chocolate
2,            Cola

输出1：

Chocolate, Cola, Total
1,         2,    3

输入2：

CountOfSales, ProductName
100,          Burgers
200,          Fries
300,          Shakes

输出2：

Burgers, Fries, Shakes, Total
100,     200,   300,    600

当前进展 #

我已经写出了能生成正确动态SQL字符串的查询，比如：

SELECT CONCAT('SELECT ', STRING_AGG(CONCAT(CountOfSales, ' AS ', ProductName), ','), ', ', SUM(CountOfSales), ' as Total FROM Input') FROM Input

这个查询生成的SQL字符串单独执行能通过对应场景测试，但我没法做到：

• 运行生成解决挑战所需的SQL字符串
• 执行该SQL并返回结果集
• 返回结果集通过测试

我试过DO块、替换单引号字符串、无块EXECUTE语句、创建自定义exec函数等方法，都报语法错误。我感觉这些方法都需要PLPGSQL上下文，但我只能用纯SQL语句。还没试query-to-xml，因为觉得没法动态提取可变列的结果集。

明确：我不需要挑战本身的帮助，已经能生成正确动态SQL，问题是如何在无PLPGSQL环境下执行该SQL字符串并返回结果集？

在纯PostgreSQL SQL环境下，确实没有直接执行动态SQL并返回原生结果集的方式——因为动态SQL的结果结构不固定，纯SQL无法提前确定返回列的元数据。不过有两种可行的折中方案：

1. 使用query_to_xml提取动态结果 #

虽然你之前对这个方法有疑虑，但实际上可以结合XML处理来提取可变列的结果：

• 先用你的查询生成动态SQL，再传入query_to_xml
• 通过XML解析函数提取列名和对应的值

示例代码：

WITH dynamic_sql AS (
  SELECT CONCAT('SELECT ', STRING_AGG(CONCAT(CountOfSales, ' AS ', ProductName), ','), ', ', SUM(CountOfSales), ' as Total FROM Input') AS sql_text
  FROM Input
), xml_result AS (
  SELECT query_to_xml(sql_text, true, true, '') AS xml_data FROM dynamic_sql
)
SELECT 
  xpath('//row/*/name()', xml_data) AS column_names,
  xpath('//row/*/text()', xml_data) AS values
FROM xml_result;

这个查询会返回列名列表和对应的值列表，虽然不是原生的行/列结构，但能拿到正确的数据，部分挑战平台可能接受这种结构化输出。

2. 临时视图方案（依赖PL/pgSQL） #

如果挑战平台允许临时PL/pgSQL块，你可以通过临时视图间接执行动态SQL：

-- 生成动态SQL并创建临时视图
DO $$
DECLARE
  sql_text TEXT;
BEGIN
  SELECT CONCAT('SELECT ', STRING_AGG(CONCAT(CountOfSales, ' AS ', ProductName), ','), ', ', SUM(CountOfSales), ' as Total FROM Input') INTO sql_text FROM Input;
  EXECUTE 'CREATE TEMP VIEW dynamic_result AS ' || sql_text;
END $$;

-- 查询临时视图获取结果
SELECT * FROM dynamic_result;

但注意这个方法需要PL/pgSQL上下文，如果平台严格限制只能用纯SQL，这个方案不可行。

关键说明 #

纯SQL本身是静态类型语言，执行前必须确定返回的列数、列名和数据类型，因此无法直接执行动态生成的SQL并返回动态结构的结果集。如果严格限制只能用纯SQL，唯一可行的方式是用XML/JSON这类半结构化格式封装结果，再由外部程序解析成所需的列结构。

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