使用jOOQ创建PostgreSQL触发器触发无效函数的语法错误问题

问题背景 #

用jOOQ为MY_TABLE创建触发器，触发器创建成功且能触发，但执行时抛出语法错误。jOOQ代码如下：

dsl.createTrigger("my_trigger")
  .beforeInsert().orUpdate().on(MY_TABLE).forEachRow()
  .`as`(DSL.execute("EXECUTE PROCEDURE other_schema.update_method()")).execute()

其中MY_TABLE和other_schema下的update_method均已存在。

生成的代码与报错 #

jOOQ生成的触发器SQL：

create trigger my_trigger before insert or update on MY_TABLE for each row execute function my_trigger_function()

插入或更新表数据时，报错：

ERROR: syntax error at or near "other_schema"

排查过程 #

在public模式下找到jOOQ自动生成的包装函数：

CREATE OR REPLACE FUNCTION public.my_trigger_function()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
  execute 'EXECUTE PROCEDURE other_schema.update_method()';
  return new;
end;
$function$
;

尝试将execute改为call后，提示找不到方法。目标函数update_method的定义为：

CREATE OR REPLACE FUNCTION other_schema.update_method()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
    new.tsv_search_fulltext :=
            setweight(to_tsvector(coalesce(new.search_fulltext,'')), 'A') ;
    return new;
end
$function$
;

解决方案 #

问题根源在于错误使用DSL.execute()导致jOOQ生成了冗余的包装函数，且包装函数内的动态SQL语法错误。

正确的jOOQ写法 #

直接引用触发器函数，让jOOQ生成直接绑定目标函数的触发器，无需中间包装：

dsl.createTrigger("my_trigger")
  .beforeInsert().orUpdate().on(MY_TABLE).forEachRow()
  .`as`(DSL.triggerFunction(OTHER_SCHEMA.UPDATE_METHOD)).execute()

效果 #

生成的触发器SQL会直接调用目标函数：

create trigger my_trigger before insert or update on MY_TABLE for each row execute function other_schema.update_method()

这样就能正常执行，不会再出现语法错误。

错误原因解释 #

1. EXECUTE PROCEDURE是PostgreSQL触发器定义中调用函数的语法，不是PL/pgSQL中的执行语句，包装函数里用动态SQL执行该语句本身就不符合语法规范。
2. 触发器函数依赖NEW/OLD上下文，不能通过普通函数调用的方式执行，必须由触发器直接绑定调用。

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