Biller Builder's questions

小提琴链接：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创建的行。

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

表格和类型

CREATE TYPE profile_init AS (
  name text,
  description text
);

CREATE TYPE account_init AS (
  login text,
  password text,
  email text,
  -- an array of `profile_init`s
  profile_inits json
);

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

CREATE TABLE profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  init_index bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  name text,
  description text
);

CREATE TABLE account_profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  account_id bigint NOT NULL REFERENCES accounts,
  profile_id bigint NOT NULL REFERENCES profiles
);

输入数据

[
  {
    "login": "3ED4ECBBC9",
    "password": "E67EDDB6033D02140BB4",
    "email": "a@b",
    "profile_inits": null
  },
  {
    "login": "C86D7E2CF0",
    "password": "75404617C000A0EB070C",
    "profile_inits": [
      {
        "name":"C1B549E",
        "description":"1313CB6F876EA62837A15C20D78A8FA3FC926008FA289AE722"
      }
    ]
  },
  {
    "login": "C51D77BF87",
    "password": "605509993A05EE393081",
    "email": null,
    "profile_inits": [
      {},
      { "name": "2E35813" },
      { "description": "AF2A372263233827253DE19DB8E12798EEF59C311CFE9CEFAB" },
      { "name": "EE899CD", "description": null },
      { "name": null, "description": "CB4CEE63293E16988E58805FED223943E7CAFDF6F417393B30" }
    ]
  },
  {
    "login": "4229D47E2C",
    "password": "CE4E14ED6AD77CBC71B5",
    "email": "b@c",
    "profile_inits": [
      {
        "name": "956F079",
        "description": "BC1E803500773076940C0052D289AAB9952DD47D4954447C8E"
      },
      {
        "name": "99B327B",
        "description": "C4C9702836B1E05CC49D4E205CC8292D017FF3C1BE179CA435"
      },
      {
        "name": "D8EF1A8",
        "description": "554A01F7DBA0C889AF014CBF7EA938DED791FB3A3A50C932E5"
      },
      {
        "name": "91151DB",
        "description": "A86CA083BD509F23FD515C045C8BE32D4B57E3A3940FB8BFD4"
      },
      {
        "name": "31EC363",
        "description": "7008C341EDDBB93B3B1D5904E5EF1FCAE01EB25AC2A5E51761"
      },
      {
        "name": "E7E11D7",
        "description": "0C313B46ADD0E946D24854EA5651379C9D4D56656BFBC6312F"
      },
      {
        "name": "32F1C7C",
        "description": "5B08641B0A3F7359C929E14EEE58502DACA40CF830FF923A7B"
      },
      {
        "name": "23C85ED",
        "description": "7CB34E28022DD84E96D9825CD5E0CB0774D548F56762CF2A6C"
      },
      {
        "name": "1800D37",
        "description": "589850742C3A3A1FC2E9130494069847CCB426636B7F7440F4"
      }
    ]
  }
]

询问

