在postgresql中的Update触发器之后，将值从一个表更新到另一个表

Question

我有两个表permit_table和permit_number weekly_table，前者具有列builder，后者具有列applicant和permit_number。现在，如果在来自permit_table的构建器列中有任何更新，我需要使用‘旧的申请者值+新的构建器值’来更新weekly_table的Applicant列，前提是来自permit_table的更新的构建器值的许可号应该与weekly_table中的任何许可证号相匹配。我已经尝试了下面的触发器和函数

CREATE OR REPLACE FUNCTION edmonton.automated_builder_update_trigger_manual()
RETURNS trigger AS
$BODY$
DECLARE
    e record;
BEGIN

EXECUTE format('Update weekly_table as a
set applicant = old.applicant||new.builder where old.permit_number = 
a.permit_number');


RETURN NULL;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION edmonton.automated_builder_update_trigger_manual()
OWNER TO postgres;

触发器是

CREATE TRIGGER builder_update_trigger_manual
AFTER UPDATE
ON edmonton.permit_table
FOR EACH ROW
WHEN (old.builder IS DISTINCT FROM new.builder)
EXECUTE PROCEDURE edmonton.automated_builder_update_trigger_manual();

我收到错误“表‘FROm’的旧子句条目缺失。”

Answer 1

EXECUTE format('Update weekly_table as a
set applicant = old.applicant||new.builder where old.permit_number = 
a.permit_number');

尝试更改为：

EXECUTE format('Update weekly_table as a
set applicant = ($1).applicant||($2).builder where ($1).permit_number  = 
a.permit_number') using old,new;