Q7R08DdE

1CREATE OR REPLACE PROCEDURE warehouseoperation_delete_duplicate()
2    LANGUAGE plpgsql
3AS $BODY$
4    DECLARE
5        _document_id uuid = '00000000-0000-0000-0000-000000000000';
6        i int = 0;
7BEGIN
8    CREATE UNLOGGED TABLE IF NOT EXISTS temp_warehouseoperation(document_id uuid);
9
10    IF (SELECT 1 FROM temp_warehouseoperation LIMIT 1) IS NULL THEN
11        INSERT INTO temp_warehouseoperation (document_id) VALUES (_document_id);
12        COMMIT;
13    ELSE
14        _document_id := (SELECT document_id FROM temp_warehouseoperation LIMIT 1);
15    END IF;
16
17    LOOP
18        WITH CTE AS(
19            SELECT oper.id, ROW_NUMBER() OVER (PARTITION BY oper.sgtin, oper.document_id) RN
20            FROM warehouse_warehouseoperation oper
21            WHERE oper.document_id = _document_id
22        )
23        DELETE FROM warehouse_warehouseoperation oper
24        WHERE EXISTS
25            (
26              SELECT NULL FROM CTE WHERE cte.id = oper.id AND cte.RN > 1
27            );
28
29        UPDATE temp_warehouseoperation SET
30            document_id = _document_id;
31
32        i := i + 1;
33        IF i % 10000 = 0 THEN
34            COMMIT;
35        END IF;
36
37        _document_id =   (
38            SELECT id
39            FROM warehouse_warehouseoperation
40            WHERE id > _document_id
41            ORDER BY id
42            LIMIT 1
43        );
44    END LOOP;
45    COMMIT;
46END;
47$BODY$;