时间戳

首页 > 解决方案 > 使用 DEFAULT 处理 PostgreSQL 视图触发器中的 NULL 值？

postgresql - 使用 DEFAULT 处理 PostgreSQL 视图触发器中的 NULL 值？

问题描述

我想解释一下 Postgres 视图的触发器。

为了弄清楚我想问什么，我会给你一个非常简化的例子来说明我的情况。在此示例中，我们有两个表 ( table_a, table_b)，它们连接在一起构成示例 ( vw_table_ab) 中的视图。

在本例中，我将使用简单的名称和简单的 DDL/DML。

-- TABLE table_a
CREATE TABLE table_a
(
    id              serial PRIMARY KEY,
    timestamp_field timestamp DEFAULT now() NOT NULL,
    boolean_field   boolean   DEFAULT FALSE NOT NULL
);

-- TABLE table_b
CREATE TABLE table_b
(
    id              serial PRIMARY KEY,
    timestamp_field timestamp DEFAULT now() NOT NULL,
    boolean_field   boolean   DEFAULT FALSE NOT NULL,
    id_table_a      integer                 NOT NULL,
    CONSTRAINT "fk_table_a" FOREIGN KEY (id_table_a) REFERENCES table_a (id) ON DELETE CASCADE NOT DEFERRABLE,
    CONSTRAINT "u_table_a" UNIQUE (id_table_a)
);

-- VIEW vw_table_ab
CREATE VIEW vw_table_ab AS (
    SELECT a.timestamp_field AS timestamp_a,
           a.boolean_field   AS boolean_a,
           b.timestamp_field AS timestamp_b,
           b.boolean_field   AS boolean_b
    FROM table_a a
    JOIN table_b b ON a.id = b.id_table_a
);

标准操作 ( 和 ) 上的触发器函数INSERT通过UPDATE触发器DELETE链接到此视图INSTEAD OF。

-- TRIGGER FUNCTION fn_trigger
CREATE FUNCTION fn_trigger() RETURNS trigger LANGUAGE plpgsql AS
$_$
DECLARE
    sql TEXT;
BEGIN
    sql = 'SELECT ' || TG_TABLE_NAME || '_' || lower(TG_OP) || '($1);';

    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        EXECUTE (sql) USING NEW;
        RAISE NOTICE '%', sql;
        RETURN NEW;
    ELSE
        EXECUTE (sql) USING OLD;
        RAISE NOTICE '%', sql;
        RETURN OLD;
    END IF;
END;
$_$;

-- TRIGGER tr_table_ab
CREATE TRIGGER tr_table_ab
INSTEAD OF INSERT OR UPDATE OR DELETE ON vw_table_ab
FOR EACH ROW EXECUTE PROCEDURE fn_trigger();

我带来的示例有一个仅在插入操作上调用的触发器，执行的函数是这样的：

-- INSERT FUNCTION vw_table_ab_insert
CREATE FUNCTION vw_table_ab_insert(new vw_table_ab) RETURNS void LANGUAGE plpgsql AS
$_$
DECLARE
    id_table_a integer;
BEGIN
    INSERT INTO table_a (timestamp_field, boolean_field) VALUES (new.timestamp_a, new.boolean_a) 
    RETURNING id 
    INTO id_table_a;

    INSERT INTO table_b (timestamp_field, boolean_field, id_table_a) VALUES (new.timestamp_a, new.boolean_b, id_table_a);
END;
$_$;

现在我们可以解决我的问题了。我在视图上进行了插入，当触发操作时，我收到“非空违规”错误，因为在这种情况下我有一些NOT NULL限制table_a和table_b喜欢：

INSERT INTO vw_table_ab (timestamp_a, boolean_a, timestamp_b, boolean_b) VALUES (now(), NULL, now(), NULL);

假设前面的语句是通过编程语言框架生成的，我不想在后端代码中处理这种情况，但我想在 PostgreSQL 中的 insert 函数中处理这种情况vw_table_ab_insert。所以此时我的问题new与函数的参数有关，因为我的视图字段是NULL. 但是这些字段DEFAULT在基表的定义中有一个值，我想使用它。

...
timestamp_field timestamp DEFAULT now() NOT NULL,
boolean_field   boolean   DEFAULT FALSE NOT NULL
...

我的问题： 如何使用表中定义的 DEFAULT 管理视图触发器中的 NULL 值？

最初我想把函数放在IF ... THEN ...函数内部并用表达式覆盖空值，DEFAULT但我真的不喜欢那样。例如，函数会变成这样：

CREATE FUNCTION vw_table_ab_insert(new vw_table_ab) RETURNS void LANGUAGE plpgsql AS
$_$
DECLARE
    id_table_a integer;
BEGIN
    IF new.timestamp_a IS NULL THEN
        new.timestamp_a = DEFAULT;
    END IF;

    IF new.boolean_a IS NULL THEN
        new.boolean_a = DEFAULT;
    END IF;

    IF new.timestamp_b IS NULL THEN
        new.timestamp_b = DEFAULT;
    END IF;

    IF new.boolean_b IS NULL THEN
        new.boolean_b = DEFAULT;
    END IF;

    INSERT INTO table_a (timestamp_field, boolean_field)
    VALUES (new.timestamp_a, new.boolean_a) 
    RETURNING id
    INTO id_table_a;

    INSERT INTO table_b (timestamp_field, boolean_field, id_table_a)
    VALUES (new.timestamp_a, new.boolean_b, id_table_a);
END;
$_$;

有人可以帮助我吗？有没有其他方法可以处理这种情况？

标签： postgresqlplpgsqlpostgresql-9.3database-trigger

解决方案

最简单的方法是使用ALTER VIEW ... ALTER col SET DEFAULT在视图上定义与基表上的默认值相同的默认值。

INSERT然后，不要插入显式 NULL，而是从语句中省略列或DEFAULT显式插入。您生成的视图的行为就像一个真实的表格。

推荐阅读