-- transform top level json array into a set of records
WITH account_inits AS (
  SELECT
    row_number () OVER () as account_init_index,
    login,
    password,
    email,
    profile_inits
  FROM
    json_to_recordset(
      $json$
      ...
      $json$
    ) AS input_init(
      login text,
      password text,
      email text,
      profile_inits json
    )
),
-- transform nested profile inits into profile inits
profile_inits AS (
  SELECT
    -- need a reference to original account init
    -- for later joins
    account_init_index,
    row_number () OVER () as profile_init_index,
    name,
    description
    -- this json array has to be expanded
    -- and joined on `account_init_index`
    -- on itself I presume
    profile_inits
  FROM
    account_inits
),
-- create new accounts
new_accounts AS (
  INSERT INTO accounts
    (
      init_index,
      login,
      password,
      email
    )
  SELECT
    account_init_index AS init_index,
    login,
    password,
    email
  FROM
    account_inits
  RETURNING
    *
),
-- create new profiles
new_profiles AS (
  INSERT INTO profiles
    (
      init_index,
      name,
      description
    )
  SELECT
    profile_init_index AS init_index,
    name,
    description
  FROM
    profile_inits
  RETURNING
    *
),
-- create new profile account relations
new_account_profiles AS (
  -- join new accounts and their inits
  WITH input_accounts AS (
    SELECT
      account_inits.account_init_index,
      new_accounts.id AS account_id
    FROM
      account_inits
      INNER JOIN
      new_accounts
      ON
        account_inits.account_init_index = new_accounts.init_index
  ),
  -- join new profiles and their inits
  input_profiles AS  (
    SELECT
      profile_inits.account_init_index,
      profile_inits.profile_init_index,
      new_profiles.id AS profile_id
    FROM
      profile_inits
      INNER JOIN
      new_profiles
      ON
        profile_inits.profile_init_index = new_profiles.init_index

  ),
  -- join inputs
  account_profile_pairs AS (
    SELECT
      input_accounts.account_id,
      input_profiles.profile_id
    FROM
      input_accounts
      INNER JOIN
      input_profiles
      ON
        input_accounts.account_init_index = input_profiles.account_init_index
  )
  INSERT INTO account_profiles
    (
      account_id,
      profile_id
    )
  SELECT
    account_id,
    profile_id
  FROM
    account_profile_pairs
  RETURNING
    *
)
SELECT
  (
    SELECT
      count(*)
    FROM
      new_accounts
  ) AS new_accounts_count,
  (
    SELECT
      count(*)
    FROM
      new_profiles
  ) AS new_profiles_count,
  (
    SELECT
      count(*)
    FROM
      new_account_profiles
  ) AS new_account_profiles_count
;

问题

在profile_initsCTE，我需要转换此表：

进入这个：

因此可以选择它用于new_profilesCTE 中的插入查询，然后在初始化器和创建的实体 ID 之间提供映射以插入 realtions。

数据库示例：https ://dbfiddle.uk/sERgZPiB

表格和类型

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  login text NOT NULL,
  password text NOT NULL,
  email text
);

CREATE TYPE account_init AS (
  login text,
  password text,
  email text
);

辅助函数

-- random string generator
-- https://www.simononsoftware.com/random-string-in-postgresql/#combined-md5-and-sql
CREATE FUNCTION random_string(length integer)
RETURNS text
LANGUAGE SQL
AS $$ 
  SELECT upper(
    substring(
      (
        SELECT 
          string_agg(
            md5(
              CAST (random() AS text)
            ), 
            ''
          )
        FROM 
          generate_series(
            1,
            CAST (CEIL(length / 32.) AS integer)
          ) 
      ), 
      1, 
      length
    ) 
  );
$$;

--sequence generator
CREATE FUNCTION create_series(amount integer)
RETURNS TABLE (
  index_id bigint
)
LANGUAGE SQL
AS $BODY$
  SELECT
    generate_series AS index_id
  FROM
    generate_series(1, amount)
$BODY$;

实体函数

CREATE FUNCTION get_accounts(
  pagination_limit bigint DEFAULT 25,
  pagination_offset bigint DEFAULT 0,
  account_ids bigint[] DEFAULT NULL
)
RETURNS TABLE (
  id bigint,
  login text,
  password text,
  email text
)
LANGUAGE SQL
AS $BODY$
  WITH input_accounts AS (
    SELECT
      id,
      login,
      password,
      email
    FROM
      accounts
    WHERE
      account_ids IS NULL OR id = ANY (account_ids)
    ORDER BY
      id
    LIMIT pagination_limit
    OFFSET pagination_offset
  )
  SELECT
    id,
    login,
    password,
    email
  FROM
    input_accounts
  ORDER BY
    id
$BODY$;

CREATE FUNCTION create_accounts(
  account_inits account_init[]
)
RETURNS TABLE (
  id bigint,
  login text,
  password text,
  email text
)
LANGUAGE SQL
AS $BODY$
  WITH new_accounts AS (
    INSERT INTO accounts ( 
      login, 
      password, 
      email 
    )
    SELECT 
      login, 
      password, 
      email
    FROM 
      unnest(account_inits)
    RETURNING
      id
  )
  SELECT
    id,
    login,
    password,
    email
  FROM
    get_accounts(
      NULL,
      NULL,
      ARRAY(
        SELECT
          id
        FROM
          new_accounts
      )
    )
  ORDER BY
    id
