Модуль уведомлений

В первую очередь, необходимо добавить шаблон уведомления на странице /admin/notification/notificationtemplate/

Например:

Код: close
Имя: State is closed
Текст: "%s" is closed

Далее, необходимо в нужной процедуре, добавить уведомление.

-- нотификация для статуса close
IF (SELECT model_notification(document_id::int, 'close')) IS NOT TRUE THEN 
    RAISE NOTICE '%', 'Не удалось разослать нотификации!';
END IF;

Пример реализации функций:

CREATE OR REPLACE FUNCTION public.get_notification_field(user_lang character, noty_code character, field text)
  RETURNS text
  LANGUAGE plpgsql
AS $function$
DECLARE

    select_sql TEXT;
    target TEXT;
BEGIN

    select_sql := format('SELECT %s_%s FROM notification_templates WHERE code = ''%s''', field, user_lang, noty_code);
    EXECUTE select_sql INTO target;

    IF target IS NULL OR target = '' THEN
        select_sql := format('SELECT %s FROM notification_templates WHERE code = ''%s''', field, noty_code);
        EXECUTE select_sql INTO target;
    END IF;

    RETURN target;
END;
$function$


CREATE OR REPLACE FUNCTION public.model_notification(in_obj_id integer, in_tpl_code character)
  RETURNS boolean
  LANGUAGE plpgsql
AS $function$
DECLARE
    --
    -- Уведомления
    --

    row record;
    data_obj record;
    user_lang TEXT;
    name text;
    body text;
    title text;
BEGIN
    -- обьект рейтинга
    FOR data_obj IN (SELECT * FROM db_table WHERE id = in_obj_id) LOOP

        -- В статусе закрыто
        IF in_tpl_code = 'close' THEN

            -- Эксперты и Участники рейтинга
            FOR row IN (SELECT DISTINCT id FROM (SELECT id FROM authentication_user WHERE organization_id IN (SELECT organization_id FROM db_table_members WHERE data_id = in_obj_id) UNION SELECT id FROM authentication_user WHERE organization_id IN (SELECT expert_organization_id FROM rating_sub_indexes WHERE data_id = in_obj_id)) AS t) LOOP
                user_lang := (SELECT language FROM authentication_user WHERE id = row.id LIMIT 1);

                -- данные шаблона
                body := (SELECT get_notification_field(user_lang, in_tpl_code, 'body'));

                -- уведомление
                INSERT INTO notifications (type, level, verb_id, object_id, created_at, recipient_id, is_read, body)
                    VALUES ('rating', 'info', 1, in_obj_id, NOW(), row.id, FALSE,
                            FORMAT(body,
                                title,
                                to_char(data_obj.publish_date, 'YYYY-MM-DD'),
                                to_char(data_obj.open_date, 'YYYY-MM-DD'),
                                to_char(data_obj.close_date, 'YYYY-MM-DD'),
                                to_char(data_obj.result_publish_date, 'YYYY-MM-DD')
                                )
                            );
            END LOOP;

        END IF;

        -- В статусе открыто
        IF in_tpl_code = 'open' THEN

            -- Участники рейтинга
            FOR row IN (SELECT DISTINCT id FROM (SELECT id FROM authentication_user WHERE organization_id IN (SELECT organization_id FROM db_table_members WHERE rating_id = in_obj_id)) AS t) LOOP
                user_lang := (SELECT language FROM authentication_user WHERE id = row.id LIMIT 1);

                -- данные шаблона
                name := (SELECT get_notification_field(user_lang, in_tpl_code, 'name'));
                body := (SELECT get_notification_field(user_lang, in_tpl_code, 'body'));

                -- данные обьекта
                title := (SELECT get_rating_field(user_lang, in_obj_id, 'name'));

                -- уведомление
                INSERT INTO notifications (type, level, verb_id, object_id, created_at, recipient_id, is_read, body)
                    VALUES ('rating', 'info', 1, in_obj_id, NOW(), row.id, FALSE,
                            FORMAT(body,
                                title,
                                to_char(data_obj.publish_date, 'YYYY-MM-DD'),
                                to_char(data_obj.open_date, 'YYYY-MM-DD')
                                )
                            );

            END LOOP;

        END IF;


    END LOOP;
    RETURN TRUE;

END;
$function$