DROP PROCEDURE IF EXISTS public.sp_cleanfrlloremainsprocess;
DROP PROCEDURE IF EXISTS dbo.sp_cleanfrlloremainsprocess;
DROP PROCEDURE IF EXISTS rds.sp_cleanfrlloremainsprocess;

CREATE OR REPLACE PROCEDURE rds.sp_cleanfrlloremainsprocess(
    "ExportId" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_num      int  := 1000;
    p_exportid uuid := "ExportId";
BEGIN
    
    CREATE TEMP TABLE tmp_obj
    (
        id  integer NOT NULL UNIQUE,
        num serial
    );
    
    INSERT INTO tmp_obj(id)
    SELECT DISTINCT exportfrlloremainsdataid
    FROM rds.t_export_frllo_remains_data fed
    WHERE fed.exportfrlloremains = p_exportid;
    
    LOOP
        IF
            NOT EXISTS (SELECT 1 FROM tmp_obj LIMIT 1)
        THEN
            EXIT;
        END IF;
        
        UPDATE rds.t_export_frllo_remains_data fed
        SET frlloid     = NULL,
            frllostatus = NULL,
            frlloerror  = NULL
        WHERE EXISTS(SELECT 1 FROM tmp_obj t WHERE t.id = exportfrlloremainsdataid AND t.num <= v_num)
          AND COALESCE(fed.frllostatus, 1) <> 1
          AND exportfrlloremains = p_exportid;
        
        DELETE FROM tmp_obj WHERE num <= v_num;
        v_num := v_num + 1000;
    END LOOP;
END;
$$
DROP PROCEDURE IF EXISTS public.sp_cleanfrlloremainsprocess;
DROP PROCEDURE IF EXISTS dbo.sp_cleanfrlloremainsprocess;
DROP PROCEDURE IF EXISTS rds.sp_cleanfrlloremainsprocess;

CREATE OR REPLACE PROCEDURE rds.sp_cleanfrlloremainsprocess(
    "ExportId" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_num      int  := 1000;
    p_exportid uuid := "ExportId";
BEGIN
    
    CREATE TEMP TABLE tmp_obj
    (
        id  integer NOT NULL UNIQUE,
        num serial
    );
    
    INSERT INTO tmp_obj(id)
    SELECT DISTINCT exportfrlloremainsdataid
    FROM rds.t_export_frllo_remains_data fed
    WHERE fed.exportfrlloremains = p_exportid;
    
    LOOP
        IF
            NOT EXISTS (SELECT 1 FROM tmp_obj LIMIT 1)
        THEN
            EXIT;
        END IF;
        
        UPDATE rds.t_export_frllo_remains_data fed
        SET frlloid     = NULL,
            frllostatus = NULL,
            frlloerror  = NULL
        WHERE EXISTS(SELECT 1 FROM tmp_obj t WHERE t.id = exportfrlloremainsdataid AND t.num <= v_num)
          AND COALESCE(fed.frllostatus, 1) <> 1
          AND exportfrlloremains = p_exportid;
        
        DELETE FROM tmp_obj WHERE num <= v_num;
        v_num := v_num + 1000;
    END LOOP;
END;
$$
