Différence entre le langage sql et le langage plpgsql dans les fonctions PostgreSQL

je Suis très nouveau dans développement de Base de données j'ai donc quelques doutes au sujet de mon exemple suivant:

Fonction f1() - langage sql

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

fonction f2 () - langue plpgsql

 create  or replace function f2(istr  varchar)
 returns text as $$ 
 begin select 'hello! '::varchar || istr; end;
 $$ language plpgsql;
  • les deux fonctions peuvent être appelé comme select f1('world') ou select f2('world') .

  • si j'appelle select f1('world') le sortie sera:

    `hello! world`
    
  • et sortie pour select f2('world') :

    Erreur

    : la requête n'a pas de destination pour les données de résultat Conseil: si vous voulez rejeter les résultats D'un SELECT, utilisez PERFORM à la place. CADRE: PL/pgSQL fonction f11(character varying), ligne 2 à l'instruction SQL ****** Erreur ******

  • je souhaite connaître la différence et les situations dans lesquelles je devrais utiliser language sql ou language plpgsql .

tout lien ou réponse utile concernant les fonctions sera très apprécié.

22
demandé sur Erwin Brandstetter 2014-07-15 14:10:41

2 réponses

fonctions SQL

sont le meilleur choix:

  • Pour de simples requêtes scalaires . Pas grand-chose à planifier, mieux vaut éviter les frais généraux.

  • Pour seule appels par session . Rien à gagner de la mise en cache de plan et des déclarations préparées que PL/pgSQL a à offrir. Voir ci-dessous.

  • S'ils sont typiquement appelés dans le contexte de requêtes plus importantes et sont suffisamment simples pour être inlined .

  • Pour manque de expérience avec n'importe quel langage procédural comme PL/pgSQL. Beaucoup connaissent bien SQL et c'est à peu près tout ce dont vous avez besoin pour les fonctions SQL. Peu de gens peuvent en dire autant de PL/pgSQL.

  • un peu plus court. Pas de bloc de frais généraux.

PL / pgSQL fonctions

sont le meilleur choix:

  • quand vous avez besoin de n'importe quels éléments de procédure ou variables qui ne sont pas disponibles dans les fonctions SQL, évidemment.

  • pour toute sorte de SQL dynamique , où vous construisez et EXECUTE déclarations dynamiquement. Des précautions particulières sont nécessaires pour éviter l ' injection de SQL. Plus de détails:

  • quand vous avez calculs qui peut être réutilisé dans plusieurs endroits et un CTE ne peut pas être étiré pour le but. Dans une fonction SQL, vous n'avez pas de variables et seriez forcé de calculer à plusieurs reprises ou d'écrire à une table. Cette réponse sur la dba.SE a côté-à-côte exemples de code pour résoudre le même problème à l'aide d'une fonction SQL / une fonction plpgsql / une requête avec des expressions de table communes:

    les attributions sont un peu plus cher que dans d'autres langages procéduraux. Adapter un style de programmation qui n'utilise pas plus de missions que nécessaire.

  • Lorsqu'une fonction ne peut pas être insérée et est appelée à plusieurs reprises. Contrairement aux fonctions SQL, les plans de requête peuvent être mis en cache pour toutes les instructions SQL à l'intérieur D'une fonction PL/pgSQL ; ils sont traités comme les instructions préparées , le plan est mis en cache pour les appels répétés au cours de la même session (si Postgres s'attend à ce que le plan cache (generic) fonctionne mieux qu'une nouvelle planification à chaque fois. C'est la raison pour laquelle les fonctions PL/pgSQL sont typiquement plus rapide après le premier couple d'appels dans de tels cas.

    voici un fil sur pgsql-performance discutant de certains de ces éléments:

    Re: fonctions pl/pgsql plus performantes que les fonctions sql?

  • Lorsque vous avez besoin de piège "erreurs de 151950920" .

  • Pour les procédures de déclenchement (qui sont juste en fonctions, trop).

  • lorsque vous incluez des déclarations DDL modifiant des objets ou modifiant des catalogues système de quelque manière que ce soit en rapport avec des commandes subséquentes - parce que toutes les instructions dans les fonctions SQL sont analysées en même temps tandis que les fonctions PL/pgSQL planifient et exécutent chaque instruction de manière séquentielle (comme une instruction préparée). Voir:

aussi considérer:


pour être complet: en fait retour à partir D'une fonction PL/pgSQL, vous pouvez écrire:

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

il y a d'autres moyens:

32
répondu Erwin Brandstetter 2018-06-26 02:11:42

PL/PgSQL est un langage procédural propre à PostgreSQL basé sur le code SQL . Il a des boucles, des variables, la gestion des erreurs/exceptions, etc. Tous les SQL ne sont pas valides PL / PgSQL - comme vous l'avez découvert, par exemple, vous ne pouvez pas utiliser SELECT sans INTO ou RETURN QUERY . PL / PgSQL peut également être utilisé dans les blocs DO pour les procédures à une seule injection.

sql fonctions ne peuvent utiliser que du SQL pur, mais ils sont souvent plus efficace et ils sont plus simples à écrire parce que vous n'avez pas besoin d'un bloc BEGIN ... END; , etc. Les fonctions SQL peuvent être inlined, ce qui n'est pas vrai pour PL/PgSQL.

les gens utilisent souvent PL/PgSQL où SQL simple serait suffisant, parce qu'ils sont habitués à penser procéduralement. Dans la plupart des cas, lorsque vous pensez que vous avez besoin de PL/PgSQL, vous ne le faites probablement pas. Les CTE récursifs, les requêtes latérales, etc. répondent généralement à la plupart des besoins.

pour plus d'information ... voir le manuel.

12
répondu Craig Ringer 2014-07-15 10:31:13