Início / dba / Perguntas / 91731
Accepted
John
Asked: 2015-02-12 12:02:24 +0800 CST

Redefina a sequência de um PK serial com SQL dinâmico

  • 772

Como posso referenciar dinamicamente um nome de sequência (como abaixo) usando uma combinação de strings e DECLAREvariáveis? O código abaixo está correto ou existe outro método para fazer isso?

Meu objetivo é fazer isso dentro de um bloco DO/ BEGINpor motivos de desempenho, além de me ajudar a entender o plpgsql fazendo algo útil, então não vou misturar isso ao PHP, a menos que seja realmente necessário.

DO $$
 DECLARE PKEY VARCHAR;
BEGIN

SELECT pg_attribute.attname INTO PKEY 
FROM pg_index, pg_class, pg_attribute 
WHERE pg_class.oid = 'parts1'::regclass 
AND indrelid = pg_class.oid 
AND pg_attribute.attrelid = pg_class.oid 
AND pg_attribute.attnum = any(pg_index.indkey) 
AND indisprimary;

--SELECT setval('parts1_id_seq', (SELECT MAX(pkey) + 1 FROM parts));
SELECT setval('parts1_' || PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));

  END;
$$ LANGUAGE plpgsql;
postgresql plpgsql

2 respostas

  1. Best Answer

    Conselho Geral

    Você mesmo mencionou, acabou de começar a usar o Postgres. No entanto, você está lidando com tarefas extremamente avançadas imediatamente, fazendo malabarismos com catálogos do sistema e operando com SQL dinâmico avançado para automatizar as coisas.

    Embora seus objetivos pareçam razoáveis, você ainda precisa começar do básico . Há muito o que explicar aqui. Comece com (partes relevantes de) o excelente manual . Forneci alguns links profundos mais abaixo.

    Responda

    Para obter o(s) nome(s) (e tipo(s) de dados) das colunas envolvidas na chave primária, use esta consulta mais simples:

    SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'tbl'::regclass
AND    i.indisprimary;

    Atualizei a página Postgres Wiki de onde sua consulta original parece ter se originado.

    No entanto , isso pode retornar várias linhas, enquanto você atribui apenas um único valor. Supondo que você tenha estabelecido que estamos lidando com uma serialchave primária do tipo (coluna única). Caso contrário, use técnicas semelhantes às apresentadas na minha resposta anterior para ter certeza.

    Em seguida, use a função de informações do sistema dedicada pg_get_serial_sequence()para determinar o nome da sequência usada, como demonstrado na minha resposta anterior .
    Por documentação:

    obtenha o nome da sequência que um serial, smallserialou bigserialcoluna usa

    DO
$do$
BEGIN
   EXECUTE (
   SELECT format($$SELECT setval('%s'::regclass, max(%I)) FROM %s$$
                 , pg_get_serial_sequence(a.attrelid::regclass::text, a.attname)
                 , a.attname
                 , a.attrelid::regclass
                )
   FROM   pg_index i
   JOIN   pg_attribute a ON a.attrelid = i.indrelid
                        AND a.attnum = i.indkey[0]
   WHERE  i.indrelid = 'tbl'::regclass
   AND    i.indisprimary
   );
END
$do$ LANGUAGE plpgsql;

    Isso cria e executa uma consulta do formulário:

    SELECT setval('tbl_tbl_id_seq'::regclass, max(tbl_id)) FROM tbl;

    Explicar / Aconselhar

    • Isso é algo avançado e não é realmente adequado para iniciantes. Mexer nos catálogos do sistema pode dar errado rapidamente se você não souber exatamente o que está fazendo.

    • Atenha-se aos identificadores legais em minúsculas no Postgres e no plpgsql para facilitar sua vida. Mas nunca confie nele em SQL dinâmico, onde você também precisa se defender contra a injeção de SQL o tempo todo.

    • Fazendo uso pesado format()para construir a string de consulta de forma conveniente e segura.

    • Dentro de uma função plpgsql você não pode chamar SELECTsem atribuir o resultado. Você usaria PERFORMem vez disso. Detalhes no manual.
      Eu removi isso completamente, já que reduzi tudo a um único arquivo EXECUTE.

    • Como toda a operação só faz sentido para uma chave primária de coluna única, simplifiquei a JOINcondição para a.attnum = i.indkey[0]
      Note que pg_index.indkeypossui o tipo especial (interno) int2vector. Ao contrário das matrizes do Postgres, seu índice começa com 0 , não 1.

    • Crie o hábito de usar aspas com um token em torno do código plpgsql (incluindo DOinstruções). Isso permite aninhar cotações de dólares simples como eu faço no meu exemplo. Detalhes:

    • Você só precisa de um único SELECTaqui:

      SELECT setval('tbl_tbl_id_seq'::regclass, max(tbl_id)) FROM tbl;

      ao invés de:

      SELECT setval('tbl_tbl_id_seq'::regclass, (SELECT max(tbl_id) FROM tbl));

    SQL Fiddle demonstrando algumas coisas.

    • 4

  2. Sempre que você tiver uma instrução SQL de natureza dinâmica (possivelmente alterada em tempo de execução) que precisa ser executada dentro de uma função, você precisa executá EXECUTE-la.

    Consulte a Seção 40.5.4. Executando Comandos Dinâmicos

    Você precisa estar ciente de que a execução do SQL dessa maneira PODE ser vulnerável a ataques de SQL Injection .

    Sua linha acima:

    SELECT setval('parts1_' || INTO PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));

    Se tornaria (pelo menos, acredito que essa seja a intenção)

    EXECUTE 'SELECT setval(''parts1_' || PKEY || '_seq'', (SELECT MAX(' || pkey || ') + 1 FROM parts))';
    • Nota .. a aspa simples dupla ( '') dentro de uma string é tratada como uma aspa simples literal (como \')
    • Nota 2 .. para proteger contra SQL Injection, você pode EXECUTE uma string formatada (como Erwin Brandstetter já forneceu vários exemplos para você em um post diferente)
    • 1

relate perguntas