PL/pgSQL : les bases

Préambule

• Vous apprendrez :
  • à choisir si vous voulez écrire du PL
  • à choisir votre langage PL
  • les principes généraux des langages PL autres que PL/pgSQL
  • les bases de PL/pgSQL

Ce module présente la programmation PL/pgSQL. Il commence par décrire les routines stockées et les différents langages disponibles. Puis il aborde les bases du langage PL/pgSQL, autrement dit :

• comment installer PL/pgSQL dans une base PostgreSQL ;
• comment créer un squelette de fonction ;
• comment déclarer des variables ;
• comment utiliser les instructions de base du langage ;
• comment créer et manipuler des structures ;
• comment passer une valeur de retour de la fonction à l’appelant.

Au menu

• Présentation du PL et des principes
• Présentations de PL/pgSQL et des autres langages PL
• Installation d’un langage PL
• Détails sur PL/pgSQL

Objectifs

• Comprendre les cas d’utilisation d’une routine PL/pgSQL
• Choisir son langage PL en connaissance de cause
• Comprendre la différence entre PL/pgSQL et les autres langages PL
• Écrire une routine simple en PL/pgSQL
  • et même plus complexe

Langages PL : introduction

Qu’est-ce qu’un langage PL ?

• PostgreSQL accepte des fonctions et procédures en SQL et C
  • utilisables depuis une requête SQL
• PL = Procedural Language

PostgreSQL permet d’écrire des fonctions dans divers langages. Nativement, sont supportées les fonctions en SQL et en C.

PL est l’acronyme de Procedural Languages. En dehors du C et du SQL, tous les langages acceptés par PostgreSQL sont des PL.

Une fois en place, une fonction peut être appelée depuis une requête SQL, ou une autre fonction SQL ou PL/pgSQL. Le langage utilisé est totalement transparent pour l’utilisateur.

Quels langages PL sont disponibles ?

• Installé par défaut :
  • PL/pgSQL
• Intégrés au projet :
  • PL/Perl
  • PL/Python
  • PL/Tcl
• Extensions tierces :
  • PL/java, PL/R, PL/v8 (JavaScript), PL/R, PL/sh…
  • extensible à volonté

Les quatre langages PL supportés nativement (en plus du C et du SQL bien sûr) sont décrits en détail dans la documentation officielle :

• PL/pgSQL est intégré par défaut dans toute nouvelle base (de par sa présence dans la base modèle template1) ;
• PL/Tcl ;
• PL/Perl ;
• PL/Python.

D’autres langages PL sont accessibles en tant qu’extensions tierces. Ils réclament généralement d’installer les bibliothèques du langage sur le serveur. Souvent il suffit d’installer des paquets fournis par le PGDG.

Une liste plus large est par ailleurs disponible sur le wiki PostgreSQL, à des stades divers de fiabilité. Il en ressort qu’une vingtaine de langages sont disponibles, dont dix installables en production, même s’il faut vérifier que le langage reste bien maintenu. Les plus connus sont PL/Java ou PL/R. S’ajoutent aussi PL/Rust, JavaScript (PLV8), PL/Haskell, ou n’importe quel langage shell (via PL/sh).

De plus, il est possible d’en ajouter d’autres, comme décrit dans la documentation.

Langages trusted vs untrusted

• Trusted = langage de confiance :
  • ne permet que l’accès à la base de données
  • pas aux fichiers, réseau, etc.
  • SQL, PL/pgSQL, PL/Perl, PL/Tcl
• Untrusted:
  • PL/Python, C, PL/TclU, PL/PerlU
  • potentiellement dangereux !

Les langages trusted (« de confiance ») ne peuvent accéder qu’aux données de la base de la session en cours. Ils ne peuvent pas accéder aux autres bases, aux systèmes de fichiers, au réseau, réaliser certaines opérations comme fork, etc. Ils ne peuvent donc pas compromettre le système, mais de ce fait, ils sont bridés dans leurs possibilités. PL/pgSQL et SQL sont des exemples typiques.

La plupart des autres langages sont untrusted, n’ont pas ces limites et sont donc potentiellement dangereux. Certains sont même très dangereux par ce qu’ils permettent de faire (PL/sh). Certains langages sont disponibles en version complète untrusted, et en version bridée trusted (comme pl/Perl et pl/PerlU, ou Rust).

Seuls les superutilisateurs peuvent créer une routine dans un langage untrusted. Par contre, ils peuvent ensuite donner les droits d’exécution à ces routines aux autres rôles de l’instance :

GRANT EXECUTE ON FUNCTION nom_fonction TO un_role ;

Rappelons qu’un superutilisateur a autant de droits sur le serveur que l’utilisateur système postgres. Donner à un utilisateur les droits sur une telle fonction ne se fait pas à la légère. Il faut être sûr que l’utilisateur ne peut détourner la fonction pour des buts malveillants.

Les langages PL de PostgreSQL

Les langages PL fournissent :

• des fonctionnalités procédurales dans un univers relationnel
• les fonctionnalités avancées du langage PL choisi
• les librairies associées
• des performances supérieures au code côté client
  • latence
  • indexation

La question se pose souvent de placer la logique applicative du côté de la base, dans un langage PL, ou des clients. La tendance, ces dernières années, est de coder intégralement la logique du côté client. Mais il peut y avoir de nombreuses raisons en faveur de la première option.

Centralisation du code :

Simplifier et centraliser des traitements clients directement dans la base est l’argument le plus fréquent. Par exemple, une insertion complexe dans plusieurs tables, avec la génération et la récupération d’identifiants pour lier entre elles des tables, peut évidemment être écrite côté client. Il est quelquefois plus pratique de l’écrire sous forme d’une routine PL. Si plusieurs applications ont potentiellement besoin d’opérer un même traitement, à fortiori dans des langages différents, porter cette logique dans la base réduit d’autant les risques de bugs et facilite la maintenance.

Une règle peut être que tout ce qui a trait à l’intégrité des données devrait être exécuté au niveau de la base.

Performances :

Le code s’exécute localement, directement dans le moteur de la base. Il n’y a donc pas tous les changements de contexte et échanges de messages réseaux dus à l’exécution de nombreux ordres SQL consécutifs.

L’impact de la latence due au trafic réseau entre la base au client est souvent sous-estimé !

Les langages PL permettent aussi d’accéder à leurs bibliothèques spécifiques (extrêmement nombreuses en python ou perl, entre autres).

Une fonction en PL peut également servir à l’indexation des données. Cela est impossible si elle se calcule sur une autre machine.

Simplicité :

Suivant le besoin, un langage PL peut être bien plus pratique que le langage client.

Il est par exemple très simple d’écrire un traitement d’insertion/mise à jour en PL/pgSQL, le langage étant créé pour simplifier ce genre de traitements, et la gestion des exceptions pouvant s’y produire.

PL/Perl et PL/Python sont bien plus évolués que PL/PgSQL. Par exemple, ils sont bien plus efficaces en matière de traitement de chaînes de caractères, possèdent des structures avancées comme des tables de hachage, permettent l’utilisation de variables statiques pour maintenir des caches, voire, pour leur version untrusted, peuvent effectuer des appels systèmes. Dans ce cas, il devient possible d’appeler un service web par exemple, ou d’écrire des données dans un fichier externe.

Il existe des langages PL spécialisés. Le plus emblématique d’entre eux est PL/R. R est un langage utilisé par les statisticiens pour manipuler de gros jeux de données. PL/R permet donc d’effectuer ces traitements R directement en base, traitements qui seraient très pénibles à écrire dans d’autres langages, et avec une latence dans le transfert des données. Un autre intérêt est de réutiliser des fonctions existantes, que ce soit des librairies du langage ou des développements exécutés auparavant sur un poste client. PL/Python possède aussi de nombreuses librairies mathématiques et statistiques.

Un autre langage (pas PL à proprement parler) est, du moins sur le papier, plus rapide que tous les langages cités précédemment : le C. Des procédures stockées en C peuvent être compilées à l’extérieur de PostgreSQL, en respectant un certain formalisme, puis chargées en indiquant la bibliothèque C qui les contient et leurs paramètres et types de retour.

Mais attention : toute erreur dans le code C est susceptible d’accéder à toute la mémoire visible par le processus PostgreSQL qui l’exécute, et donc de corrompre les données. Il est donc conseillé de n’utiliser le C qu’en dernier recours.

La grande variété des différents langages PL supportés par PostgreSQL permet normalement d’en trouver un correspondant aux besoins et aux langages déjà maîtrisés dans l’entreprise.

Intérêts de PL/pgSQL en particulier

