DROP PROCEDURE IF EXISTS public.sp_deletefrllo;
DROP PROCEDURE IF EXISTS dbo.sp_deletefrllo;
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2021 02 17
-- Description: Удаление данных ФРЛЛО
-- =============================================
CREATE OR REPLACE PROCEDURE rds.sp_deletefrllo(
    "Id" uuid DEFAULT NULL::uuid,
    "RemovalAllowed" boolean = false
)
    LANGUAGE plpgsql
AS
$$
declare 
    p_id                uuid := "Id";
    p_removalallowed    boolean  := "RemovalAllowed";
BEGIN
    if (exists (select 1 from RDS.T_FRLLO_MESSAGE where exportfrllo = p_id))
    then
        if (p_removalallowed = true)
        then
            delete from rds.t_frllo_message where exportfrllo = p_id;
        else 
            raise exception 'Нельзя удалить запись, так как существуют связанные сообщения';
            return;
        end if;
    end if;

    LOOP
        EXIT WHEN NOT EXISTS(SELECT 1 FROM rds.t_export_frllo_data WHERE exportfrllo = p_id LIMIT 1)
        ;WITH to_delete AS (SELECT exportfrllodataid AS id
                           FROM rds.t_export_frllo_data
                           WHERE exportfrllo = p_id
                           LIMIT 10000
                          )
        DELETE
        FROM rds.t_export_frllo_data d
            USING to_delete
        WHERE d.exportfrllodataid = to_delete.id;
    END LOOP;
    
    LOOP
        EXIT WHEN NOT EXISTS(SELECT 1 FROM rds.t_export_frllo WHERE exportfrlloid = p_id LIMIT 1)
        
        ;WITH to_delete AS (SELECT f.exportfrlloid
                           FROM rds.t_export_frllo f
                           WHERE f.exportfrlloid = p_id
                           LIMIT 10000
                          )
        DELETE
        FROM rds.t_export_frllo f
            USING to_delete
        WHERE f.exportfrlloid = to_delete.exportfrlloid;
    
    
    END LOOP;
END;
$$;