Примеры работы с POSTGRES (функции и их применение)

postgres.sql

---------------------------------
-- Функции для создания писем  --
---------------------------------

-------------------
-- MESSAGE GROUP --
-------------------
CREATE OR REPLACE FUNCTION create_close_message_group(external_id_p text, inn_p text)
    RETURNS bigint AS
$group_id$
DECLARE
    org_id        text;
    mess_group_id bigint;
    type_val      text = 'OTHERS';
    subject_val   text = 'Уведомление о закрытии';

BEGIN
    external_id_p = split_part(external_id_p, '-', 1);

    SELECT DISTINCT INTO org_id organization_id
    FROM tb_organization
    WHERE external_id like concat('%', external_id_p, '%')
      AND inn = inn_p;

    IF org_id ISNULL THEN
        RAISE EXCEPTION 'Organisation not found for `EXTERNAL_ID` (sem_id): %', external_id_p
            USING HINT = 'Проверьте наличие записи в таблице [tb_organization] с данным EXTERNAL_ID (sem_id)';
    ELSE
        INSERT INTO tb_message_group (organization_id, "type", subject, create_date, update_date)
        VALUES (org_id, type_val, subject_val, NOW(), NOW());
    END IF;

    SELECT DISTINCT INTO mess_group_id message_group_id
    FROM tb_message_group
    WHERE "type" = type_val;

    RAISE NOTICE '*** OK: Message Group was created with id: %', mess_group_id;

    RETURN mess_group_id;
END ;

$group_id$ LANGUAGE plpgsql;


-------------
-- MESSAGE --
-------------
CREATE OR REPLACE FUNCTION create_close_message(text_to_send text,
                                                inn_p text,
                                                number_of_contract text,
                                                date_of_contract text,
                                                external_id_p text)
    RETURNS bigint AS
$mes_id$
DECLARE
    mess_id         bigint;
    mess_group_id   bigint;
    number_template text = '%num%';
    date_template   text = '%date%';
    folder_val      text = 'IN';
    state_val       text = 'RECEIVED';

BEGIN
    SELECT INTO mess_group_id create_close_message_group(external_id_p, inn_p);

    IF mess_group_id ISNULL THEN
        RAISE EXCEPTION '!!! MESSAGE_GROUP_ID % not found in [tb_message_group] table: ', mess_group_id
            USING HINT = '>>> Проверьте наличие записи в таблице [tb_message_group] с данным MESSAGE_GROUP_ID';
    ELSE
        SELECT REPLACE(text_to_send, number_template, number_of_contract) into text_to_send;
        SELECT REPLACE(text_to_send, date_template, date_of_contract) into text_to_send;

        INSERT INTO tb_message (message_group_id, "text", folder, state, create_date, update_date)
        VALUES (mess_group_id, text_to_send, folder_val, state_val, NOW(), NOW()) RETURNING message_id INTO mess_id;
    END IF;

    RAISE NOTICE '>>> OK: Message was created with id: %', mess_id;

    RETURN mess_id;
END ;
$mes_id$ LANGUAGE plpgsql;


---------------- Выполнение:
                DO $inserting_data$
    DECLARE
        text_to_send text = 'Уважаемый клиент!
Nunc egestas, augue at pellentesque laoreet, felis eros vehicula leo, at malesuada velit leo quis pede. Curabitur nisi. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus.
';
    BEGIN
        -- ПАРАМЕТРЫ:                text_to_send,  inn,            number_of_contract, contract_date, external_id,
        PERFORM create_close_message(text_to_send, '164413158767', 'МБ/12-00/4/18-001', '28.09.2018', '66666666666-NOM');

    END; $inserting_data$;


-- ПРОВЕРКА УВЕДОМЛЕНИЙ, ОЖИДАЮЩИХ ОТПРАВКИ (увидеть какие сообщения ожидают отправку):
SELECT *
FROM tb_message
WHERE folder = 'IN'
  AND state = 'RECEIVED';




--------------------
-- pg_sleep ПРИМЕР: 
---------------------
CREATE FUNCTION stable_foo()
    RETURNS void
AS $$
SELECT pg_sleep(1);
$$
    LANGUAGE sql
    STABLE;

SELECT * FROM immutable_foo() AS a, immutable_foo() AS b;
SELECT immutable_foo(), immutable_foo() FROM ( VALUES (1) ) AS t(x);
SELECT immutable_foo()::text || immutable_foo()::text;

SELECT immutable_foo() FROM ( VALUES (1),(2) ) AS t(x);