• Dédié au traitement des données et au SQL
• Maniement direct des données
• Ajout de structures de contrôle au langage SQL
• Traitements complexes, transactions
• Hérite de tous les types, fonctions et opérateurs définis par les utilisateurs
• Trusted
• Facile à utiliser
• Mais assez pauvre

Le langage étant assez ancien, proche du Pascal et de l’ADA, sa syntaxe ne choquera personne. Elle est d’ailleurs très proche de celle du PLSQL d’Oracle.

Les gros atouts de PL/pgSQL est son intégration au SQL, et son intégration à la base de données. PL/pgSQL permet d’écrire des requêtes SQL directement dans le code PL sans déclaration préalable, sans appel à des méthodes complexes, ni rien de cette sorte. Le code SQL est mélangé naturellement au code PL, et on a donc un sur-ensemble procédural de SQL. Les curseurs sont totalement optionnels.

À l’inverse, le gros défaut commun de tous les autres langages est qu’ils ont besoin de fonctions supplémentaires pour accéder à la base de données. L’accès aux données est donc assez fastidieux au niveau syntaxique comparé à PL/pgSQL.

PL/pgSQL étant intégré à PostgreSQL, il hérite de tous les types déclarés dans le moteur, même ceux rajoutés par l’utilisateur. Il peut les manipuler de façon transparente. Là encore, les autres langages peuvent poser problème à cause de différences de types.

Un autre atout du PL/pgSQL (et du C et du SQL) est l’absence d’interpréteur externe à lancer, au contraire de python, Java, etc.

PL/pgSQL possède les structures de contrôle habituelles comme IF… THEN… END IF, ou les boucles LOOP…END LOOP, y compris les boucles directement sur un ensemble de résultat.

PL/pgSQL dispose d’une gestion des erreurs évoluée (gestion d’exceptions).

Les procédures PL/pgSQL (pas les fonctions) permettent de gérer des transactions.

PL/pgSQL est trusted. Par défaut, tous les utilisateurs peuvent donc créer des routines dans ce langage. Vous pouvez toujours soit supprimer le langage dans la base, soit retirer les droits à un utilisateur sur ce langage (via la commande SQL REVOKE).

PL/pgSQL est donc raisonnablement facile à utiliser : il y a peu de complications et peu de pièges. Par contre, le langage peut sembler pauvre pour des gens habitués aux langages plus récents. Il n’y a pas non plus beaucoup de librairies de fonctions à utiliser. Les autres langages et les diverses extensions comblent cependant ces lacunes.

Routines / Procédures stockées / Fonctions

• Procédure stockée
  • pas de retour
  • contrôle transactionnel : COMMIT / ROLLBACK
• Fonction
  • peut renvoyer des données (même des lignes)
  • utilisable dans un SELECT
  • peut être de type TRIGGER, agrégat, fenêtrage
• Routine
  • procédure ou fonction

Les programmes écrits à l’aide des langages PL sont habituellement enregistrés sous forme de « routines » :

• procédures ;
• fonctions ;
• fonctions trigger ;
• fonctions d’agrégat ;
• fonctions de fenêtrage (window functions).

Le code source de ces objets est stocké dans la table pg_proc du catalogue.

Les procédures, apparues avec PostgreSQL 11, sont très similaires aux fonctions. Les principales différences entre les deux sont :

• Les fonctions doivent déclarer des arguments en sortie (RETURNS ou arguments OUT). Elles peuvent renvoyer n’importe quel type de donnée, ou des ensembles de lignes. Il est possible d’utiliser void pour une fonction sans argument de sortie ; c’était d’ailleurs la méthode utilisée pour émuler le comportement d’une procédure avant leur introduction avec PostgreSQL 11. Les procédures n’ont pas de code retour (on peut cependant utiliser des paramètres OUT ou INOUT).
• Les procédures offrent le support du contrôle transactionnel, c’est-à-dire la capacité de valider (COMMIT) ou annuler (ROLLBACK) les modifications effectuées jusqu’à ce point par la procédure. L’intégralité d’une fonction s’effectue dans la transaction appelante.
• Les procédures sont appelées exclusivement par la commande SQL CALL ; les fonctions peuvent être appelées dans la plupart des ordres DML/DQL (notamment SELECT), mais pas par CALL.
• Les fonctions peuvent être déclarées de telle manière qu’elles peuvent être utilisées dans des rôles spécifiques (trigger, agrégat ou fonction de fenêtrage).

Installation d’un langage PL

Installation des binaires nécessaires

• SQL, C et PL/pgSQL
  • compilés et installés par défaut
• Paquets du PGDG pour la plupart des langages :

    yum|dnf install postgresql16-plperl
    apt     install postgresql-plpython3-16

• Autres langages :
  • à compiler soi-même

Pour savoir si PL/Perl ou PL/Python a été compilé, on peut demander à pg_config :

pg_config --configure
'--prefix=/usr/local/pgsql-10_icu' '--enable-thread-safety'
'--with-openssl' '--with-libxml' '--enable-nls' '--with-perl' '--enable-debug'
'ICU_CFLAGS=-I/usr/local/include/unicode/'
'ICU_LIBS=-L/usr/local/lib -licui18n -licuuc -licudata' '--with-icu'

Si besoin, les emplacements exacts d’installation des bibliothèques peuvent être récupérés à l’aide des options --libdir et --pkglibdir de pg_config.

Cependant, dans les paquets fournis par le PGDG, il faudra installer explicitement le paquet dédié à plperl pour la version majeure de PostgreSQL concernée. Pour PostgreSQL 16, les paquets sont postgresql16-plperl (depuis yum.postgresql.org) ou postgresql-plperl-16 (depuis apt.postgresql.org). De même pour Python 3 (paquets postgresql14-plpython3 ou postgresql-plython3-14).

Les bibliothèques plperl.so, plpython3.so ou plpgsql.so contiennent les fonctions qui permettent l’utilisation de chaque langage. La bibliothèque nécessaire est chargée par le moteur à la première utilisation d’une procédure utilisant ce langage.

La plupart des langages intéressants sont disponibles sous forme de paquets. Des versions très récentes, ou des langages plus exotiques, peuvent nécessiter une compilation de l’extension.

Activer un langage

Activer un langage passe par la création de l’extension :

CREATE EXTENSION plperl ;     -- pour tous
-- versions untrusted
CREATE EXTENSION plperlu ;    -- pour le superutilisateur
CREATE EXTENSION plpython3u ;

• Liste : \dL ou pg_language

Le langage est activé uniquement dans la base dans laquelle la commande est lancée. Il faudra donc répéter le CREATE EXTENSION dans chaque base au besoin (noter qu’activer un langage dans la base modèle template1 l’activera aussi pour toutes les bases créées par la suite, comme c’est déjà le cas pour le PL/pgSQL).

Pour voir les langages activés, utiliser la commande \dL qui reprend le contenu de la table système pg_language :

CREATE EXTENSION plperl ;
CREATE EXTENSION plpython3u ;
CREATE EXTENSION plsh ;
CREATE EXTENSION plr;

postgres=# \dL
                         Liste des langages
    Nom     | … | De confiance |                Description
------------+---+--------------+-------------------------------------------
 plperl     | … | t            | PL/PerlU untrusted procedural language
 plpgsql    | … | t            | PL/pgSQL procedural language
 plpython3u | … | f            | PL/Python3U untrusted procedural language
 plr        | … | f            |
 plsh       | … | f            | PL/sh procedural language

Noter la distinction entre les langages trusted (de confiance) et untrusted. Si un langage est trusted, tous les utilisateurs peuvent créer des procédures dans ce langage sans danger. Sinon seuls les superutilisateurs le peuvent.

Il existe par exemple deux variantes de PL/Perl : PL/Perl et PL/PerlU. La seconde est la variante untrusted et est un Perl « complet ». La version trusted n’a pas le droit d’ouvrir des fichiers, des sockets, ou autres appels systèmes qui seraient dangereux.

SQL, PL/pgSQL, PL/Tcl, PL/Perl (mais pas PL/Python) sont trusted et les utilisateurs peuvent les utiliser à volonté.

C, PL/TclU, PL/PerlU, et PL/Python3U sont untrusted. Un superutilisateur doit alors écrire les fonctions et procédures et opérer des GRANT EXECUTE aux utilisateurs.

Exemples de fonctions & procédures

Fonction PL/pgSQL simple

Une fonction simple en PL/pgSQL :

