Есть задача перевести функционал БД с Oracle PL/SQL на PostgreSql (в основном это процедуры и функции).
Объем кода примерно 15 тысяч строк.


Пример кода ниже
CREATE OR REPLACE EDITIONABLE FUNCTION "ADDNEWMASTERDATAFUNCTION" (loadId VARCHAR2) RETURN number IS
tradingDate DATE;
result NUMBER(10);
BEGIN
INSERT ALL
INTO Temp_InputModify_To_MasterData (ID, ID_INPUT_MODIFY, ID_RECORD_MDM, ID_RECORD_SYSTEM, ID_REFERENCE, ID_TABLE_SYSTEM, ID_LOAD_TABLE, ID_LOAD, SIGN_ACTIVE, COUNT_ERRORS)
values (random_uuid(), ID_INPUT_MODIFY, ID_RECORD_MDM, ID_RECORD_SYSTEM, ID_REFERENCE, ID_TABLE_SYSTEM, ID_LOAD_TABLE, ID_LOAD, SIGN_ACTIVE, COUNT_ERRORS)
SELECT ID_RECORD_MDM, ID_INPUT_MODIFY, ID_RECORD_SYSTEM, ID_REFERENCE, ID_TABLE_SYSTEM, ID_LOAD_TABLE, ID_LOAD, SIGN_ACTIVE, COUNT_ERRORS
FROM
(
select
random_uuid() AS ID_RECORD_MDM,
im.Id as ID_INPUT_MODIFY,
im.ID_RECORD_SYSTEM as ID_RECORD_SYSTEM,
ts.ID_REFERENCE as ID_REFERENCE,
ts.id as ID_TABLE_SYSTEM,
lt.id as ID_LOAD_TABLE,
lt.ID_LOAD as ID_LOAD,
1 as SIGN_ACTIVE,
0 as COUNT_ERRORS
from Input_Modify im
left join ALLEGED_DUPLICATE ad on im.id = ad.ID_INPUT_RECORD
join LOAD_TABLE lt on lt.id = im.ID_LOAD_TABLE
join TABLE_SYSTEM ts on ts.id = lt.ID_TABLE_SYSTEM
where im.IS_APPROVED = 1 and
lt.ID_LOAD = loadId and
im.ID_RECORD_MDM is null and
(ad.ID_INPUT_RECORD is null or (ad.ID_INPUT_RECORD is not null and ad.IS_DUPLICATE = 0 and ad.ISMAINDUPLICATE = 0)) and
(ad.IS_APPROVED = 1 or ad.IS_APPROVED is null)
);

INSERT ALL
INTO master_data(ID, ID_REFERENCE, COUNT_ERRORS, SIGN_ACTIVE)
values (ID_RECORD_MDM, ID_REFERENCE, COUNT_ERRORS, SIGN_ACTIVE)
INTO origin_record(ID, ID_RECORD_MDM, ID_TABLE_SYSTEM, SIGN_ACTIVE, ID_RECORD_SYSTEM)
values (random_uuid(), ID_RECORD_MDM, ID_TABLE_SYSTEM, SIGN_ACTIVE, ID_RECORD_SYSTEM)
SELECT * FROM Temp_InputModify_To_MasterData temp where temp.ID_LOAD = loadId;

update
INPUT_MODIFY im
set
(Count_Added_New, ID_RECORD_MDM) =
(
select 1, ID_RECORD_MDM
from Temp_InputModify_To_MasterData temp
where temp.ID_INPUT_MODIFY = im.id and temp.ID_LOAD = loadId
) where exists(
select * from Temp_InputModify_To_MasterData temp where temp.ID_INPUT_MODIFY = im.id and temp.ID_LOAD = loadId
);

FOR loadTable IN (SELECT * FROM LOAD_TABLE where ID_LOAD = loadId)
LOOP
update Load_Table lt
set Count_Added_New =
(
select
count(*) as Count_Added_New
from Temp_InputModify_To_MasterData temp
where temp.ID_LOAD_TABLE = loadTable.id and temp.ID_LOAD = loadId
)
where lt.id = loadTable.id;
END LOOP;

update Load l
set
Count_Added_New =
(
select count(*)
from Temp_InputModify_To_MasterData temp
where temp.ID_LOAD = loadId
) where l.id = loadId;

insert all
into VALUE_MASTER_DATA(ID, ID_REFERENCE, ID_TABLE_SYSTEM, ID_RECORD_MDM, ID_ATTRIBUTE, VALUE, NUMBER_VIEW)
values (random_uuid(), ID_REFERENCE, ID_TABLE_SYSTEM, ID_RECORD_MDM, ID_ATTRIBUTE, VALUE, 0)
into MODIFY_MASTER_DATA(ID, ID_LOAD_TABLE, ID_RECORD_MDM, ID_RECORD_SYSTEM, ID_INPUT_MODIFY, ID_ATTRIBUTE, OLD_VALUE, NEW_VALUE, SIGN_MODIFY)
values (random_uuid(), ID_LOAD_TABLE, ID_RECORD_MDM, ID_RECORD_SYSTEM, ID_INPUT_MODIFY, ID_ATTRIBUTE, null, VALUE, 1)
select ID_REFERENCE, ID_TABLE_SYSTEM, ID_RECORD_MDM, ID_ATTRIBUTE, VALUE, ID_LOAD_TABLE, ID_RECORD_SYSTEM, ID_INPUT_MODIFY
from
(
select
temp.ID_REFERENCE as ID_REFERENCE,
temp.ID_TABLE_SYSTEM as ID_TABLE_SYSTEM,
temp.ID_RECORD_MDM as ID_RECORD_MDM,
temp.ID_LOAD_TABLE as ID_LOAD_TABLE,
temp.ID_RECORD_SYSTEM as ID_RECORD_SYSTEM,
temp.ID_INPUT_MODIFY as ID_INPUT_MODIFY,
ats.ID_ATTRIBUTE as ID_ATTRIBUTE,
vm.VALUE as VALUE
from Temp_InputModify_To_MasterData temp
join VALUE_MODIFY vm on vm.ID_INPUT_MODIFY = temp.ID_INPUT_MODIFY
join ATTRIBUTE_TABLE_SYSTEM ats on ats.id = vm.ID_ATTRIBUTE
where temp.ID_LOAD = loadId
);
SELECT count(*) INTO result FROM Temp_InputModify_To_MasterData WHERE ID_LOAD = loadId;
delete from Temp_InputModify_To_MasterData;
commit;
RETURN result;
END;