KVQzRdrC

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 not null );
9
10    IF (SELECT COUNT(*) FROM temp_warehouseoperation) = 0::BIGINT 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
18WITH CTE AS(
19            SELECT oper.id
20            FROM warehouse_warehouseoperation oper
21            LEFT JOIN warehouse_warehouseproductstore ww on oper.id = ww.operation_id
22            WHERE oper.document_id = _document_id
23            AND ww.operation_id = oper.id
24        )
25        DELETE FROM warehouse_warehouseoperation oper
26        WHERE EXISTS
27            (
28              SELECT NULL FROM CTE WHERE cte.id = oper.id
29            );
30
31        UPDATE temp_warehouseoperation SET
32            document_id = _document_id;
33
34        i := i + 1;
35        IF i % 10000 = 0 THEN
36            COMMIT;
37        END IF;
38        _document_id =   (
39            SELECT document_id
40            FROM warehouse_warehouseoperation
41            WHERE document_id > _document_id
42            ORDER BY document_id
43            LIMIT 1
44        );
45        EXIT when _document_id is null;
46    END LOOP;
47    COMMIT;
48END;
49$BODY$;