DROP PROCEDURE IF EXISTS public.sp_cleanfrlloremainsprocess;
DROP PROCEDURE IF EXISTS dbo.sp_cleanfrlloremainsprocess;
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2024 11 25
-- Description: Выгрузка данных в ФРЛЛО
-- =============================================
CREATE OR REPLACE PROCEDURE rds.sp_cleanfrlloremainsprocess(
    "ExportId" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    _num int := 1000;
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 = "ExportId";
    
    LOOP
        IF
            NOT EXISTS (SELECT 1 FROM tmp_obj LIMIT 1)
        THEN
            EXIT;
        END IF;
        UPDATE rds.t_export_frllo_remains_data AS fed
        SET frlloid     = NULL,
            frllostatus = NULL,
            frlloerror  = NULL
        WHERE EXISTS(SELECT 1 FROM tmp_obj t WHERE t.id = fed.exportfrlloremainsdataid AND t.num <= _num);
        
        DELETE FROM tmp_obj WHERE num <= _num;
        _num := _num + 1000;
    END LOOP;
END;
$$;