Início / dba / Perguntas / 317103
Accepted
Biller Builder
Asked: 2022-09-20 09:02:15 +0800 CST

Como executar multi-inserções fictícias com generate_series ()?

  • 772

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
postgresql cte

1 respostas

  1. Best Answer

    GERADO SEMPRE

    Você vai ter que fazer insert- returningou usarlastval()

    Depois de alguma discussão chegamos a isso:

    WITH input_sequence AS (
  SELECT
    generate_series AS index_id
  FROM
    generate_series(1, 5)
),
new_accounts AS (
  INSERT INTO accounts
  SELECT
  FROM
    input_sequence
  RETURNING    *
),
input_accounts AS (
  SELECT
    row_number() over () as index,
    new_accounts.id
  FROM
    new_accounts
),
new_profiles AS (
  INSERT INTO profiles 
    SELECT 
    FROM 
      input_sequence
    RETURNING
    *
),
input_profiles AS (
  SELECT
    row_number() over () as index,
    new_profiles.id
  FROM
    new_profiles
),
new_account_profile_pairs AS ( 
  INSERT INTO account_profiles ( account_id, profile_id )
  SELECT
    input_accounts.id AS account_id,
    input_profiles.id AS profile_id
  FROM
    input_accounts,
    input_profiles
  WHERE 
    input_accounts.index = input_profiles.index
)
SELECT
  index, 
  id
FROM
  input_accounts
ORDER BY 
  id;

    Aqui estou usando a função window row_bnumber() over ()para atribuir números de linha a cada nova linha inserida, fornecendo um número no qual elas podem ser unidas no conjunto de resultados final.

    • 1

relate perguntas