Diferença entre o idioma sql e o idioma plpgsql nas funções do PostgreSQL

Sou muito novo no desenvolvimento de database, por isso tenho algumas dúvidas sobre o meu exemplo a seguir:

Função f1 () – linguagem sql

create or replace function f1(istr varchar) returns text as $$ select 'hello! '::varchar || istr; $$ language sql; 

Função f2 () – linguagem plpgsql

  create or replace function f2(istr varchar) returns text as $$ begin select 'hello! '::varchar || istr; end; $$ language plpgsql; 
  • Ambas as funções podem ser chamadas como select f1('world') ou select f2('world') .

  • Se eu chamar select f1('world') a saída será:

     `hello! world` 
  • E saída para select f2('world') :

    ERRO: a consulta não tem destino para os dados do resultado. DICA: Se você quiser descartar os resultados de um SELECT, use PERFORM. CONTEXTO: Função PL / pgSQL f11 (variante de caractere) linha 2 na instrução SQL ****** Erro ******

  • Gostaria de saber a diferença e em quais situações devo usar language sql ou language plpgsql .

Qualquer link útil ou respostas sobre funções será muito apreciado.

Funções SQL

são a melhor escolha:

  • Para consultas escalares simples . Não há muito a planejar, melhor economizar qualquer sobrecarga.

  • Para chamadas únicas por session . Nada a ganhar com o planejamento de cache e declarações preparadas que o PL / pgSQL tem a oferecer. Ver abaixo.

  • Se eles são normalmente chamados no contexto de consultas maiores e são simples o suficiente para serem embutidos .

  • Por falta de experiência com qualquer linguagem procedural como PL / pgSQL. Muitos conhecem bem o SQL e isso é tudo o que você precisa para funções SQL. Poucos podem dizer o mesmo sobre PL / pgSQL.

  • Um código um pouco mais curto. Nenhuma sobrecarga de bloco.

Funções PL / pgSQL

são a melhor escolha:

  • Quando você precisar de qualquer elemento processual ou variável que não esteja disponível em funções SQL, obviamente.

  • Para qualquer tipo de SQL dynamic , onde você constrói e EXECUTE instruções dinamicamente. Um cuidado especial é necessário para evitar a injeção de SQL. Mais detalhes:

    • Funções do Postgres vs consultas preparadas
  • Quando você tem cálculos que podem ser reutilizados em vários lugares e um CTE não pode ser esticado para o efeito. Em uma function SQL, você não tem variables ​​e seria forçado a computar repetidamente ou gravar em uma tabela. Esta resposta relacionada no dba.SE tem exemplos de código lado-a-lado para resolver o mesmo problema usando uma function SQL / uma function plpgsql / uma consulta com CTEs:

    • Como passar um parâmetro para uma function

    Atribuições são um pouco mais caras do que em outras linguagens procedurais. Adapte um estilo de programação que não use mais atribuições do que o necessário.

  • Quando uma function não pode ser inline e é chamada repetidamente. Ao contrário das funções SQL, os planos de consulta podem ser armazenados em cache para todas as instruções SQL dentro de uma function PL / pgSQL ; eles são tratados como instruções preparadas , o plano é armazenado em cache para chamadas repetidas dentro da mesma session (se o Postgres espera que o plano armazenado em cache tenha um desempenho melhor que o replanejamento de cada vez. Essa é a razão pela qual as funções PL / pgSQL são mais rápidas o primeiro par de chamadas nesses casos.

    Aqui está um tópico sobre pgsql-performance discutindo alguns destes itens:
    Re: funções pl / pgsql superando os sql?

  • Quando você precisa interceptar erros .

  • Para procedimentos de gatilho (que são apenas funções também).

  • Ao include instruções DDL alterando objects ou alterando catálogos do sistema de alguma forma relevante para comandos subseqüentes – porque todas as instruções em funções SQL são analisadas de uma vez enquanto as funções PL / pgSQL planejam e executam cada instrução seqüencialmente (como uma instrução preparada). Vejo:

    • Por que as funções PL / pgSQL podem ter efeitos colaterais, enquanto as funções SQL não podem?

Considere também:

  • Desempenho do procedimento armazenado do PostgreSQL

Para completar: para retornar de uma function PL / pgSQL, você poderia escrever:

 CREATE FUNCTION f2(istr varchar) RETURNS text AS $func$ BEGIN RETURN 'hello! '; -- defaults to type text anyway END $func$ LANGUAGE plpgsql; 

Existem outras maneiras:

  • Posso fazer uma function plpgsql retornar um inteiro sem usar uma variável?
  • O manual sobre “retornando de uma function”

O PL / PgSQL é uma linguagem procedural específica do PostgreSQL baseada em SQL . Tem loops, variables, manipulação de erros / exceções, etc. Nem todo SQL é válido PL / PgSQL – como você descobriu, por exemplo, você não pode usar SELECT without INTO ou RETURN QUERY . O PL / PgSQL também pode ser usado em blocos DO para procedimentos de disparo único.

sql funções sql só podem usar SQL puro, mas geralmente são mais eficientes e são mais fáceis de escrever porque você não precisa de um BEGIN ... END; bloco, etc. Funções SQL podem ser embutidas, o que não é verdade para PL / PgSQL.

As pessoas costumam usar o PL / PgSQL onde o SQL simples seria suficiente, porque eles estão acostumados a pensar processualmente. Na maioria dos casos, quando você pensa que precisa de PL / PgSQL, você provavelmente não precisa. CTEs recursivos, consultas laterais, etc geralmente atendem a maioria das necessidades.

Para mais informações … veja o manual.