如何为event_entity表添加含details_json条件的通知触发器

我有一个名为event_entity的事件实体表，表结构定义如下：

create table event_entity
(
    id           varchar(36) not null
        constraint constraint_4
            primary key,
    details_json varchar(2550),
    event_time   bigint,
    type         varchar(255),
    user_id      varchar(255)
);

其中details_json字段存储如下格式的JSON数据：

{
   "custom_required_action":"VERIFY_EMAIL"
}

我需要为该表创建插入触发器，当插入的行满足条件type = 'CUSTOM_REQUIRED_ACTION' AND details_json:custom_required_action = 'VERIFY_EMAIL'时发送通知。

目前我已编写了部分触发器代码：

CREATE OR REPLACE FUNCTION notify_verifyEmail()
    RETURNS trigger AS $$
DECLARE
BEGIN
    PERFORM pg_notify(
            'verifyEmail',
            row_to_json(NEW)::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_verifyEmail
    AFTER INSERT ON event_entity
    FOR EACH ROW 
    WHEN (new.type = 'CUSTOM_REQUIRED_ACTION')
EXECUTE PROCEDURE notify_verifyEmail();

请问如何为该触发器添加details_json字段的判断条件？

直接修改触发器的WHEN条件，加入对details_json字段的判断逻辑即可。由于details_json是varchar类型，需要先转为JSON类型再提取字段值进行比较：

CREATE OR REPLACE FUNCTION notify_verifyEmail()
    RETURNS trigger AS $$
DECLARE
BEGIN
    PERFORM pg_notify(
            'verifyEmail',
            row_to_json(NEW)::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_verifyEmail
    AFTER INSERT ON event_entity
    FOR EACH ROW 
    WHEN (
        new.type = 'CUSTOM_REQUIRED_ACTION'
        AND (new.details_json::json ->> 'custom_required_action') = 'VERIFY_EMAIL'
    )
EXECUTE PROCEDURE notify_verifyEmail();

逻辑说明 #

• new.details_json::json：把字符串格式的details_json转换为JSON类型，才能进行字段提取操作
• ->>：该操作符用于提取JSON字段的文本值，提取后即可与'VERIFY_EMAIL'做等值判断

如果你的PostgreSQL版本支持jsonb类型，建议将details_json字段改为jsonb，这会提升JSON操作的性能，此时判断逻辑可以简化为：

AND (new.details_json ->> 'custom_required_action') = 'VERIFY_EMAIL'

内容的提问来源于stack exchange，提问作者hoozr