ores.sql.schema.notify_trigger

Template
See also

pg_notify trigger announcing entity changes (insert/update/delete with the primary-key value) on the entity's channel — the database end of the eventing pipeline. {{#domain_entity}} section. sql profile; output …/{component}_{entity_plural}_notify_trigger_create.sql. PostgreSQL `NOTIFY` trigger: fires a channel notification on every `INSERT`, `UPDATE`, or `DELETE` so the application layer can push real-time updates over NATS.

See the Template variable reference for the complete list of available variables and their semantics. See SQL naming conventions for the official object-name patterns all SQL templates follow.

Template

The full template source. Edit here and re-tangle with compass build --direct tangle_codegen_templates to regenerate library/templates/sql_schema_notify_trigger.mustache.

{{! GENERATED FILE — tangled from projects/ores.codegen/library/templates/sql_schema.org. Edit the org source. }}
{{! Template to generate SQL notification trigger for entity changes }}
{{{sql_license}}}
{{#domain_entity}}
/*
 * AUTO-GENERATED FILE - DO NOT EDIT MANUALLY
 * Template: sql_schema_notify_trigger.mustache
 * To modify, update the template and regenerate.
 */

create or replace function {{sql_name_base}}_notify_fn()
returns trigger as $$
declare
    notification_payload jsonb;
    entity_name text := '{{product}}.{{component}}.{{entity_singular}}';
    change_timestamp timestamptz := NOW();
    {{primary_key.notify_declarations}}
    changed_tenant_id text;
begin
    if TG_OP = 'DELETE' then
        {{primary_key.notify_assign_old}}
        changed_tenant_id := OLD.tenant_id::text;
    else
        {{primary_key.notify_assign_new}}
        changed_tenant_id := NEW.tenant_id::text;
    end if;

    notification_payload := jsonb_build_object(
        'entity', entity_name,
        'timestamp', ores_utility_iso8601_timestamp_fn(change_timestamp),
        'entity_ids', jsonb_build_array({{primary_key.notify_id_array}}),
        'tenant_id', changed_tenant_id
    );

    perform pg_notify('{{sql_name_base}}', notification_payload::text);

    return null;
end;
$$ language plpgsql;

create or replace trigger {{sql_name_base}}_notify_trg
after insert or update or delete on {{sql_name_base}}_tbl
for each row execute function {{sql_name_base}}_notify_fn();
{{/domain_entity}}

ores.sql.schema.notify_trigger

Table of Contents

Template

See also