-- PROCEDURE: internal.pupdatetargetpersonid()

-- DROP PROCEDURE IF EXISTS internal.pupdatetargetpersonid();

CREATE OR REPLACE PROCEDURE internal.pupdatetargetpersonid(
	)
LANGUAGE 'plpgsql'
AS $BODY$

DECLARE
   var_TranCountOnEntry INTEGER 
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
-- BEGIN
    /*
    [7810 - Severity CRITICAL - PostgreSQL doesn't support the SET NOCOUNT. If need try another way to send message back to the client application.]
    SET NOCOUNT ON;
    */
    BEGIN
        IF var_TranCountOnEntry = 0 THEN
            /*
            [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
            BEGIN TRAN;
            */
--             BEGIN
--             END;
        END IF;

        DELETE FROM dbo.targetpersonid;
        WITH RECURSIVE results (targetpersonid, depth, personid)
        AS (SELECT
            p.personid, 0, p.personid
            FROM dbo.person AS p
        UNION ALL
        SELECT
            r.targetpersonid, r.depth + 1, m.personid
            FROM results AS r
            JOIN dbo.personmerge AS m
                ON r.personid = m.mergeintopersonid), maxdepth (personid, maxdepth)
        AS (SELECT
            personid, MAX(depth)
            FROM results
            GROUP BY personid)
        INSERT INTO dbo.targetpersonid (personid, targetpersonid, depth)
        SELECT
            r.personid, r.targetpersonid, r.depth
            FROM results AS r
            JOIN maxdepth AS d
                ON r.personid = d.personid AND r.depth = d.maxdepth;

	IF var_TranCountOnEntry = 0 THEN
            COMMIT;
        END IF;

--         EXCEPTION
--              WHEN OTHERS THEN

--                 IF var_TranCountOnEntry = 0 THEN
--                     ROLLBACK;
--                 END IF;
--                 RAISE;

    END;
-- END;
$BODY$;
