时间戳

首页 > 解决方案 > Postgres - 创建可以存在于多个模式中的触发器

postgresql - Postgres - 创建可以存在于多个模式中的触发器

问题描述

我有一个我写的 postgres 触发器。它100％有效，我已经对其进行了彻底的测试。但是，当我的应用程序写入该数据库表时，它会失败。我将代码放在下面，然后我将解释失败。

CREATE OR REPLACE FUNCTION validate_client_user_role()
    RETURNS trigger AS
    $BODY$

    DECLARE role_has_client INT;
    DECLARE user_has_client INT;

    BEGIN
        IF NEW.client_id IS NULL THEN
            RAISE EXCEPTION 'client_id cannot be null';
        END IF;

        IF NEW.user_id IS NULL THEN
            RAISE EXCEPTION 'user_id cannot be null';
        END IF;

        IF NEW.role_id IS NULL THEN
            RAISE EXCEPTION 'role_id cannot be null';
        END IF;

        SELECT COUNT(*)
        INTO role_has_client
        FROM roles
        WHERE id = NEW.role_id
        AND client_id = NEW.client_id;

        SELECT COUNT(*)
        INTO user_has_client
        FROM client_users
        WHERE user_id = NEW.user_id
        AND client_id = NEW.client_id;

        IF role_has_client = 0 THEN
            RAISE EXCEPTION 'Role is not allowed by client';
        END IF;

        IF user_has_client = 0 THEN
            RAISE EXCEPTION 'User is not allowed by client';
        END IF;

        RETURN NEW;
    END
    $BODY$ LANGUAGE plpgsql;

CREATE TRIGGER client_user_role_validation
    BEFORE INSERT OR UPDATE
    ON client_user_roles
    FOR EACH ROW
    EXECUTE PROCEDURE validate_client_user_role();

所以，我的应用程序数据库有多个模式，一个用于 dev、qa、prod 等。当我第一次编写这个 DDL 语句时，我首先运行它：

set search_path to dev;

然后将其正确添加到“开发”模式中。只要我在我的数据库查询控制台中，我就可以验证这个触发器是否正常工作。

但是，当我的应用程序尝试写入表时，触发器失败，说它尝试执行的选择的关系（角色、client_users）不存在。我可以通过修改函数代码以显式引用每个表的“dev”模式来解决这个问题。不过，我不想这样做。我更喜欢可以作为 dev/qa/prod 的 DDL 语句执行的代码，而无需替换所有模式引用或保留它的多个副本。

我希望有人可以解释是否有某种方法可以做到这一点。显然，触发器不是从我执行它时分配给它的架构继承的。但是，如果有一些我不知道的 postgres 技巧来完成这项工作，我将不胜感激。谢谢你。

标签： postgresql

解决方案

您可以使用TG_TABLE_SCHEMA变量和set_config()withIS_LOCAL = true来完成此操作：

CREATE OR REPLACE FUNCTION validate_client_user_role()
    RETURNS trigger AS
    $BODY$

    DECLARE role_has_client INT;
    DECLARE user_has_client INT;

    BEGIN
        IF NEW.client_id IS NULL THEN
            RAISE EXCEPTION 'client_id cannot be null';
        END IF;

        IF NEW.user_id IS NULL THEN
            RAISE EXCEPTION 'user_id cannot be null';
        END IF;

        IF NEW.role_id IS NULL THEN
            RAISE EXCEPTION 'role_id cannot be null';
        END IF;

        PERFORM set_config('search_path', TG_TABLE_SCHEMA, true);  -- <-- This line

        SELECT COUNT(*)
        INTO role_has_client
        FROM roles
        WHERE id = NEW.role_id
        AND client_id = NEW.client_id;

        SELECT COUNT(*)
        INTO user_has_client
        FROM client_users
        WHERE user_id = NEW.user_id
        AND client_id = NEW.client_id;

        IF role_has_client = 0 THEN
            RAISE EXCEPTION 'Role is not allowed by client';
        END IF;

        IF user_has_client = 0 THEN
            RAISE EXCEPTION 'User is not allowed by client';
        END IF;

        RETURN NEW;
    END
    $BODY$ LANGUAGE plpgsql;

推荐阅读