CREATE FUNCTION addition (entier1 integer, entier2 integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS '
DECLARE
  resultat integer;
BEGIN
  resultat := entier1 + entier2;
  RETURN resultat;
END ' ;

SELECT addition (1,2);

 addition
----------
        3

Exemple de fonction SQL

Même fonction en SQL pur :

CREATE FUNCTION addition (entier1 integer, entier2 integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS '     SELECT entier1 + entier2 ; ' ;

• Intérêt : inlining & planification
• Syntaxe allégée :

CREATE OR REPLACE FUNCTION addition (entier1 integer, entier2 integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE
RETURN entier1 + entier2 ;

Les fonctions simples peuvent être écrites en SQL pur. La syntaxe est plus claire, mais bien plus limitée qu’en PL/pgSQL (ni boucles, ni conditions, ni exceptions notamment).

Toujours pour les fonctions en SQL uniquement, il est possible de se passer des guillemets encadrants. La même fonction devient donc :

CREATE OR REPLACE FUNCTION addition (entier1 integer, entier2 integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE
RETURN entier1 + entier2 ;

Cette nouvelle écriture respecte mieux le standard SQL. Surtout, elle autorise un parsing et une vérification des objets impliqués dès la déclaration, et non à l’utilisation.

Les dépendances entre fonctions et objets utilisés sont aussi mieux tracées.

L’avantage principal des fonctions en pur SQL est, si elles sont assez simples, leur intégration lors de la réécriture interne de la requête (inlining) : elles ne sont donc pas pour l’optimiseur des « boîtes noires ». À l’inverse, l’optimiseur ne sait rien du contenu d’une fonction PL/pgSQL.

Dans l’exemple suivant, la fonction sert de filtre à la requête. Comme elle est en pur SQL, elle permet d’utiliser l’index sur la colonne date_embauche de la table employes_big :

CREATE FUNCTION employe_eligible_prime_sql (service int, date_embauche date)
RETURNS boolean
LANGUAGE sql
AS $$
  SELECT ( service !=3 AND date_embauche < '2003-01-01')  ;
$$ ;

EXPLAIN (ANALYZE) SELECT matricule, num_service, nom, prenom
FROM    employes_big
WHERE   employe_eligible_prime_sql (num_service, date_embauche) ;

                            QUERY PLAN
---------------------------------------------------------------------------------
 Index Scan using employes_big_date_embauche_idx on employes_big
       (cost=0.42..1.54 rows=1 width=22) (actual time=0.008..0.009 rows=1 loops=1)
   Index Cond: (date_embauche < '2003-01-01'::date)
   Filter: (num_service <> 3)
   Rows Removed by Filter: 1
 Planning Time: 0.102 ms
 Execution Time: 0.029 ms

Avec une version de la même fonction en PL/pgSQL, le planificateur ne voit pas le critère indexé. Il n’a pas d’autre choix que de lire toute la table et d’appeler la fonction pour chaque ligne, ce qui est bien sûr plus lent :

CREATE FUNCTION employe_eligible_prime_pl (service int, date_embauche date)
RETURNS boolean
LANGUAGE plpgsql AS $$
BEGIN
 RETURN ( service !=3 AND date_embauche < '2003-01-01') ;
END ;
$$ ;

EXPLAIN (ANALYZE) SELECT matricule, num_service, nom, prenom
FROM employes_big
WHERE  employe_eligible_prime_pl (num_service, date_embauche) ;

                            QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on employes_big   (cost=0.00..134407.90 rows=166338 width=22)
                            (actual time=0.069..269.121 rows=1 loops=1)
   Filter: employe_eligible_prime_pl(num_service, date_embauche)
   Rows Removed by Filter: 499014
 Planning Time: 0.038 ms
 Execution Time: 269.157 ms

Le wiki décrit les conditions pour que l’inlining des fonctions SQL fonctionne : obligation d’un seul SELECT, interdiction de certains fonctionnalités…

Exemple de fonction PL/pgSQL utilisant la base

CREATE OR REPLACE FUNCTION nb_lignes_table (sch text, tbl text)
 RETURNS bigint
 STABLE
AS '
DECLARE     n bigint ;
BEGIN
    SELECT n_live_tup
    INTO n
    FROM pg_stat_user_tables
    WHERE schemaname = sch AND relname = tbl ;
    RETURN n ;
END ; '
LANGUAGE plpgsql ;

Dans cet exemple, on récupère l’estimation du nombre de lignes actives d’une table passée en paramètres.

L’intérêt majeur du PL/pgSQL et du SQL sur les autres langages est la facilité d’accès aux données. Ici, un simple SELECT <champ> INTO <variable> suffit à récupérer une valeur depuis une table dans une variable.

SELECT nb_lignes_table ('public', 'pgbench_accounts');

 nb_lignes_table
-----------------
        10000000

Exemple de fonction PL/Perl complexe

• Permet d’insérer une facture associée à un client
• Si le client n’existe pas, une entrée est créée
• Utilisation fréquente de spi_exec

Voici l’exemple de la fonction :

CREATE OR REPLACE FUNCTION
    public.demo_insert_perl(nom_client text, titre_facture text)
 RETURNS integer
 LANGUAGE plperl
 STRICT
AS $function$
  use strict;
  my ($nom_client, $titre_facture)=@_;
  my $rv;
  my $id_facture;
  my $id_client;

  # Le client existe t'il ?
  $rv = spi_exec_query('SELECT id_client FROM mes_clients WHERE nom_client = '
    . quote_literal($nom_client)
  );
  # Sinon on le crée :
  if ($rv->{processed} == 0)
  {
    $rv = spi_exec_query('INSERT INTO mes_clients (nom_client) VALUES ('
        . quote_literal($nom_client) . ') RETURNING id_client'
    );
  }
  # Dans les deux cas, l'id client est dans $rv :
  $id_client=$rv->{rows}[0]->{id_client};

  # Insérons maintenant la facture
  $rv = spi_exec_query(
    'INSERT INTO mes_factures (titre_facture, id_client) VALUES ('
    . quote_literal($titre_facture) . ", $id_client ) RETURNING id_facture"
  );

  $id_facture = $rv->{rows}[0]->{id_facture};

  return $id_facture;
$function$ ;

Cette fonction n’est pas parfaite, elle ne protège pas de tout. Il est tout à fait possible d’avoir une insertion concurrente entre le SELECT et le INSERT par exemple.

Il est clair que l’accès aux données est malaisé en PL/Perl, comme dans la plupart des langages, puisqu’ils ne sont pas prévus spécifiquement pour cette tâche. Par contre, on dispose de toute la puissance de Perl pour les traitements de chaîne, les appels système…

PL/Perl, c’est :

• Perl, moins les fonctions pouvant accéder à autre chose qu’à PostgreSQL (il faut utiliser PL/PerlU pour passer outre cette limitation) ;
• un bloc de code anonyme appelé par PostgreSQL ;
• des fonctions d’accès à la base, spi_*

Exemple de fonction PL/pgSQL complexe

• Même fonction en PL/pgSQL que précédemment
• L’accès aux données est simple et naturel
• Les types de données SQL sont natifs
• La capacité de traitement est limitée par le langage
• Attention au nommage des variables et paramètres

Pour éviter les conflits avec les objets de la base, il est conseillé de préfixer les variables.

CREATE OR REPLACE FUNCTION
public.demo_insert_plpgsql(p_nom_client text, p_titre_facture text)
 RETURNS integer
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
  v_id_facture int;
  v_id_client int;
BEGIN
  -- Le client existe t'il ?
  SELECT id_client
  INTO v_id_client
  FROM mes_clients
  WHERE nom_client = p_nom_client;

  -- Sinon on le crée :
  IF NOT FOUND THEN
    INSERT INTO mes_clients (nom_client)
    VALUES (p_nom_client)
    RETURNING id_client INTO v_id_client;
  END IF;

  -- Dans les deux cas, l'id client est maintenant dans v_id_client

  -- Insérons maintenant la facture
  INSERT INTO mes_factures (titre_facture, id_client)
  VALUES (p_titre_facture, v_id_client)
  RETURNING id_facture INTO v_id_facture;

  return v_id_facture;
END;
$function$ ;

Exemple de procédure

CREATE OR REPLACE PROCEDURE vide_tables (dry_run BOOLEAN)
AS '
BEGIN
    TRUNCATE TABLE pgbench_history ;
    TRUNCATE TABLE pgbench_accounts CASCADE ;
    TRUNCATE TABLE pgbench_tellers  CASCADE ;
    TRUNCATE TABLE pgbench_branches CASCADE ;
    IF dry_run THEN
        ROLLBACK ;
    END IF ;
END ;
 ' LANGUAGE plpgsql ;

Cette procédure tronque des tables de la base d’exemple pgbench, et annule si dry_run est vrai.

Les procédures sont à utiliser quand on n’attend pas de résultat en retour. Surtout, elles permettent de gérer les transactions (COMMIT, ROLLBACK), ce qui ne peut se faire dans des fonctions, même si celles-ci peuvent modifier les données.

Une procédure ne peut utiliser le contrôle transactionnel que si elle est appelée en dehors de toute transaction.

Comme pour les fonctions, il est possible d’utiliser le SQL pur dans les cas les plus simples, sans contrôle transactionnel notamment :

CREATE OR REPLACE PROCEDURE vide_tables ()
AS '
    TRUNCATE TABLE pgbench_history ;
    TRUNCATE TABLE pgbench_accounts CASCADE ;
    TRUNCATE TABLE pgbench_tellers  CASCADE ;
    TRUNCATE TABLE pgbench_branches CASCADE ;
' LANGUAGE sql;

Toujours pour les procédures en SQL, il existe une variante sans guillemets, mais qui ne supporte pas tous les ordres. Comme pour les fonctions, l’intérêt est la prise en compte des dépendances entre objets et procédures.

CREATE OR REPLACE PROCEDURE vide_tables ()
BEGIN ATOMIC
    DELETE FROM pgbench_history ;
    DELETE FROM pgbench_accounts ;
    DELETE FROM pgbench_tellers ;
    DELETE FROM pgbench_branches ;
END ;

Exemple de bloc anonyme en PL/pgSQL

• Bloc procédural anonyme en PL/pgSQL :

DO $$
DECLARE r record;
BEGIN
    FOR r IN (SELECT schemaname, relname
              FROM pg_stat_user_tables
              WHERE coalesce(last_analyze, last_autoanalyze) IS NULL
              ) LOOP
        RAISE NOTICE 'Analyze %.%', r.schemaname, r.relname ;
        EXECUTE 'ANALYZE ' || quote_ident(r.schemaname)
                           || '.' || quote_ident(r.relname) ;
    END LOOP;
END$$;

Les blocs anonymes sont utiles pour des petits scripts ponctuels qui nécessitent des boucles ou du conditionnel, voire du transactionnel, sans avoir à créer une fonction ou une procédure. Ils ne renvoient rien. Ils sont habituellement en PL/pgSQL mais tout langage procédural installé est possible.

L’exemple ci-dessus lance un ANALYZE sur toutes les tables où les statistiques n’ont pas été calculées d’après la vue système, et donne aussi un exemple de SQL dynamique. Le résultat est par exemple :

NOTICE:  Analyze public.pgbench_history
NOTICE:  Analyze public.pgbench_tellers
NOTICE:  Analyze public.pgbench_accounts
NOTICE:  Analyze public.pgbench_branches
DO
Temps : 141,208 ms

(Pour ce genre de SQL dynamique, si l’on est sous psql , il est souvent plus pratique d’utiliser \gexec.)

Noter que les ordres constituent une transaction unique, à moins de rajouter des COMMIT ou ROLLBACK explicitement (ce n’est autorisé qu’à partir de la version 11).

Utiliser une fonction ou une procédure

Invocation d’une fonction ou procédure

• Appeler une procédure : ordre spécifique CALL

CALL ma_procedure('arg1');

• Appeler une fonction : dans une requête

SELECT ma_fonction('arg1', 'arg2') ;

SELECT * FROM ma_fonction('arg1', 'arg2') ;

INSERT INTO matable
SELECT ma_fonction( champ1, champ2 )   FROM ma_table2 ;

CALL ma_procedure( mafonction() );

CREATE INDEX ON ma_table ( ma_fonction(ma_colonne) );

Demander l’exécution d’une procédure se fait en utilisant un ordre SQL spécifique : CALL. Il suffit de fournir les paramètres. Il n’y a pas de code retour.

Les fonctions ne sont quant à elles pas directement compatibles avec la commande CALL, il faut les invoquer dans le contexte d’une commande SQL. Elles sont le plus couramment appelées depuis des commandes de type DML (SELECT, INSERT, etc.), mais on peut aussi les trouver dans d’autres commandes.

Voici quelques exemples :

• dans un SELECT (la fonction ne doit renvoyer qu’une seule ligne) :

  SELECT ma_fonction('arg1', 'arg2');

• dans un SELECT, en passant en argument les valeurs d’une colonne d’une table :

  SELECT ma_fonction(ma_colonne) FROM ma_table;

• dans le FROM d’un SELECT, la fonction renvoit ici généralement plusieurs lignes (SETOF), et un résultat de type RECORD :

  SELECT result FROM ma_fonction() AS f(result);

• dans un INSERT pour générer la valeur à insérer :

  INSERT INTO ma_table(ma_colonne) VALUES ( ma_fonction() );

• dans une création d’index (index fonctionnel, la fonction sera réellement appelée lors des mises à jour de l’index… attention la fonction doit être déclarée « immutable ») :

  CREATE INDEX ON ma_table ( ma_fonction(ma_colonne) );

• appel d’une fonction en paramètre d’une autre fonction ou d’une procédure, par exemple ici le résultat de la fonction ma_fonction() (qui doit renvoyer une seule ligne) est passé en argument d’entrée de la procédure ma_procedure() :

  CALL ma_procedure( ma_fonction() );

Par ailleurs, certaines fonctions sont spécialisées et ne peuvent être invoquées que dans le contexte pour lequel elles ont été conçues (fonctions trigger, d’agrégat, de fenêtrage, etc.).

Contrôle transactionnel dans les procédures

• COMMIT et ROLLBACK : possibles dans les procédures
• Pas de BEGIN
  • automatique après la fin d’une transaction dans le code
• Un seul niveau de transaction
  • pas de sous-transactions
  • pas d’appel depuis une transaction
• Incompatible avec une clause EXCEPTION

Une procédure peut contenir des ordres COMMIT ou ROLLBACK pour du contrôle transactionnel. (À l’inverse une fonction est une transaction unique, ou opère dans une transaction.)

Voici un exemple validant ou annulant une insertion suivant que le nombre est pair ou impair :

CREATE TABLE test1 (a int) ;

CREATE OR REPLACE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
  FOR i IN 0..5 LOOP
    INSERT INTO test1 (a) VALUES (i);
    IF i % 2 = 0 THEN
      COMMIT;
    ELSE
      ROLLBACK;
    END IF;
  END LOOP;
END
$$;

CALL transaction_test1();

SELECT * FROM test1;
 a | b
---+---
 0 |
 2 |
 4 |

Une exemple plus fréquemment utilisé est celui d’une procédure effectuant un traitement de modification des données par lots, et donc faisant un COMMIT à intervalle régulier.

Noter qu’il n’y a pas de BEGIN explicite dans la gestion des transactions. Après un COMMIT ou un ROLLBACK, un BEGIN est immédiatement exécuté.

On ne peut pas imbriquer des transactions, car PostgreSQL ne connaît pas les sous-transactions :

BEGIN ; CALL transaction_test1() ;

ERROR:  invalid transaction termination
CONTEXTE : PL/pgSQL function transaction_test1() line 6 at COMMIT

On ne peut pas utiliser en même temps une clause EXCEPTION et le contrôle transactionnel :

DO LANGUAGE plpgsql $$
BEGIN
  BEGIN
    INSERT INTO test1 (a) VALUES (1);
  COMMIT;
  INSERT INTO test1 (a) VALUES (1/0);
COMMIT;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'caught division_by_zero';
  END;
END;
$$;

ERREUR:  cannot commit while a subtransaction is active
CONTEXTE : fonction PL/pgSQL inline_code_block, ligne 5 à COMMIT

Création et maintenance des fonctions et procédures

Création

• CREATE FUNCTION
• CREATE PROCEDURE

Voici la syntaxe complète pour une fonction d’après la documentation :

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, …] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, …] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, … ]
    | WINDOW
    | { IMMUTABLE | STABLE | VOLATILE }
    | [ NOT ] LEAKPROOF
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } …

