主页 / dba / 问题 / 318982
Accepted
Biller Builder
Asked: 2022-10-31 11:57:36 +0800 CST

如何在同一事务中对新创建的行执行更新？

  • 772

小提琴链接：https ://dbfiddle.uk/EOE627Oa

表

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
);

输入数据

[
  {
    "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
  }
]

询问

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
;

问题

有问题的表有一个外键来表达其行之间的树状关系。但是这些关系甚至在批量插入之前就可以知道。因此reference_id和parent_reference是由应用程序生成的“批处理标识符”，用于显示初始化程序之间的引用。

INSERT然而，由于显而易见的原因，数据库不能在语句中表达这种关系。所以我考虑UPDATE在 CTE 中为新创建的行运行一个单独的步骤。但它不起作用，尽管所有输入数据都是正确的（您可以通过SELECT'ing CTE 中使用的UPDATECTE 来检查），所以问题出在UPDATE块本身。文档列出UPDATE了从一组记录中执行的两种不同语法。我尝试了它们，但都没有将它们应用于parent_id创建的行。

postgresql

1 个回答

  1. Best Answer

    你要求更新...

    在同一笔交易中

    但是，更具体地说，您正在尝试在同一个命令中执行此操作，这会产生很大的不同。

    解决方案

    长话短说：

    WITH account_inits AS (
   SELECT nextval(pg_get_serial_sequence('public.accounts', 'id')) AS id, *  -- !!!
   FROM   json_to_recordset($your_json_input)
   AS input_init(login text, password text, email text, reference_id int, parent_reference int)
   )
INSERT INTO public.accounts
        (id,   login,   password,   email,   parent_id) OVERRIDING SYSTEM VALUE  -- !!!
SELECT a.id, a.login, a.password, a.email, p.id                     -- !!!
FROM   account_inits a
LEFT   JOIN account_inits p ON p.reference_id = a.parent_reference  -- !!!
RETURNING *;

    小提琴

    就这样。注意简化小提琴中添加的细节。

    这假定每个插入批次内的引用完整性reference_id：任何实际被 a 引用的内容都parent_reference必须唯一地存在于批次中。

    为什么？如何？

    您最初尝试的主要问题在这里解释

    解决方案是运行单独的语句（在同一事务中）。但不要去那里。按照建议使用更简单nextval()的方法：预先从序列中获取新的序列号。A LEFT JOINto self 完成剩下的工作。

    有关的：

    既然你id是GENERATED ALWAYS AS IDENTITY我们必须使用OVERRIDING SYSTEM VALUE的演示。看：

    • 2

相关问题