如何在PostgreSQL中执行函数返回的查询语句？

报错原因 #

你使用 execute func1('some clause') 报错，是因为PostgreSQL的EXECUTE命令是用来执行预准备语句的，它会把func1当成预准备语句的名称，而非调用函数并执行其返回的SQL字符串，因此才会提示“prepared statement 'func1' does not exist”。

先修正func1的语法问题 #

你的func1存在单引号嵌套的语法错误，同时有SQL注入风险，先调整如下：

CREATE OR REPLACE FUNCTION func1(clause text) RETURNS text AS
$body$ 
DECLARE 
  temp text;
BEGIN 
  -- 用美元引号避免单引号转义，%I安全处理列名，防止SQL注入
  EXECUTE format ($$SELECT string_agg(format('%I text', name), ', ') FROM "table" WHERE %s$$, clause) INTO temp;

  RETURN format($$SELECT * FROM crosstab(%L, %L) AS %s$$,
    format($$SELECT col1, col2, col3 FROM "table" WHERE %s$$, clause),
    format($$SELECT DISTINCT col1 FROM "table" WHERE %s$$, clause),
    '(name, ' || temp || ')');
END;
$body$ LANGUAGE plpgsql;

执行动态SQL的可行方法 #

方法1：psql交互式场景用\gexec

在psql客户端中，直接执行以下命令：

SELECT func1('some clause') \gexec

这个命令会先执行SELECT获取func1返回的SQL字符串，再自动执行该SQL并返回动态结果集，完美适配列结构不确定的场景。

方法2：程序调用用包装函数

如果需要在应用程序中调用，可以创建一个返回动态结果的包装函数：

CREATE OR REPLACE FUNCTION execute_func1(clause text)
RETURNS SETOF record AS
$$
BEGIN
  RETURN QUERY EXECUTE func1(clause);
END;
$$ LANGUAGE plpgsql;

调用时需要临时指定列结构（需提前知晓当前查询的列名和类型）：

SELECT * FROM execute_func1('some clause') AS result(name text, col_a text, col_b text);

方法3：应用程序层分两步执行

在Python、Java等应用中，先调用func1获取SQL字符串，再单独执行该字符串：

# 示例：用psycopg2实现
import psycopg2

conn = psycopg2.connect("dbname=your_db user=your_user")
cur = conn.cursor()

# 第一步：获取动态SQL
cur.execute("SELECT func1('some clause')")
dynamic_sql = cur.fetchone()[0]

# 第二步：执行动态SQL
cur.execute(dynamic_sql)
results = cur.fetchall()

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