Voici la syntaxe complète pour une procédure d’après la documentation :

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, …] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, … ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } …

Noter qu’il n’y a pas de langage par défaut. Il est donc nécessaire de le spécifier à chaque création d’une routine comme dans les exemples ci-dessous.

Structure d’une routine PL/pgSQL

• Reprenons le code montré plus haut :

CREATE FUNCTION addition (entier1 integer, entier2 integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS '
DECLARE
  resultat integer;
BEGIN
  resultat := entier1 + entier2 ;
  RETURN resultat ;
END';

Le langage PL/pgSQL n’est pas sensible à la casse, tout comme SQL (sauf les noms des objets ou variables, si vous les mettez entre des guillemets doubles). L’opérateur de comparaison est =, l’opérateur d’affectation :=

Structure d’une routine PL/pgSQL (suite)

• DECLARE
  • déclaration des variables locales
• BEGIN
  • début du code de la routine
• END
  • la fin
• Instructions séparées par des points-virgules
• Commentaires commençant par -- ou compris entre /* et */

Une routine est composée d’un bloc de déclaration des variables locales et d’un bloc de code. Le bloc de déclaration commence par le mot clé DECLARE et se termine avec le mot clé BEGIN. Ce mot clé est celui qui débute le bloc de code. La fin est indiquée par le mot clé END.

Toutes les instructions se terminent avec des points-virgules. Attention, DECLARE, BEGIN et END ne sont pas des instructions.

Il est possible d’ajouter des commentaires. -- indique le début d’un commentaire qui se terminera en fin de ligne. Pour être plus précis dans la délimitation, il est aussi possible d’utiliser la notation C : /* est le début d’un commentaire et */ la fin.

Blocs nommés

• Labels de bloc possibles
• Plusieurs blocs d’exception possibles dans une routine
• Permet de préfixer des variables avec le label du bloc
• De donner un label à une boucle itérative
• Et de préciser de quelle boucle on veut sortir, quand plusieurs d’entre elles sont imbriquées

Indiquer le nom d’un label ainsi :

<<mon_label>>
-- le code (blocs DECLARE, BEGIN-END, et EXCEPTION)

ou bien (pour une boucle)

[ <<mon_label>> ]
LOOP
    ordres …
END LOOP [ mon_label ];

Bien sûr, il est aussi possible d’utiliser des labels pour des boucles FOR, WHILE, FOREACH.

On sort d’un bloc ou d’une boucle avec la commande EXIT, on peut aussi utiliser CONTINUE pour passer à l’exécution suivante d’une boucle sans terminer l’itération courante.

Par exemple :

EXIT [mon_label] WHEN compteur > 1;

Modification du code d’une routine

• CREATE OR REPLACE FUNCTION
• CREATE OR REPLACE PROCEDURE
• Une routine est définie par son nom et ses arguments
• Si type de retour différent, la fonction doit d’abord être supprimée puis recréée

Une routine est surchargeable. La seule façon de les différencier est de prendre en compte les arguments (nombre et type). Les noms des arguments peuvent être indiqués mais ils seront ignorés.

Deux routines identiques aux arguments près (on parle de prototype) ne sont pas identiques, mais bien deux routines distinctes.

CREATE OR REPLACE a principalement pour but de modifier le code d’une routine, mais il est aussi possible de modifier les méta-données.

Modification des méta-données d’une routine

• ALTER FUNCTION / ALTER PROCEDURE
• Une routine est définie par son nom et ses arguments
• Permet de modifier nom, propriétaire, schéma et autres options

Toutes les méta-données discutées plus haut sont modifiables avec un ALTER.

Suppression d’une routine

• Une routine est définie par son nom et ses arguments :

DROP FUNCTION addition (integer, integer) ;

DROP PROCEDURE public.vide_tables (boolean);
DROP PROCEDURE public.vide_tables ();

La suppression se fait avec l’ordre DROP.

Une fonction pouvant exister en plusieurs exemplaires, avec le même nom et des arguments de type différents, il faudra parfois parfois préciser ces derniers.

Utilisation des guillemets

• Les guillemets deviennent très rapidement pénibles
  • préférer $$
  • ou $fonction$, $toto$…

Définir une fonction entre guillemets simples (') devient très pénible dès que la fonction doit en contenir parce qu’elle contient elle-même des chaînes de caractères. PostgreSQL permet de remplacer les guillemets par $$, ou tout mot encadré de $.

Par exemple, on peut reprendre la syntaxe de déclaration de la fonction addition() précédente en utilisant cette méthode :

CREATE FUNCTION addition (entier1 integer, entier2 integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $ma_fonction_addition$
DECLARE
  resultat integer;
BEGIN
  resultat := entier1 + entier2;
  RETURN resultat;
END
$ma_fonction_addition$;

Ce peut être utile aussi dans tout code réalisant une concaténation de chaînes de caractères contenant des guillemets. La syntaxe traditionnelle impose de les multiplier pour les protéger, et le code devient difficile à lire. :

requete := requete || '' AND vin LIKE ''''bordeaux%'''' AND xyz ''

En voilà une simplification grâce aux dollars :

requete := requete || $sql$ AND vin LIKE 'bordeaux%' AND xyz $sql$

Si vous avez besoin de mettre entre guillemets du texte qui inclut $$, vous pouvez utiliser $Q$, et ainsi de suite. Le plus simple étant de définir un marqueur de fin de routine plus complexe, par exemple incluant le nom de la fonction.

Paramètres et retour des fonctions et procédures

Version minimaliste

CREATE FUNCTION fonction (entier integer, texte text)
RETURNS int  AS …

Ceci une forme de fonction très simple (et très courante) : deux paramètres en entrée (implicitement en entrée seulement), et une valeur en retour.

Dans le corps de la fonction, il est aussi possible d’utiliser une notation numérotée au lieu des noms de paramètre : le premier argument a pour nom $1, le deuxième $2, etc. C’est à éviter.

Tous les types sont utilisables, y compris les types définis par l’utilisateur. En dehors des types natifs de PostgreSQL, PL/pgSQL ajoute des types de paramètres spécifiques pour faciliter l’écriture des routines.

Paramètres IN, OUT, INOUT & retour

CREATE FUNCTION cree_utilisateur (
  nom text,                 -- IN
  type_id int DEFAULT 0     -- IN
) RETURNS id_utilisateur int  AS …

CREATE FUNCTION explose_date (
  IN  d date,
  OUT jour int, OUT mois int, OUT annee int
 ) AS …

• VARIADIC : nombre variable

Si le mode d’un argument est omis, IN est la valeur implicite : la valeur en entrée ne sera pas modifiée par la fonction.

Un paramètre OUT sera modifié. S’il s’agit d’une variable d’un bloc PL appelant, sa valeur sera modifiée. Un paramètre INOUT est un paramètre en entrée qui peut être également modifié.

Dans le corps d’une fonction, RETURN est inutile avec des paramètres OUT parce que c’est la valeur des paramètres OUT à la fin de la fonction qui est retournée, comme dans l’exemple plus bas.

L’option VARIADIC permet de définir une fonction avec un nombre d’arguments libres à condition de respecter le type de l’argument (comme printf en C par exemple). Seul un argument OUT peut suivre un argument VARIADIC : l’argument VARIADIC doit être le dernier de la liste des paramètres en entrée puisque tous les paramètres en entrée suivant seront considérées comme faisant partie du tableau variadic. Seuls les arguments IN et VARIADIC sont utilisables avec une fonction déclarée comme renvoyant une table (clause RETURNS TABLE, voir plus loin).

La clause DEFAULT permet de rendre les paramètres optionnels. Après le premier paramètre ayant une valeur par défaut, tous les paramètres qui suivent doivent aussi avoir une valeur par défaut. Pour rendre le paramètre optionnel, il doit être le dernier argument ou alors les paramètres suivants doivent aussi avoir une valeur par défaut.

Type en retour : 1 valeur simple

• Fonctions uniquement

RETURNS type     -- int, text, etc

• Tous les types de base & utilisateur
• Rien : void

Le type de retour (clause RETURNS dans l’entête) est obligatoire pour les fonctions et interdit pour les procédures.

Des exemples plus haut utilisent des types simples, mais tous ceux de PostgreSQL ou les types créés par l’utilisateur sont utilisables.

Depuis le corps de la fonction, le résultat est renvoyé par un appel à RETURN (PL/pgSQL) ou SELECT (SQL).

Type en retour : 1 ligne, plusieurs champs (exemple)

Comment obtenir ceci ?

SELECT * FROM explose_date ('31-12-2020');

 jour | mois | annee
------+------+-------
   31 |    0 |  2020

Type en retour : 1 ligne, plusieurs champs

3 options :

• Type composé dédié

CREATE TYPE ma_structure AS ( … ) ;
CREATE FUNCTION …  RETURNS ma_structure ;

• Paramètres OUT

CREATE FUNCTION explose_date (IN d date,
                              OUT jour int, OUT mois int, OUT annee int) AS …

• RETURNS TABLE

CREATE FUNCTION explose_date_table (d date)
RETURNS TABLE  (jour integer, mois integer, annee integer) AS…

S’il y a besoin de renvoyer plusieurs valeurs à la fois, une première possibilité est de renvoyer un type composé défini auparavant.

Une alternative très courante est d’utiliser plusieurs paramètres OUT (et pas de clause RETURN dans l’entête) pour obtenir un enregistrement composite :

CREATE OR REPLACE FUNCTION explose_date
             (IN d date, OUT jour int, OUT mois int, OUT annee int)
AS $$
SELECT extract (day FROM d)::int,
                extract(month FROM d)::int, extract (year FROM d)::int
$$
LANGUAGE sql;

SELECT * FROM explose_date ('31-12-2020');

 jour | mois | annee
------+------+-------
   31 |    0 |  2020

(Noter que l’exemple ci-dessus est en simple SQL.)

La clause TABLE est une autre alternative, sans doute plus claire. Cet exemple devient alors, toujours en pur SQL :

CREATE OR REPLACE FUNCTION explose_date_table (d date)
RETURNS TABLE (jour integer, mois integer, annee integer)
LANGUAGE sql
AS $$
  SELECT  extract (day FROM d)::int,
                   extract(month FROM d)::int, extract (year FROM d)::int ;
$$ ;

Retour multiligne

• 1 seul champ ou plusieurs ?

RETURNS SETOF type   -- int, text, type personnalisé

RETURNS TABLE ( col1 type, col2 type … )

• Ligne à ligne ou en bloc ?

RETURN NEXT …

RETURN QUERY   SELECT …
RETURN QUERY   EXECUTE …

• Le résultat est stocké puis envoyé

RETURNS SETOF :

Pour renvoyer plusieurs lignes, la première possibilité est de déclarer un type de retour SETOF. Cet exemple utilise RETURN NEXT pour renvoyer les lignes une à une :

CREATE OR REPLACE FUNCTION liste_entiers_setof (limite int)
 RETURNS SETOF integer
 LANGUAGE plpgsql
AS $$
BEGIN
  FOR i IN 1..limite LOOP
    RETURN NEXT i;
  END LOOP;
END
$$ ;

SELECT * FROM liste_entiers_setof (3) ;

 liste_entiers_setof
---------------------
                   1
                   2
                   3

Renvoyer une structure existante :

S’il y a plusieurs champs à renvoyer, une possibilité est d’utiliser un type dédié (composé), qu’il faudra cependant créer auparavant. L’exemple suivant utilise aussi un RETURN QUERY pour éviter d’itérer sur toutes les lignes du résultat :

CREATE TYPE pgt AS (schemaname text, tablename text) ;

CREATE OR REPLACE FUNCTION tables_by_owner (p_owner text)
RETURNS SETOF pgt
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY SELECT schemaname::text, tablename::text
               FROM pg_tables WHERE tableowner=p_owner
               ORDER BY tablename ;
END $$ ;

SELECT * FROM tables_by_owner ('pgbench');

 schemaname |    tablename
------------+------------------
 public     | pgbench_accounts
 public     | pgbench_branches
 public     | pgbench_history
 public     | pgbench_tellers

Si l’on veut renvoyer une structure correspondant exactement à une table ou vue, la syntaxe est très simple (il n’y a même pas besoin de %ROWTYPE) :

CREATE OR REPLACE FUNCTION  tables_jamais_analyzees ()
RETURNS SETOF pg_stat_user_tables
LANGUAGE sql
AS $$
  SELECT * FROM pg_stat_user_tables
  WHERE coalesce(last_analyze, last_autoanalyze) IS NULL ;
$$ ;

SELECT * FROM tables_jamais_analyzees() \gx

-[ RECORD 1 ]-------+------------------------------
relid               | 414453
schemaname          | public
relname             | table_nouvelle
…
n_mod_since_analyze | 10
n_ins_since_vacuum  | 10
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0
-[ RECORD 2 ]-------+------------------------------
…

NB : attention de ne pas oublier le SETOF, sinon une seule ligne sera retournée.

RETURNS TABLE :

On a vu que la clause TABLE permet de renvoyer plusieurs champs. Or, elle implique aussi SETOF, et les deux exemples ci-dessus peuvent devenir :

CREATE OR REPLACE FUNCTION liste_entiers_table (limite int)
RETURNS TABLE (j int)
AS $$
BEGIN
  FOR i IN 1..limite LOOP
    j = i ;
    RETURN NEXT ;  -- renvoie la valeur de j en cours
  END LOOP;
END $$ LANGUAGE plpgsql;

SELECT * FROM liste_entiers_table (3) ;
 j
---
 1
 2
 3

(Noter ici que le nom du champ retourné dépend du nom de la variable utilisée, et n’est pas forcément le nom de la fonction. En effet, chaque appel à RETURN NEXT retourne un enregistrement composé d’une copie de toutes les variables, au moment de l’appel à RETURN NEXT.)

DROP FUNCTION tables_by_owner ;
CREATE FUNCTION tables_by_owner (p_owner text)
RETURNS TABLE (schemaname text, tablename text)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY SELECT t.schemaname::text, t.tablename::text
               FROM pg_tables t WHERE tableowner=p_owner
               ORDER BY t.tablename ;
END $$ ;

Si RETURNS TABLE est peut-être le plus souple et le plus clair, le choix entre toutes ces méthodes est affaire de goût, ou de compatibilité avec du code ancien ou converti d’un produit concurrent.

Renvoyer le résultat d’une requête :

Les exemples ci-dessus utilisent RETURN NEXT (pour du ligne à ligne) ou RETURN QUERY (pour envoyer directement le résultat d’une requête).

La variante RETURN QUERY EXECUTE … est destinée à des requêtes en SQL dynamique (voir plus loin).

Quand plusieurs lignes sont renvoyées, tout est conservé en mémoire jusqu’à la fin de la fonction. S’il y en a beaucoup, cela peut poser des problèmes de latence, voire de mémoire. Le paramètre work_mem permet de définir la mémoire utilisée avant de basculer sur un fichier temporaire, qui a bien sûr un impact sur les performances.

Appel de fonction :

En général, l’appel se fait ainsi pour obtenir des lignes :

SELECT * FROM ma_fonction();

Une alternative est d’utiliser :

SELECT ma_fonction();

pour récupérer un résultat d’une seule colonne, scalaire, type composite ou RECORD suivant la fonction.

Cette différence concerne aussi les fonctions système :

SELECT * FROM pg_control_system () ;

 pg_control_version | catalog_version_no |  system_identifier  | pg_control_…
--------------------+--------------------+---------------------+-------------
               1201 |          201909212 | 6744959735975969621 | 2021-09-17 …
(1 ligne)

SELECT pg_control_system () ;

                       pg_control_system
---------------------------------------------------------------
 (1201,201909212,6744959735975969621,"2021-09-17 18:24:05+02")
 (1 ligne)

Gestion des valeurs NULL

Comment gérer les paramètres à NULL ?

• STRICT :
  • 1 paramètre NULL : retourne NULL immédiatement
• Défaut :
  • gestion par la fonction

Si une fonction est définie comme STRICT et qu’un des arguments d’entrée est NULL, PostgreSQL n’exécute même pas la fonction et utilise NULL comme résultat.

Dans la logique relationnelle, NULL signifie « la valeur est inconnue ». La plupart du temps, il est logique qu’une fonction ayant un paramètre à une valeur inconnue retourne aussi une valeur inconnue, ce qui fait que cette optimisation est très souvent pertinente.

On gagne à la fois en temps d’exécution, mais aussi en simplicité du code (il n’y a pas à gérer les cas NULL pour une fonction dans laquelle NULL ne doit jamais être injecté).

Dans la définition d’une fonction, les options sont STRICT ou son synonyme RETURNS NULL ON NULL INPUT, ou le défaut implicite CALLED ON NULL INPUT.

Variables en PL/pgSQL

Clause DECLARE

• Dans le source, partie DECLARE :

  DECLARE
    i  integer;
    j  integer := 5;
    k  integer NOT NULL DEFAULT 1;
    ch text    COLLATE "fr_FR";

• Blocs DECLARE/BEGIN/END imbriqués possible
  • restriction de scope de variable

En PL/pgSQL, pour utiliser une variable dans le corps de la routine (entre le BEGIN et le END), il est obligatoire de l’avoir déclarée précédemment :

• soit dans la liste des arguments (IN, INOUT ou OUT) ;
• soit dans la section DECLARE.

La déclaration doit impérativement préciser le nom et le type de la variable.

En option, il est également possible de préciser :

• sa valeur initiale (si rien n’est précisé, ce sera NULL par défaut) :

  answer integer := 42;

• sa valeur par défaut, si on veut autre chose que NULL :

  answer integer DEFAULT 42;

• une contrainte NOT NULL (dans ce cas, il faut impérativement un défaut différent de NULL, et toute éventuelle affectation ultérieure de NULL à la variable provoquera une erreur) :

  answer integer NOT NULL DEFAULT 42;

• le collationnement à utiliser, pour les variables de type chaîne de caractères :

  question text COLLATE "en_GB";

Pour les fonctions complexes, avec plusieurs niveaux de boucle par exemple, il est possible d’imbriquer les blocs DECLARE/BEGIN/END en y déclarant des variables locales à ce bloc. Si une variable est par erreur utilisée hors du scope prévu, une erreur surviendra.

Constantes

• Clause supplémentaire CONSTANT :

  DECLARE
    eur_to_frf   CONSTANT numeric := 6.55957 ;
    societe_nom  CONSTANT text    := 'Dalibo SARL';

L’option CONSTANT permet de définir une variable pour laquelle il sera alors impossible d’assigner une valeur dans le reste de la routine.

Types de variables

• Récupérer le type d’une autre variable avec %TYPE :

    quantite    integer ;
    total       quantite%TYPE ;

• Récupérer le type de la colonne d’une table :

    quantite    ma_table.ma_colonne%TYPE ;

Cela permet d’écrire des routines plus génériques.

Type ROW - 1

• Pour renvoyer plusieurs valeurs à partir d’une fonction
• Utiliser un type composite :

    CREATE TYPE ma_structure AS (
        un_entier integer,
        une_chaine text,
        …);

    CREATE FUNCTION ma_fonction () RETURNS ma_structure …;

Type ROW - 2

• Utiliser le type composite défini par la ligne d’une table

    CREATE FUNCTION ma_fonction () RETURNS integer
    AS $$
    DECLARE
      ligne ma_table%ROWTYPE;
    …
    $$

L’utilisation de %ROWTYPE permet de définir une variable qui contient la structure d’un enregistrement de la table spécifiée. %ROWTYPE n’est pas obligatoire, il est néanmoins préférable d’utiliser cette forme, bien plus portable. En effet, dans PostgreSQL, toute création de table crée un type associé de même nom, le seul nom de la table est donc suffisant.

Type RECORD

• RECORD identique au type ROW
  • …sauf que son type n’est connu que lors de son affectation
• RECORD peut changer de type au cours de l’exécution de la routine
• Curseur et boucle sur une requête

RECORD est beaucoup utilisé pour manipuler des curseurs, ou dans des boucles FOR … LOOP : cela évite de devoir se préoccuper de déclarer un type correspondant exactement aux colonnes de la requête associée à chaque curseur.

Type RECORD : exemple

CREATE FUNCTION ma_fonction () RETURNS integer
AS $$
DECLARE
  ligne RECORD;
BEGIN
  -- récupération de la 1è ligne uniquement
  SELECT * INTO ligne FROM ma_première_table;
  -- ou : traitement ligne à ligne
  FOR ligne IN SELECT * FROM ma_deuxième_table  LOOP
    …
  END LOOP ;
  RETURN … ;
END $$ ;

Dans ces exemples, on récupère la première ligne de la fonction avec SELECT … INTO, puis on ouvre un curseur implicite pour balayer chaque ligne obtenue d’une deuxième table. Le type RECORD permet de ne pas déclarer une nouvelle variable de type ligne.

Exécution de requête dans un bloc PL/pgSQL

Requête dans un bloc PL/pgSQL

• Toutes opérations sur la base de données
• Et calculs, comparaisons, etc.
• Toute expression écrite en PL/pgSQL sera passée à SELECT pour interprétation par le moteur
• PREPARE implicite, avec cache

Par expression, on entend par exemple des choses comme :

IF myvar > 0 THEN
    myvar2 := 1 / myvar;
END IF;

Dans ce cas, l’expression myvar > 0 sera préparée par le moteur de la façon suivante :

PREPARE statement_name(integer, integer) AS SELECT $1 > $2;

Puis cette requête préparée sera exécutée en lui passant en paramètre la valeur de myvar et la constante 0.

Si myvar est supérieur à 0, il en sera ensuite de même pour l’instruction suivante :

PREPARE statement_name(integer, integer) AS SELECT $1 / $2;

Comme toute requête préparée, son plan sera mis en cache.

Pour les détails, voir les dessous de PL/pgSQL.

Affectation d’une valeur à une variable

• Utiliser l’opérateur := :

    un_entier := 5;

• Utiliser SELECT INTO :

    SELECT 5 INTO un_entier;

Privilégiez la première écriture pour la lisibilité, la seconde écriture est moins claire et n’apporte rien puisqu’il s’agit ici d’une affectation de constante.

À noter que l’écriture suivante est également possible pour une affectation :

  ma_variable := une_colonne FROM ma_table WHERE id = 5;

Cette méthode profite du fait que toutes les expressions du code PL/pgSQL vont être passées au moteur SQL de PostgreSQL dans un SELECT pour être résolues. Cela va fonctionner, mais c’est très peu lisible, et donc non recommandé.

Exécution d’une requête

• Affectation de la ligne :

  SELECT *
  INTO ma_variable_ligne  -- type ROW ou RECORD
  FROM …;

• INTO STRICT pour garantir unicité

  • INTO seul : juste 1è ligne !

• Plus d’un enregistrement :

  • écrire une boucle

• Ordre statique :

  • colonnes, clause WHERE, tables figées

Récupérer une ligne de résultat d’une requête dans une ligne de type ROW ou RECORD se fait avec SELECT … INTO. La première ligne est récupérée. Généralement on préférera utiliser INTO STRICT pour lever une de ces erreurs si la requête renvoie zéro ou plusieurs lignes :

ERROR:  query returned no rows
ERROR:  query returned more than one row

Dans le cas du type ROW, la définition de la ligne doit correspondre parfaitement à la définition de la ligne renvoyée. Utiliser un type RECORD permet d’éviter ce type de problème. La variable obtient directement le type ROW de la ligne renvoyée.

Il est possible d’utiliser SELECT INTO avec une simple variable si l’on n’a qu’un champ d’une ligne à récupérer.

Cette fonction compte les tables, et en trace la liste (les tables ne font pas partie du résultat) :

CREATE OR REPLACE FUNCTION compte_tables () RETURNS int LANGUAGE plpgsql AS $$
DECLARE
  n int ;
  t RECORD ;
BEGIN
  SELECT count(*) INTO STRICT n
  FROM pg_tables ;

  FOR t IN SELECT * FROM pg_tables LOOP
    RAISE NOTICE 'Table %.%', t.schemaname, t.tablename;
  END LOOP ;

  RETURN n ;
END ;
$$ ;

# SELECT compte_tables ();

NOTICE:  Table pg_catalog.pg_foreign_server
NOTICE:  Table pg_catalog.pg_type
…
NOTICE:  Table public.pgbench_accounts
NOTICE:  Table public.pgbench_branches
NOTICE:  Table public.pgbench_tellers
NOTICE:  Table public.pgbench_history
 compte_tables
---------------
           186
(1 ligne)

Exécution d’une requête sans besoin du résultat

• PERFORM : résultat ignoré

PERFORM * FROM ma_table WHERE une_colonne>0 ;
PERFORM mafonction (argument1) ;

• Variable FOUND
  • si une ligne est affectée par l’instruction
• Nombre de lignes :

GET DIAGNOSTICS variable = ROW_COUNT;

On peut déterminer qu’aucune ligne n’a été trouvée par la requête en utilisant la variable FOUND :

PERFORM * FROM ma_table WHERE une_colonne>0;
IF NOT FOUND THEN
…
END IF;

Pour appeler une fonction, il suffit d’utiliser PERFORM de la manière suivante :

PERFORM mafonction(argument1);

Pour récupérer le nombre de lignes affectées par l’instruction exécutée, il faut récupérer la variable de diagnostic ROW_COUNT :

GET DIAGNOSTICS variable = ROW_COUNT;

Il est à noter que le ROW_COUNT récupéré ainsi s’applique à l’ordre SQL précédent, quel qu’il soit :

• PERFORM ;
• EXECUTE ;
• ou même à un ordre statique directement dans le code PL/pgSQL.

SQL dynamique

EXECUTE d’une requête

EXECUTE 'chaine' [INTO [STRICT] cible] [USING (paramètres)] ;

• Exécute la requête dans chaine
• chaine peut être construite à partir d’autres variables
• cible : résultat (une seule ligne)

EXECUTE dans un bloc PL/pgSQL permet notamment du SQL dynamique : l’ordre peut être construit dans une variable.

EXECUTE & requête dynamique : injection SQL

Si nom vaut : « 'Robert' ; DROP TABLE eleves ; »

que renvoie ceci ?

EXECUTE 'SELECT * FROM eleves WHERE nom = '|| nom ;

Un danger du SQL dynamique est de faire aveuglément confiance aux valeurs des variables en construisant un ordre SQL :

CREATE TEMP TABLE eleves (nom text, id int) ;
INSERT INTO eleves VALUES ('Robert', 0) ;

-- Mise à jour d'un ID
DO $f$
DECLARE
    nom text := $$'Robert' ; DROP TABLE eleves;$$  ;
    id  int ;
BEGIN
RAISE NOTICE 'A exécuter : %','SELECT * FROM eleves WHERE nom = '|| nom ;
EXECUTE 'UPDATE eleves SET id = 327 WHERE nom = '|| nom ;
END ;
$f$ LANGUAGE plpgsql ;

NOTICE:  A exécuter : SELECT * FROM eleves WHERE nom = 'Robert' ; DROP TABLE eleves;

\d+ eleves
Aucune relation nommée « eleves » n'a été trouvée.

Cet exemple est directement inspiré d’un dessin très connu de XKCD.

Dans la pratique, la variable nom (entrée ici en dur) proviendra par exemple d’un site web, et donc contient potentiellement des caractères terminant la requête dynamique et en insérant une autre, potentiellement destructrice.

Moins grave, une erreur peut être levée à cause d’une apostrophe (quote) dans une chaîne texte. Il existe effectivement des gens avec une apostrophe dans le nom.

Ce qui suit concerne le SQL dynamique dans des routines PL/pgSQL, mais le principe concerne tous les langages et clients, y compris psql et sa méta-commande \gexec. En SQL pur, la protection contre les injections SQL est un argument pour utiliser les requêtes préparées, dont l’ordre EXECUTE diffère de celui-ci du PL/pgSQL ci-dessous.

EXECUTE & requête dynamique : 3 possibilités

EXECUTE 'UPDATE tbl SET '
    || quote_ident(nom_colonne)
    || ' = '
    || quote_literal(nouvelle_valeur)
    || ' WHERE cle = '
    || quote_literal(valeur_cle) ;

EXECUTE format('UPDATE matable SET %I = %L '
   'WHERE clef = %L', nom_colonne, nouvelle_valeur, valeur_clef);

EXECUTE format('UPDATE table SET %I = $1 '
   'WHERE clef = $2', nom_colonne) USING nouvelle_valeur, valeur_clef;

Les trois exemples précédents sont équivalents.

Le premier est le plus simple au premier abord. Il utilise quote_ident et quote_literal pour protéger des injections SQL (voir plus loin).

Le second est plus lisible grâce à la fonction de formatage format qui évite ces concaténations et appelle implicitement les fonctions quote_% Si un paramètre ne peut pas prendre la valeur NULL, utiliser %L (équivalent de quote_nullable) et non %I (équivalent de quote_ident).

La troisième alternative avec USING et les paramètres numériques $1 et $2 est considérée comme la plus performante. (Voir les détails dans la documentation).

L’exemple complet suivant tiré de la documentation officielle utilise EXECUTE pour rafraîchir des vues matérialisées en masse.

CREATE FUNCTION rafraichir_vuemat() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    RAISE NOTICE 'Rafraîchissement de toutes les vues matérialisées…';

    FOR mviews IN
       SELECT n.nspname AS mv_schema,
              c.relname AS mv_name,
              pg_catalog.pg_get_userbyid(c.relowner) AS owner
         FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
        WHERE c.relkind = 'm'
     ORDER BY 1
    LOOP
        -- Maintenant "mviews" contient un enregistrement
        -- avec les informations sur la vue matérialisé
        RAISE NOTICE 'Rafraichissement de la vue matérialisée %.% (owner: %)…',
                     quote_ident(mviews.mv_schema),
                     quote_ident(mviews.mv_name),
                     quote_ident(mviews.owner);
        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I',
                       mviews.mv_schema, mviews.mv_name) ;
    END LOOP;

    RAISE NOTICE 'Fin du rafraîchissement';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

EXECUTE & requête dynamique (suite)

EXECUTE 'chaine' [INTO STRICT cible] [USING (paramètres)] ;

• STRICT : 1 résultat
  • sinon NO_DATA_FOUND ou TOO_MANY_ROWS
• Sans STRICT :
  • 1ère ligne ou NO_DATA_FOUND
• Nombre de lignes :
  • GET DIAGNOSTICS integer_var = ROW_COUNT

De la même manière que pour SELECT … INTO, utiliser STRICT permet de garantir qu’il y a exactement une valeur comme résultat de EXECUTE, ou alors une erreur sera levée.

Nous verrons plus loin comment traiter les exceptions.

Outils pour construire une requête dynamique

• quote_ident ()
  • pour mettre entre guillemets un identifiant d’un objet PostgreSQL (table, colonne, etc.)
• quote_literal ()
  • pour mettre entre guillemets une valeur (chaîne de caractères)
• quote_nullable ()
  • pour mettre entre guillemets une valeur (chaîne de caractères), sauf NULL qui sera alors renvoyé sans les guillemets
• || : concaténer
• Ou fonction format(…), équivalent de sprintf en C

La fonction format est l’équivalent de la fonction sprintf en C : elle formate une chaîne en fonction d’un patron et de valeurs à appliquer à ses paramètres et la retourne. Les types de paramètre reconnus par format sont :

• %I : est remplacé par un identifiant d’objet. C’est l’équivalent de la fonction quote_ident. L’objet en question est entouré de guillemets doubles si nécessaire ;
• %L : est remplacé par une valeur littérale. C’est l’équivalent de la fonction quote_literal. Des guillemets simples sont ajoutés à la valeur et celle-ci est correctement échappée si nécessaire ;
• %s : est remplacé par la valeur donnée sans autre forme de transformation ;
• %% : est remplacé par un simple %.

Voici un exemple d’utilisation de cette fonction, utilisant des paramètres positionnels :

SELECT format(
    'SELECT %I FROM %I WHERE %1$I=%3$L',
    'MaColonne',
    'ma_table',
    $$l'été$$
);

                           format
-------------------------------------------------------------
 SELECT "MaColonne" FROM ma_table WHERE "MaColonne"='l''été'

Structures de contrôle en PL/pgSQL

• But du PL : les traitements procéduraux

Tests conditionnels - 2

Exemple :

IF nombre = 0 THEN
  resultat := 'zero';
ELSEIF nombre > 0 THEN
   resultat := 'positif';
ELSEIF nombre < 0 THEN
   resultat := 'négatif';
ELSE
   resultat := 'indéterminé';
END IF;

Tests conditionnels : CASE

    CASE
    WHEN nombre = 0  THEN 'zéro'
    WHEN variable > 0  THEN 'positif'
    WHEN variable < 0  THEN 'négatif'
    ELSE 'indéterminé'
    END CASE

L’instruction CASE WHEN est proche de l’expression CASE des requêtes SQL dans son principe (à part qu’elle se clôt par END en SQL, et END CASE en PL/pgSQL).

Elle est parfois plus légère à lire que des IF imbriqués.

Cet exemple complet en PL/pgSQL utilise une variante de syntaxe qui permet de ne pas répéter le critère :

DO $$
BEGIN
CASE current_setting ('server_version_num')::int/10000
    WHEN 8,9,10,11,12,13 THEN RAISE NOTICE 'Version non supportée !!' ;
    WHEN 14,15,16,17,18  THEN RAISE NOTICE 'Version supportée' ;
    ELSE                 RAISE NOTICE 'Version inconnue (fin 2023)' ;
END CASE ;
END ;
$$ LANGUAGE plpgsql ;

Boucle LOOP/EXIT/CONTINUE : syntaxe

• Boucle :
  • LOOP / END LOOP
  • label possible
• En sortir :
  • EXIT [label] [WHEN expression_booléenne]
• Commencer une nouvelle itération de la boucle
  • CONTINUE [label] [WHEN expression_booléenne]

Des boucles simples s’effectuent avec LOOP/END LOOP.

Pour les détails, voir la documentation officielle.

Boucle LOOP/EXIT/CONTINUE : exemple

LOOP
  resultat := resultat + 1;
  EXIT WHEN resultat > 100;
  CONTINUE WHEN resultat < 50;
  resultat := resultat + 1;
END LOOP;

Cette boucle incrémente le résultat de 1 à chaque itération tant que la valeur du résultat est inférieure à 50. Ensuite, le résultat est incrémenté de 1 à deux reprises pour chaque tour de boucle. On incrémente donc de 2 par tour de boucle. Arrivée à 100, la procédure sort de la boucle.

Boucle WHILE

    WHILE condition LOOP
      …
    END LOOP;

• Boucle jusqu’à ce que la condition soit fausse
• Label possible

Boucle FOR : syntaxe

    FOR variable in [REVERSE] entier1..entier2 [BY incrément]
    LOOP
      …
    END LOOP;

• variable va obtenir les différentes valeurs entre entier1 et entier2
• Label possible

La boucle FOR n’a pas d’originalité par rapport à d’autres langages.

L’option BY permet d’augmenter l’incrémentation :

    FOR variable in 1..10 BY 5…

L’option REVERSE permet de faire défiler les valeurs en ordre inverse :

    FOR variable in REVERSE 10..1 …

Boucle FOR … IN … LOOP : parcours de résultat de requête

    FOR ligne IN ( SELECT * FROM ma_table ) LOOP
      …
    END LOOP;