Postgres块内执行多语句的plpgsql函数运行后表未创建问题求助

故障原因 #

• 核心错误：EXECUTE执行DDL语句时滥用INTO子句
  DROP TABLE、CREATE TABLE属于DDL语句，执行后不会返回整数值，你用INTO total试图接收不存在的返回结果，会直接触发运行时异常，导致函数执行中断，建表操作根本不会生效。
• 冗余参数问题：函数定义了入参tablename但全程未使用，属于代码逻辑缺陷，如果你原本需要动态传入表名，当前硬编码表名的写法也不符合设计预期。
• 权限问题：执行函数的数据库账号如果没有对应schema的DROP、CREATE TABLE权限，也会导致DDL语句执行失败。
• 事务问题：如果函数执行在外层未提交的事务中，后续事务回滚也会导致建表操作被撤销。

修复方案 #

去掉DDL语句后的INTO子句，可按需添加异常捕获判断执行状态，修复后代码如下：

CREATE OR REPLACE FUNCTION dropAggTables111(tablename TEXT) RETURNS INTEGER AS $total$
DECLARE
total integer;   
BEGIN
 -- 执行DROP操作，不需要接收返回值
 EXECUTE 'DROP TABLE IF EXISTS CURR_ACT_IN_EXP_TMP ';     
 -- 执行CREATE操作，不需要接收返回值
 EXECUTE 'CREATE TABLE IF NOT EXISTS CURR_ACT_IN_EXP_TMP (ACTIVITY VARCHAR(32)) ';
 -- 执行成功返回1作为状态标识
 total := 1;
 RETURN total;
-- 可选：添加异常捕获，执行失败返回0
EXCEPTION
 WHEN OTHERS THEN
   total := 0;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

如果你需要动态使用传入的tablename参数，建议用format函数加%I占位符避免SQL注入，示例如下：

CREATE OR REPLACE FUNCTION dropAggTables111(tablename TEXT) RETURNS INTEGER AS $total$
DECLARE
total integer;   
BEGIN
 EXECUTE format('DROP TABLE IF EXISTS %I ', tablename);     
 EXECUTE format('CREATE TABLE IF NOT EXISTS %I (ACTIVITY VARCHAR(32)) ', tablename);
 total := 1;
 RETURN total;
EXCEPTION
 WHEN OTHERS THEN
   total := 0;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

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