migration to new view transactions

transactions.sql

BEGIN TRANSACTION;
alter table transactions
    add tr_document_password_owner        text,
    add tr_doctype_id                     guid,
    add tr_document_date                  date,
    add tr_document_number                text,
    add tr_document_description           text,
    add tr_document_owner_name            text,
    add tr_document_owner_user_id         guid,
    add tr_document_owner_structure_id    guid,
    add tr_document_owner_mailbox_id      guid,
    add tr_document_contragent_name       text,
    add tr_document_total_sum             numeric(20, 2),
    add tr_contragent_tin                 text,
    add tr_owner_tin                      tin,
    add tr_document_vat_sum               numeric(20, 2),
    add tr_confidant_tin                  tin,
    add tr_document_total_sum_without_vat numeric(20, 2),
    add st_structure_name                 text,
    add st_structure_id                   guid;



create function transact_copy_doc() returns trigger
    language plpgsql
as
$$
BEGIN
    UPDATE
        transactions
    SET tr_document_password_owner        = doc.document_password_owner,
        tr_doctype_id                     = doc.doctype_id,
        tr_document_date                  = doc.document_date,
        tr_document_number                = doc.document_number,
        tr_document_description           = doc.document_description,
        tr_document_owner_name            = doc.document_owner_name,
        tr_document_owner_user_id         = doc.document_owner_user_id,
        tr_document_owner_structure_id    = doc.document_owner_structure_id,
        tr_document_owner_mailbox_id      = doc.document_owner_mailbox_id,
        tr_document_total_sum             = doc.document_total_sum,
        tr_contragent_tin                 = doc.contragent_tin,
        tr_owner_tin                      = doc.owner_tin,
        tr_document_vat_sum               = doc.document_vat_sum,
        tr_confidant_tin                  = doc.confidant_tin,
        tr_document_total_sum_without_vat = doc.document_total_sum_without_vat,
        tr_document_contragent_name       = doc.document_contragent_name,
        st_structure_name                 = doc.structure_name,
        st_structure_id                   = doc.structure_id
    FROM (SELECT document_password_owner,
                 doctype_id,
                 document_date,
                 document_number,
                 document_description,
                 document_owner_name,
                 document_owner_user_id,
                 document_owner_structure_id,
                 document_owner_mailbox_id,
                 document_total_sum,
                 contragent_tin,
                 owner_tin,
                 document_vat_sum,
                 confidant_tin,
                 document_total_sum_without_vat,
                 document_contragent_name,
                 structure_name,
                 structure_id
          from documents
                   left join oauth_m24.structures str on str.structure_id = new.departament_id
          where documents.document_id = new.document_id
          limit 1) as doc
    WHERE transactions.document_id = new.document_id;
    return null;
END ;
$$;

CREATE TRIGGER trans_update
    AFTER INSERT
    ON transactions
    FOR EACH ROW
EXECUTE PROCEDURE transact_copy_doc();

BEGIN TRANSACTION;
UPDATE
    transactions
SET tr_document_password_owner        = doc.document_password_owner,
    tr_doctype_id                     = doc.doctype_id,
    tr_document_date                  = doc.document_date,
    tr_document_number                = doc.document_number,
    tr_document_description           = doc.document_description,
    tr_document_owner_name            = doc.document_owner_name,
    tr_document_owner_user_id         = doc.document_owner_user_id,
    tr_document_owner_structure_id    = doc.document_owner_structure_id,
    tr_document_owner_mailbox_id      = doc.document_owner_mailbox_id,
    tr_document_total_sum             = doc.document_total_sum,
    tr_contragent_tin                 = doc.contragent_tin,
    tr_owner_tin                      = doc.owner_tin,
    tr_document_vat_sum               = doc.document_vat_sum,
    tr_confidant_tin                  = doc.confidant_tin,
    tr_document_total_sum_without_vat = doc.document_total_sum_without_vat,
    tr_document_contragent_name       = doc.document_contragent_name,
    st_structure_name                 = doc.structure_name,
    st_structure_id                   = doc.structure_id
FROM (SELECT document_password_owner,
             documents.document_id,
             doctype_id,
             document_date,
             document_number,
             document_description,
             document_owner_name,
             document_owner_user_id,
             document_owner_structure_id,
             document_owner_mailbox_id,
             document_total_sum,
             contragent_tin,
             owner_tin,
             document_vat_sum,
             confidant_tin,
             document_total_sum_without_vat,
             document_contragent_name,
             str.structure_name,
             str.structure_id
      from documents
               left join transactions t on documents.document_id = t.document_id
               left join oauth_m24.structures str on str.structure_id = t.departament_id
     ) as doc
WHERE transactions.document_id = doc.document_id;
COMMIT;

create or replace view main_documents_grid
            (transaction_id, transaction_owner_mailbox_id, transaction_owner_structure_id, transaction_owner_user_id,
             transaction_owner_status, transaction_ca_structure_id, transaction_ca_user_id, created_at,
             transaction_operation, departament_id, current_state, document_password_owner, doctype_id, document_id,
             document_date, document_number, document_description, document_owner_name, document_owner_user_id,
             document_owner_structure_id, document_owner_mailbox_id, document_contragent_name, document_total_sum,
             contragent_tin, owner_tin, document_vat_sum, confidant_tin, document_total_sum_without_vat, structure_name,
             structure_id)
as
SELECT tr_m.transaction_id,
       tr_m.transaction_owner_mailbox_id,
       tr_m.transaction_owner_structure_id,
       tr_m.transaction_owner_user_id,
       tr_m.transaction_owner_status,
       tr_m.transaction_ca_structure_id,
       tr_m.transaction_ca_user_id,
       tr_m.created_at,
       tr_m.transaction_operation,
       tr_m.departament_id,
       tr_m.current_state,
       tr_m.tr_document_password_owner        AS document_password_owner,
       tr_m.tr_doctype_id                     AS doctype_id,
       tr_m.document_id,
       tr_m.tr_document_date                  AS document_date,
       tr_m.tr_document_number                AS document_number,
       tr_m.tr_document_description           AS document_description,
       tr_m.tr_document_owner_name            AS document_owner_name,
       tr_m.tr_document_owner_user_id         AS document_owner_user_id,
       tr_m.tr_document_owner_structure_id    AS document_owner_structure_id,
       tr_m.tr_document_owner_mailbox_id      AS document_owner_mailbox_id,
       tr_m.tr_document_contragent_name       AS document_contragent_name,
       tr_m.tr_document_total_sum             AS document_total_sum,
       tr_m.tr_contragent_tin                 AS contragent_tin,
       tr_m.tr_owner_tin                      AS owner_tin,
       tr_m.tr_document_vat_sum               AS document_vat_sum,
       tr_m.tr_confidant_tin                  AS confidant_tin,
       tr_m.tr_document_total_sum_without_vat AS document_total_sum_without_vat,
       tr_m.st_structure_name                 AS structure_name,
       tr_m.st_structure_id                   AS structure_id

FROM transactions tr_m
         LEFT JOIN transactions tr_t ON tr_m.document_id = tr_t.document_id AND tr_m.created_at < tr_t.created_at AND
                                        tr_t.transaction_owner_mailbox_id = tr_m.transaction_owner_mailbox_id
WHERE tr_t.created_at IS NULL;

alter table main_documents_grid
    owner to postgres;

COMMIT;