PostgreSQL触发函数中的动态查询 [英] Dynamic query in trigger function in PostgreSQL

问题描述

我试图在触发函数中执行动态查询，但是我一直在获取表 new的 Missing FROM-clause条目

I am trying to execute a dynamic query in a trigger function but I keep getting the Missing FROM-clause entry for table "new"

为什么会发生错误，我该如何解决？

Why does the error occur and how can I fix it?

CREATE OR REPLACE FUNCTION "Site"."UpdateAncestorModified"()
  RETURNS trigger AS
$BODY$BEGIN
    EXECUTE
        format
        ('
            UPDATE 
                "' || TG_TABLE_SCHEMA || '"."' || TG_TABLE_NAME || '" 
            SET
                modified = NEW.modified
            WHERE
                id = NEW."ancestorId"
            AND
                modified <> NEW.modified
        ')
    USING
        NEW;
    RETURN NEW;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

推荐答案

我不得不更改 （$ 1）的新，因为它是对第一个绑定参数的引用。

I had to change the NEW to the ($1) since it is the reference to the first bound parameter.

正确的查询是：

CREATE OR REPLACE FUNCTION "Site"."UpdateAncestorModified"()
  RETURNS trigger AS
$BODY$BEGIN
    EXECUTE
        format
        ('
            UPDATE 
                "' || TG_TABLE_SCHEMA || '"."' || TG_TABLE_NAME || '" 
            SET
                modified = ($1).modified
            WHERE
                id = ($1)."ancestorId"
            AND
                modified <> ($1).modified
        ')
    USING
        NEW;
    RETURN NEW;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

这篇关于PostgreSQL触发函数中的动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！