Como realizar atualizações em linhas recém-criadas dentro da mesma transação?

Link do violino: https://dbfiddle.uk/EOE627Oa

Tabelas

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  login text NOT NULL,
  password text NOT NULL,
  email text,
  init_index bigint,
  parent_id bigint REFERENCES accounts
);

Dados de entrada

[
  {
    "login": "11EB19631A",
    "password": "AE128AADEF97F1E54021",
    "reference_id": 1
  },
  {
    "login": "3ED4ECBBC9",
    "password": "E67EDDB6033D02140BB4",
    "email": "a@b",
    "reference_id": 2,
    "parent_reference": 1
  },
  {
    "login": "C86D7E2CF0",
    "password": "75404617C000A0EB070C",
    "reference_id": 3,
    "parent_reference": 2
  },
  {
    "login": "C51D77BF87",
    "password": "605509993A05EE393081",
    "email": null,
    "reference_id": 4,
    "parent_reference": 2
  },
  {
    "login": "2BAB5AA533",
    "password": "DFCAB818D812B1F8F761",
    "reference_id": 5,
    "parent_reference": 3
  },
  {
    "login": "4229D47E2C",
    "password": "CE4E14ED6AD77CBC71B5",
    "email": "b@c",
    "reference_id": 6,
    "parent_reference": 2
  }
]

Consulta

WITH account_inits AS (
  SELECT
    row_number () OVER () as init_index,
    login,
    password,
    email,
    reference_id,
    parent_reference
  FROM
    json_to_recordset(
      $json$$json$
    ) AS input_init(
      login text,
      password text,
      email text,
      reference_id int,
      parent_reference int
    )
),
-- create new accounts
new_accounts AS (
  INSERT INTO accounts
    (
      init_index,
      login,
      password,
      email
    )
  SELECT
    init_index,
    login,
    password,
    email
  FROM
    account_inits
  RETURNING
    *
),
parent_id_pairs AS (
    SELECT
      new_accounts.id,
      account_inits.reference_id
    FROM
      new_accounts
      INNER JOIN
      account_inits
      ON
        account_inits.reference_id IN (
          SELECT DISTINCT
            parent_reference AS reference_id
          FROM
            account_inits
        )
        AND
        new_accounts.init_index = account_inits.init_index
),
account_updates AS (
  SELECT
    new_accounts.id,
    parent_id_pairs.id AS parent_id
  FROM
    new_accounts
    INNER JOIN
    account_inits
    ON
      account_inits.parent_reference IS NOT NULL
      AND
      new_accounts.init_index = account_inits.init_index
    INNER JOIN
    parent_id_pairs
    ON
      account_inits.parent_reference = parent_id_pairs.reference_id
),
updated_accounts AS (
  UPDATE
    accounts
  SET
    parent_id = account_updates.parent_id
  FROM
    account_updates
  WHERE
    account_updates.id = accounts.id
  RETURNING
    *
)
SELECT
  *
FROM
  updated_accounts
;

SELECT
  id,
  init_index,
  parent_id
FROM
  accounts
;

O problema

A tabela em questão tem uma chave estrangeira para ela mesma para expressar um relacionamento semelhante a uma árvore entre suas linhas. Mas essas relações podem ser conhecidas antes mesmo de serem inseridas no lote. Portanto reference_ide parent_referencesão "identificadores de lote" que são gerados pelo aplicativo para mostrar as referências entre inicializadores.

No entanto, a base de dados não pode expressar esta relação no INSERTenunciado por motivos óbvios. Então pensei em executar uma UPDATEetapa separada em um CTE para linhas recém-criadas. Mas não funciona, apesar de todos os dados de entrada estarem corretos (você pode verificar isso SELECTdigitando os CTEs usados ​​no UPDATECTE), então o problema está no UPDATEpróprio bloco. Os documentos listam duas sintaxes diferentes para executar a UPDATEpartir de um conjunto de registros. Eu tentei os dois e nem apliquei nas parent_idlinhas criadas.