$BODY$;

用法

WITH account_inits AS (
  SELECT
    index_id,
    (random_string(10)) AS login,
    (random_string(50)) AS password,
    NULL AS email
  FROM
    create_series(10)
)
SELECT
  id,
  login,
  password,
  email
FROM
  create_accounts(
    CAST (
      (
        SELECT
          login,
          password,
          email
        FROM
          account_inits
      ) AS account_init[]
    ) 
  )
ORDER BY
  id ASC
;

当前代码返回

ERROR:  subquery must return only one column
LINE 31:       (

我试过了array_agg()，array()他们都返回了不同的错误。我考虑过使用json类型作为参数，但这会掩盖读取和调试的函数签名，所以我宁愿不这样做。

示例：https ://dbfiddle.uk/bCSwVpd9

数据库：

CREATE TABLE entities (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TYPE entity_id AS (
  id bigint
);

功能：

CREATE FUNCTION get_entities (
  pagination_limit bigint DEFAULT 25,
  pagination_offset bigint DEFAULT 0,
  entity_ids entity_id DEFAULT NULL
)
RETURNS TABLE (
  id bigint
)
LANGUAGE SQL
AS $BODY$
  WITH input_entities AS (
    SELECT
      id
    FROM
      entities
    WHERE
      -- filter by id list if provided
      entity_ids IS NULL OR id IN (
        SELECT
          id
        FROM
          entity_ids
      )
    ORDER BY
      id ASC
    LIMIT pagination_limit
    OFFSET pagination_offset
  )
  SELECT
    id
  FROM
    input_entities
  ORDER BY
    id
$BODY$;

关键是我想编写一个分页多选函数，它可以从分页信息和一组 ID 中工作。它上面的函数的问题崩溃了：

ERROR:  relation "entity_ids" does not exist
LINE 22:           entity_ids

对这个问题也有类似的反应：first，second。但是，它们围绕作为标识符字符串的参数，而不是复合记录类型和 use plpgsql，这可能不重要，也可能不重要。

给定这样的表格：https ://dbfiddle.uk/Z8hOhnYG

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
);

CREATE TABLE profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE account_profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id bigint NOT NULL REFERENCES accounts,
  profile_id bigint NOT NULL REFERENCES profiles
);

要求是：

• 每个帐户必须始终至少有一个与之关联的个人资料。
• 因此，新帐户必须始终创建新配置文件并将其关系行添加到数据库
• 当整个操作因任何原因失败时，什么都不会创建。

所以为了批处理的目的，我想把它写成一个多插入查询，所以我想出了这个算法：

1. 创建一系列长度与账户数相同的ID
2. 添加帐户
3. 加入该系列的新帐户
4. 添加配置文件
5. 加入该系列的新个人资料
6. 在其系列 ID 上加入帐户和个人资料系列表并将结果插入到关系表中
7. 返回新帐户

像这样的表：

CREATE TABLE test_1 (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE test_2 (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE test_refs (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  id_1 bigint NOT NULL REFERENCES test_1,
  id_2 bigint NOT NULL REFERENCES test_2
);

和这样的插入查询：

WITH new_test_1_rows AS (
  INSERT INTO test_1
  DEFAULT VALUES
  RETURNING *
), new_test_2_rows AS (
  INSERT INTO test_2
  DEFAULT VALUES
  RETURNING *
), test_row_pairs AS (
  INSERT INTO test_refs
   ( id_1, id_2 )
  VALUES
    (
      (SELECT id FROM new_test_1_rows),
      (SELECT id FROM new_test_1_rows)
    )
  RETURNING *
)
SELECT *
FROM test_row_pairs

基本上它的作用：

• 插入一行test_1
• 插入一行test_2
• 将他们的 ID 对插入test_refs

问题是我想将查询重写为多插入查询，即插入的行将行插入n，然后为插入的值创建行。为此，我需要知道 CTE 中的行索引，因此它可以用作连接键。你可以在条款中做些什么吗？test_1ntest_2ntest_refsRETURNING