Início / dba / Perguntas / 203258
Accepted
dakini
Asked: 2018-04-07 08:53:11 +0800 CST

INSERT condicional com um CTE aninhado?

  • 772

Estou tentando descobrir se há uma maneira de fazer CTEs aninhados funcionarem para esse caso específico.

Considere o seguinte cenário (altamente artificial) baseado no aplicativo real: há uma tabela de coluna única de IDs de funcionários. Depois, há uma tabela de propriedades do funcionário com todos os detalhes. (A principal razão por trás da tabela de col única é geralmente a necessidade de novos IDs de funcionários serem criados em lotes e atribuídos antes que quaisquer detalhes da equipe real sejam conhecidos.)

Agora para a tarefa em mãos, estamos inserindo detalhes (ou seja, o nome) para um novo funcionário, mas primeiro precisamos verificar se um funcionário com esse nome já existe. Se isso acontecer, simplesmente retornaremos o id e, caso contrário, criaremos um novo registro de funcionário e inseriremos os detalhes, retornando finalmente o id recém-criado.

Para recriar este cenário de teste:

CREATE TABLE public.employee (
    id text DEFAULT gen_random_uuid(),
    PRIMARY KEY (id)
);

CREATE TABLE public.employee_details (
    employee_id text,
    name text,
    PRIMARY KEY (employee_id),
    FOREIGN KEY (employee_id) REFERENCES public.employee(id)
);

A consulta que estou tentando moldar se parece com a mostrada abaixo.

with 
e as 
    (select name, employee_id from employee_details where name = 'jack bauer'), 

i as (insert into employee_details (name, employee_id) 
    select 'jack bauer', 
        (with a as (insert into employee values(default) RETURNING id) select a.id from a)
    where not exists (select 1 from e) returning name, employee_id) 

select employee_id, name from e
union all 
select employee_id, name from i;

Se eu substituir o CTE aninhado por um id já criado (executando o CTE aninhado separadamente), ele funcionará (mas pode resultar na criação de um id supérfluo). Também é possível simplesmente mover o CTE aninhado para o nível superior (para que tudo se pareça com with e as (..), i as (..), a as (..) select .. where not exists..., mas isso também significaria que um ID de funcionário supérfluo é criado onde nenhum detalhe precisa ser inserido. Eu quero descobrir uma maneira para fazê-lo "inline" - então o novo id só será criado se a not existscláusula retornar true.

Eu continuo recebendo o erro:

A cláusula WITH que contém uma instrução de modificação de dados deve estar no nível superior.

Eu acho que o problema é que o CTE aninhado retorna uma "coluna", enquanto toda a consulta funcionará se obtiver um "valor" (o que acontece quando alguém simplesmente copia um valor de texto em vez do CTE). Eu me deparei com uma discussão um pouco relacionada a esta questão, mencionando um bug aparente que foi corrigido desde 9.3. Não sei se isso está relacionado aos meus problemas aqui. Para citar a discussão vinculada:

O código de análise parse parece pensar que WITH só pode ser anexado ao nível superior ou a um SELECT de nível folha dentro de uma árvore de operação definida; mas a gramática segue o padrão SQL que não diz tal coisa

Estou usando o Postgres 10.3.

postgresql insert

1 respostas

  1. Best Answer

    Para os fins desta pergunta, vou assumir employee_details.nameser definido UNIQUE. Caso contrário, toda a operação não faria sentido.

    Você não pode aninhar um CTE de modificação de dados como tentou (como já descobriu da maneira mais difícil) - e não precisa. Esta consulta atingiria seu objetivo:

    WITH e AS (
   SELECT name, employee_id
   FROM   employee_details
   WHERE  name = 'jack bauer'
   )
 , i1 AS (
   INSERT INTO employee             -- no target columns!
   SELECT                           -- empty SELECT list!
   WHERE NOT EXISTS (SELECT FROM e)
   RETURNING id
   )
 , i2 AS (
   INSERT INTO employee_details (name, employee_id) 
   SELECT 'jack bauer', id
   FROM   i1
   RETURNING name, employee_id
   )
SELECT employee_id, name FROM e
UNION ALL 
SELECT employee_id, name FROM i2;

    O recurso principal é o INSERTsem colunas de destino e um arquivo SELECT. O Postgres preenche todas as colunas não listadas SELECTcom valores padrão. Dessa forma, podemos substituir o incondicional VALUES (default)por um condicionalINSERT . O CTE i1só insere uma linha se o nome fornecido não for encontrado.

    O manual:

    Se nenhuma lista de nomes de coluna [target] for fornecida, o padrão será todas as colunas da tabela em sua ordem declarada; [...]

    Cada coluna não presente na lista de colunas explícita ou implícita será preenchida com um valor padrão, seja seu valor padrão declarado ou nulo se não houver nenhum.

    Esta é uma extensão específica do Postgres do padrão:

    Além disso, o caso em que uma lista de nomes de coluna é omitida, mas nem todas as colunas são preenchidas com a VALUEScláusula ou query, não é permitido pelo padrão.

    O CTE final i2só insere uma linha se i1retornar uma linha. Voilá.

    Isso está sujeito a condições de corrida sob carga de gravação simultânea nas mesmas tabelas. Se você precisa descartar isso, você precisa fazer mais. Relacionado:

    Sem as complicações do INSERT condicional na 2ª tabela, isso se resumiria a um caso comum de SELECT ou INSERT :

    Aparte

    "id" text DEFAULT gen_random_uuid()

    Eu recomendo fortemente usar o tipo de dados uuidpara armazenar UUIDs.

    • 9

relate perguntas