Início / dba / Perguntas / 315774
Accepted
Biller Builder
Asked: 2022-08-19 04:35:45 +0800 CST

Como você escreve multi-inserções ordenadas com CTEs?

  • 772

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?

postgresql insert

1 respostas

  1. Best Answer

    ... para as nlinhas inseridas em test_1insira as n linhas test_2 e, em seguida, crie 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 RETURNING cláusula?

    Não há provisão para adicionar números de linha na RETURNINGcláusula - nenhum equivalente para WITH ORDINALITYisso pode ser usado com funções de retorno de conjunto. Ver:

    E se você adicionar uma função de janela (como você pode ter tentado), você obtém:

    ERROR:  window functions are not allowed in RETURNING

    A sintaxe específica do Postgres ROWS FROMpode retornar linhas na etapa de bloqueio, mas isso também é apenas para funções de retorno de conjunto. Ver:

    Você deve pegar o caminho mais longo para casa e gerar números de linha em outra CTE ou subconsulta. Exemplo com duas linhas:

    WITH new_test_1_rows AS (
   INSERT INTO test_1 VALUES (DEFAULT), (DEFAULT)
   RETURNING *
   )
, new_test_2_rows AS (
   INSERT INTO test_2 VALUES (DEFAULT), (DEFAULT)
   RETURNING *
   )
INSERT INTO test_refs (id_1, id_2)
SELECT t1.id, t2.id
FROM  (
   SELECT row_number() OVER (ORDER BY id) AS rn, id  -- !
   FROM new_test_1_rows
   ) t1
JOIN  (
   SELECT row_number() OVER (ORDER BY id) AS rn, id  -- !
   FROM new_test_2_rows
   ) t2 USING (rn)  -- !
RETURNING *

    db<>fique aqui

    • 0

relate perguntas