Linguagens procedurais do PostgreSQL - diferenças entre PL/pgSQL e SQL

As funções PL/PgSQL e SQL simples fazem parte de um conjunto de ferramentas maior e devem ser vistas nesse contexto. Costumo pensar nisso em termos de uma escala crescente de poder combinada com complexidade e custo crescentes, onde você deve usar a ferramenta mais simples que fará bem o trabalho:

• Use visualizações sempre que possível
• Onde uma visualização não for adequada, use uma função SQL
• Quando uma função SQL não for adequada, use PL/PgSQL.
• Onde PL/PgSQL é muito limitado ou não é expressivo o suficiente, use PL/Perl, PL/Python, PL/V8, PL/Java ou qualquer que seja sua preferência
• ... e onde nenhum PL vai fazer o trabalho, use um programa externo e possivelmente LISTENe NOTIFYfale com ele.

Muitas vezes, uma visão é suficiente quando você acha que uma função é necessária. Mesmo que seja extremamente caro para SELECTtoda a exibição, WHEREas cláusulas na consulta que fazem referência à exibição geralmente são inseridas na exibição e podem resultar em planos de consulta muito diferentes. Muitas vezes tive grandes melhorias de desempenho ao converter funções SQL em exibições.

A principal ocasião em que você descobre que não pode usar uma exibição e deve considerar uma função SQL é quando:

• Parâmetros que não podem ser expressos como WHEREcláusulas simples são necessários, como um parâmetro dentro de uma WITHexpressão
• Você quer uma barreira de segurança por meio de uma SECURITY DEFINERfunção e as security_barriervisualizações no PostgreSQL 9.2 e superiores não são suficientes para suas necessidades;
• Você precisa de parâmetros que não sejam colocados em subcláusulas de uma exibição pelo otimizador e deseja controlá-lo mais diretamente; ou
• Existem muitos parâmetros ou muita repetição dos parâmetros, por isso é impraticável escrever a consulta como uma exibição.

Para a maioria dessas tarefas, uma função SQL simples funciona bem e geralmente é mais fácil de ler do que PL/PgSQL. As funções SQL declaradas STABLEou IMMUTABLE(e também não declaradas STRICTou SECURITY DEFINER) também podem ser embutidas na instrução de chamada. Isso elimina a sobrecarga da chamada de função e às vezes também pode resultar em enormes benefícios de desempenho quando uma condição WHERE na função de chamada é enviada para a função SQL pelo otimizador. Use funções SQL sempre que forem suficientes para a tarefa.

O principal momento em que as funções SQL não farão o trabalho é quando você precisa de muita lógica. Operações if/then/else que você não pode expressar como CASEinstruções, muita reutilização de resultados calculados, criação de valores a partir de partes, tratamento de erros, etc. PL/PgSQL é útil então. Escolha PL/PgSQL quando você não puder usar funções SQL ou elas não forem adequadas, como para:

• SQL dinâmico e DDL dinâmico por meio da EXECUTEinstrução
• Quando você deseja RAISEerros/avisos para os logs ou cliente
• Quando você precisa de tratamento de exceção - você pode interceptar e tratar erros com EXCEPTIONblocos em vez de ter toda a transação encerrada em caso de erro
• Lógica condicional complexa que não se encaixa CASE ... WHENmuito bem
• Muita reutilização de valores calculados nos quais você não consegue se encaixar WITHe CTEs
• Criando registros dinâmicos
• Você precisa executar uma ação depois de produzir o conjunto de resultados

Com expressões de tabela comuns (CTEs), especialmente CTEs graváveis ​​e WITH RECURSIVEacho que uso PL/PgSQL muito menos do que costumava porque o SQL é muito mais expressivo e poderoso. Eu uso visualizações e funções SQL simples muito mais agora. Vale lembrar que as funções SQL simples podem conter mais de uma instrução; a última instrução é o resultado da função.

plpgsqlé uma linguagem processual completa, com variáveis, construções de loop, etc. Uma SQLfunção é simplesmente uma subconsulta. Uma função SQL, se for declarada STABLEou IMMUTABLEnão declarada STRICT, pode frequentemente ser embutida na consulta de chamada, como se fosse escrita em cada referência.