Biller Builder's questions

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.

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

Tabelas e tipos

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

Dados de entrada

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

Consulta

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

O problema

No profile_initsCTE eu preciso transformar esta tabela:

nesta:

Assim, pode ser selecionado para a consulta de inserção emnew_profiles CTE e posteriormente fornecer um mapeamento entre inicializadores e IDs de entidade criados para inserir relações.

Exemplo de banco de dados: https://dbfiddle.uk/sERgZPiB

Tabelas e tipos

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

Funções auxiliares

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

Funções de entidade

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

Uso

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
;

O código atual retorna

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

Eu tentei array_agg()e array()ambos retornando erros diferentes. Eu pensei em usar jsontype para o argumento, mas isso irá obscurecer a assinatura da função tanto para leitura quanto para depuração, então prefiro não.

Exemplo: https://dbfiddle.uk/bCSwVpd9

Base de dados:

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

CREATE TYPE entity_id AS (
  id bigint
);

Função:

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

A muleta é que eu quero escrever uma função de seleção múltipla paginada que possa funcionar tanto a partir de informações de paginação quanto de um conjunto de ids. O problema com a função acima falha com:

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

Existem respostas semelhantes para este problema: first , second . No entanto, eles giram em torno do argumento ser uma string identificadora, não um tipo de registro composto e também usar plpgsql, que pode ou pode não ser importante.

Dadas as tabelas assim: 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
);

Os requisitos são:

• cada conta deve sempre ter pelo menos um perfil vinculado a ela.
• portanto, uma nova conta deve sempre criar um novo perfil e adicionar sua linha de relação ao banco de dados
• nada é criado quando toda a operação falha por qualquer motivo.

Portanto, para fins de lote, gostaria de escrevê-lo como uma consulta de várias inserções e criei este algoritmo:

1. crie uma série de IDs com o mesmo comprimento que o número de contas
2. adicionar contas
3. juntar novas contas com a série
4. adicionar perfis
5. juntar novos perfis com a série
6. junte as tabelas de séries de conta e perfil em seu id de série e insira o resultado na tabela de relações
7. devolver novas contas

Com a tabela assim :

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

E a consulta de inserção assim:

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

Basicamente o que ele faz:

• insere uma linha emtest_1
• insere uma linha emtest_2
• insere seu par de IDs emtest_refs

O problema é que eu gostaria de reescrever a consulta em uma consulta multi-inserção, ou seja, para as nlinhas inseridas em test_1inserir as nlinhas test_2e, em seguida, criar as nlinhas test_refspara os valores inseridos. Para isso eu preciso saber o índice de linha dentro do CTE, para que possa ser usado como uma chave de junção. É algo que você pode fazer dentro da RETURNINGcláusula?