如何将PostgreSQL中生成的SQL字符串转为可执行查询？

我有一个可正常运行的静态PostgreSQL查询：

SELECT 
    companyname,
    companyid,
    incorporationcountryid,
    periodEndDate,
    filingdate,
    AVG(dataitemvalue) FILTER (WHERE menmonic = 'AT') as "AT",
    AVG(dataitemvalue) FILTER (WHERE menmonic = 'COGS') as "COGS"
FROM
    aaa3
GROUP BY 
    companyname, companyid,
    incorporationcountryid, periodEndDate, filingdate
ORDER BY 
    companyname, periodEndDate

现在我希望纳入所有mnemonics，而非仅'AT'和'COGS'，并动态调整查询。我刚接触SQL，已写出可获取表中所有唯一mnemonics并生成目标查询字符串的SQL：

SELECT 'SELECT companyname,companyid,incorporationcountryid,periodEndDate,filingdate,' 
|| STRING_AGG(DISTINCT CONCAT('AVG(dataitemvalue) FILTER (WHERE menmonic = "', menmonic,'") as "',menmonic,'"'),',') 
|| ' FROM aaa3 GROUP BY companyname, companyid, incorporationcountryid, periodEndDate, filingdate ORDER BY companyname, periodEndDate;'
as menmonic FROM aaa2;

请问能否将这个生成的字符串转换为可执行的PostgreSQL查询？

解决方案 #

方法1：使用psql客户端的\gexec命令 #

如果你在psql命令行工具中操作，可以直接用\gexec让生成的SQL字符串自动执行：

SELECT 'SELECT companyname,companyid,incorporationcountryid,periodEndDate,filingdate,' 
|| STRING_AGG(DISTINCT CONCAT('AVG(dataitemvalue) FILTER (WHERE menmonic = "', menmonic,'") as "',menmonic,'"'),',') 
|| ' FROM aaa3 GROUP BY companyname, companyid, incorporationcountryid, periodEndDate, filingdate ORDER BY companyname, periodEndDate;'
as menmonic FROM aaa2 \gexec

这个命令会先执行查询生成SQL字符串，然后自动执行该字符串对应的SQL语句。

方法2：用PL/pgSQL函数封装执行逻辑 #

如果需要在应用程序或非psql环境中执行，可以编写PL/pgSQL函数来实现动态执行：

CREATE OR REPLACE FUNCTION run_dynamic_pivot()
RETURNS void AS $$
DECLARE
    pivot_sql text;
BEGIN
    -- 生成动态SQL字符串
    SELECT 'SELECT companyname,companyid,incorporationcountryid,periodEndDate,filingdate,' 
           || STRING_AGG(DISTINCT CONCAT('AVG(dataitemvalue) FILTER (WHERE menmonic = ', quote_literal(menmonic), ') as ', quote_ident(menmonic)), ',') 
           || ' FROM aaa3 GROUP BY companyname, companyid, incorporationcountryid, periodEndDate, filingdate ORDER BY companyname, periodEndDate;'
    INTO pivot_sql
    FROM aaa2;

    -- 执行生成的SQL
    EXECUTE pivot_sql;
END;
$$ LANGUAGE plpgsql;

调用函数即可执行动态生成的查询：

SELECT run_dynamic_pivot();

注意事项 #

• 若aaa3表本身包含所有需要的menmonic值，可将FROM aaa2改为FROM aaa3，避免依赖额外表。
• 推荐使用quote_literal()和quote_ident()函数处理menmonic值，自动转义特殊字符（如空格、引号），避免SQL注入风险。

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