|
|
|
|
|
|
by tobyhinloopen
211 days ago
|
|
|
We use it like this: CREATE TRIGGER notify_events_trg AFTER INSERT ON xxx.events FOR EACH ROW EXECUTE PROCEDURE public.notify_events();
CREATE FUNCTION public.notify_events() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
PERFORM pg_notify('events', row_to_json(NEW)::text);
RETURN NEW;
END;
$$;
And then we have a bunch of triggers like this on many tables: CREATE TRIGGER create_category_event_trg AFTER INSERT OR DELETE OR UPDATE ON public.categories FOR EACH ROW EXECUTE PROCEDURE public.create_category_event();
CREATE FUNCTION public.create_category_event() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
category RECORD;
payload JSONB;
BEGIN
category := COALESCE(NEW, OLD);
payload := jsonb_build_object('id', category.id);
IF NEW IS NULL OR NEW.deleted_at IS NOT NULL THEN
payload := jsonb_set(payload, '{deleted}', 'true');
END IF;
INSERT INTO xxx.events (channel, inserted_at, payload)
VALUES ('category', NOW() AT TIME ZONE 'utc', payload);
RETURN NULL;
END;
$$;
We found no notable performance issues. We have a single LISTEN in another application. We did some stress testing and found that it performs way better than we would ever need |
|
|