Create notify trigger for specific JSON value in inserted row

Question

I have a table with 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 has such data:

    {
   "custom_required_action":"VERIFY_EMAIL",
}

I need to create a trigger and notify on inserted row event_entity table with condition:

WHERE type = 'CUSTOM_REQUIRED_ACTION' AND details_json:custom_required_action = 'VERIFY_EMAIL'

I've made it with

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();

But how to add second condition with details_json field?

Answer 1

can u try with below once and let me if it works

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

Answer 2

First create the notification trigger function. In the example below it will notify on the_notification_channel and have the new row values JSON formatted as notification payload. The names of the trigger, the trigger function and the notification channel are such for illustration only.

create or replace function the_notification_trigger_function()
returns trigger language plpgsql as 
$$
begin
 perform pg_notify('the_notification_channel', to_json(new)::text);
 return null;
end;
$$;

and then create the trigger with a condition

create trigger the_notification_trigger
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 function the_notification_trigger_function();

Unrelated but it would be much better if your details_json field was of type jsonb instead of text and event_time was of type timestamp instead of bigint. What is constraint_4?

It might be a good idea to move the new.details_json::json ->> 'custom_required_action' = 'VERIFY_EMAIL' sub-condition into the trigger function so that the trigger fill fire on every 'CUSTOM_REQUIRED_ACTION' and the function would decide on how to react.