主页 / dba / 问题 / 227781
Accepted
Brylie Christopher Oxley
Asked: 2019-01-23 09:18:41 +0800 CST

在 Postgres 触发器的 FROM 子句中使用 NEW？

  • 772

我正在尝试编写一个 Postgres 触发器以在插入或更新新行之前取消嵌套数组字段。例如

SELECT
unnest(something)
FROM NEW

但是，这似乎会导致错误：

关系“新”不存在

如何在触发器函数中使用 NEW 行，以允许我取消嵌套数组字段以进行进一步处理？

创建表语句

以下是表结构的示例：

CREATE TABLE parent_table (
    id uuid NOT NULL,
    jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],
    CONSTRAINT "parent_table_pkey" PRIMARY KEY (id),
);

CREATE TABLE many_to_one_table (
  id serial primary key,
  parent_table_id uuid references parent_table(id),
  subfield_a text,
  subfield_a text
);

触发功能

这里是TRIGGER函数的本质：

CREATE OR REPLACE FUNCTION unnest_and_normalize_json() RETURNS TRIGGER AS 
$body$
begin
    if NEW.jsonb_array_field is null then
        raise exception 'jsonb_array_field is null';
    else
        insert into 
            many_to_one_table(subfield_a, subfield_b)
            select
                parent_table_id, -- this is to be the ForeignKey
                json_data->>'subfieldA' as subfield_a,
                json_data->>'subfieldB' as subfield_b
            from (
                select 
                    id, -- need ID for ForeignKey relationship
                    unnest(jsonb_array_field) as json_data
                    from new
            ) as unnested_json;
    end if;
    RETURN new;
end;
$body$ 
LANGUAGE plpgsql;

触发语句

触发器语句可以在 INSERT 和 UPDATE 之前或之后运行，只要数据在“迁移”表中镜像即可。

CREATE TRIGGER unnest_and_normalize_json_on_insert AFTER INSERT ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

CREATE TRIGGER unnest_and_normalize_json_on_update AFTER UPDATE ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

ForeignKey 说明 我们正在尝试将数据模型的这一部分转换为使用 ForeignKey 关系而不是 JSON 字段。触发器旨在作为一个临时步骤，以确保normalized_table有数据向前发展，同时我们从旧记录中回填/迁移数据。

postgresql trigger

1 个回答

  1. Best Answer

    NEW并且OLD在触发器函数中是记录- 每个只在适用的情况下定义。（例如，触发器中没有OLD。INSERT）您可以对它们执行ROW操作。使用点表示法（如表限定的列名）引用嵌套列，就像它们是列表中包含的FROM表一样。NEW并且OLD在触发器函数的 SQL DML 语句中几乎随处可见。（带 . 的动态 SQL 除外EXECUTE。）

    要将NEW其视为实际表，您必须首先对其进行转换 - 这通常不是必需的。喜欢：(SELECT NEW.*)。有关有意义的用例，请参阅第3章。在此相关答案中：

    您的基本触发功能可以归结为：

    CREATE OR REPLACE FUNCTION unnest_and_normalize_json()
  RETURNS trigger AS 
$func$
BEGIN
   INSERT INTO many_to_one_table (parent_table_id, subfield_a, subfield_b)
   SELECT NEW.id
        , ja->>'subfieldA' AS subfield_a  -- column alias only for documentation
        , ja->>'subfieldB'
   FROM   unnest(NEW.jsonb_array_field) ja;  -- produces a derived table

   RETURN NEW; -- optional for AFTER trigger
END
$func$  LANGUAGE plpgsql;

    这只是噪音：

    if NEW.jsonb_array_field is null then ...

    该列jsonb_array_field定义为：

    jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],

    这是一个AFTER触发器，此时NOT NULL约束已经引发了NULLin异常jsonb_array_field。

    您可以将两个触发器合并为一个：

    CREATE TRIGGER unnest_and_normalize_json_on_insert
AFTER INSERT OR UPDATE ON parent_table  -- !
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

    也就是说，您必须做更多的工作才能UPDATE正确覆盖DELETE。类似于这个（黑暗面一章）：

    • 6

相关问题