Início / dba / Perguntas / 61365
Accepted
Joishi Bodio
Asked: 2014-03-21 07:22:56 +0800 CST

INSERT usando resultados de CTE INSERT para fornecer valores de id exclusivos

  • 772

Estou escrevendo um trabalho para transformar dados de um design antigo em um novo design. Nesse processo, preciso pegar o id de uma inserção em uma tabela separada e usá-lo em uma inserção na tabela de destino, da seguinte forma:

CREATE TABLE t1 {
  t1_id BIGSERIAL,
  col1 VARCHAR
};
CREATE TABLE t2 {
  t2_id BIGSERIAL,
  col2 VARCHAR, -- renamed from col1 to avoid confusion
  t1_id BIGINT REFERENCES t1.t1_id
};

Eu tenho o SQL definido que corresponde ao seguinte formulário:

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, (SELECT * FROM ins)
FROM t3 a;

Eu queria que isso fosse executado SELECT * FROM inspara cada linha do SELECT.. mas, em vez disso, ele o executa apenas uma vez e usa esse valor para todas as linhas do SELECT. Como posso reestruturar meu SQL para obter o comportamento desejado?

edit4

t1 acaba se parecendo com:

1,<NULL>
(1 row)

t2 acaba se parecendo com:

10,'a',1
11,'b',1 -- problem with id from t1 being 1
12,'c',1 -- problem with id from t1 being 1
.
.

O que eu quero que t1 pareça:

1,<NULL>
2,<NULL>
3,<NULL>
.
.

O que eu quero que t2 se pareça:

10,'a',1
11,'b',2 -- id from t1 of 2
12,'c',3 -- id from t1 of 3
.
.

editar Para abordar o que a_horse_with_no_name disse, eu também tentei isso (com o mesmo resultado):

WITH ins AS (
  INSERT INTO t1 (t1_id) VALUES (DEFAULT) RETURNING t1_id
) INSERT INTO t2
  (col1, t1_id)
SELECT
  a.val1, b.t1_id
FROM t3 a
JOIN ins b ON TRUE;

edit2 Acabei de tentar referenciar diretamente o apropriado SEQUENCEem minha consulta, e isso FUNCIONA - mas não gosto muito dessa solução (principalmente porque não gosto de nomes de objeto codificados). do que referenciar diretamente o nome do SEQUENCE, eu agradeceria. :)

edit3 Suponho que outra solução seria usar um PROCEDUREpara fazer o INSERTem vez de um CTE .. mas ainda apreciaria as opções/sugestões.

postgresql postgresql-9.2

1 respostas

  1. Best Answer

    Não entendo por que você precisa de 2 tabelas se elas têm apenas um relacionamento 1-1. Mas aqui está ( pké a chave primária de t3):

    WITH ins AS (
  INSERT INTO t1 (col1) 
    SELECT NULL FROM t3 
  RETURNING t1_id
) 
, r AS
( SELECT t1_id, ROW_NUMBER() OVER () AS rn
  FROM ins
) 
, t AS
( SELECT *, ROW_NUMBER() OVER () AS rn
  FROM t3
) 
INSERT INTO t2
  (col1, t1_id)
SELECT
  t.val1, r.t1_id
FROM t 
  JOIN r USING (rn) ;

    Se o seu t3 for o resultado de um SELECT em vez de uma tabela preexistente, você poderá implementá-lo como tal para não precisar repetir a consulta t3 duas vezes:

    WITH t3 AS (
  SELECT ...
), ins AS (
  INSERT INTO t1 (col1)
    SELECT NULL FROM t3
  RETURNING t1_id
), r AS (
  SELECT t1_id, ROW_NUMBER() OVER () AS rn
  FROM ins
), t AS (
  SELECT *, ROW_NUMBER() OVER () AS rn
  FROM t3
) INSERT INTO t2
  (col1, t1_id)
SELECT
  t.val1, r.t1_id
FROM t 
  JOIN r USING (rn);
    • 13

relate perguntas