# PL/pgSQL : les bases > Transcription française complète du chapitre « PL/pgSQL : les bases » du manuel DEV42, révision 26.09, pages PDF 173 à 256. --- Index LLMS : [llms.txt](/fr/llms.txt) --- # PL/pgSQL : les bases {#dev42-ch3} Illustration du manuel DEV42 ## Préambule {#dev42-ch3-1} - 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 {#dev42-ch3-1-1} - 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 {#dev42-ch3-1-2} - 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 {#dev42-ch3-2} ### Qu’est-ce qu’un langage PL ? {#dev42-ch3-2-1} - 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 ? {#dev42-ch3-2-2} - 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](https://docs.postgresql.fr/current/plpgsql.html) est intégré par défaut dans toute nouvelle base (de par sa présence dans la base modèle **template1**) ; - [PL/Tcl](https://docs.postgresql.fr/current/pltcl.html) ; - [PL/Perl](https://docs.postgresql.fr/current/plperl.html) ; - [PL/Python](https://docs.postgresql.fr/current/plpython.html). 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](https://wiki.postgresql.org/wiki/PL_Matrix), à 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](https://tada.github.io/pljava/) ou [PL/R](https://github.com/postgres-plr/plr). S’ajoutent aussi [PL/Rust](https://plrust.io/), JavaScript ([PLV8](https://github.com/plv8/plv8)), [PL/Haskell](https://github.com/ed-o-saurus/PLHaskell), ou n’importe quel langage shell (via [PL/sh](https://github.com/petere/plsh)). De plus, il est possible d’en ajouter d’autres, comme décrit [dans la documentation](https://docs.postgresql.fr/current/plhandler.html). ### Langages *trusted* vs *untrusted* {#dev42-ch3-2-3} - *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 : ``` sql 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 {#dev42-ch3-2-4} 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](https://github.com/postgres-plr/plr/blob/master/userguide.md). 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](https://docs.postgresql.fr/current/xfunc-c.html). 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 {#dev42-ch3-2-5} - **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 {#dev42-ch3-2-6} - **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 {#dev42-ch3-3} ### Installation des binaires nécessaires {#dev42-ch3-3-1} - SQL, C et PL/pgSQL - compilés et installés par défaut - Paquets du PGDG pour la plupart des langages : ``` bash 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` : ``` bash 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 {#dev42-ch3-3-2} Activer un langage passe par la création de l’extension : ``` sql 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` : ``` sql CREATE EXTENSION plperl ; CREATE EXTENSION plpython3u ; CREATE EXTENSION plsh ; CREATE EXTENSION plr; ``` ``` default 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 {#dev42-ch3-4} ### Fonction PL/pgSQL simple {#dev42-ch3-4-1} Une fonction simple en PL/pgSQL : ``` sql CREATE FUNCTION addition (entier1 integer, entier2 integer) RETURNS integer LANGUAGE plpgsql IMMUTABLE AS ' DECLARE resultat integer; BEGIN resultat := entier1 + entier2; RETURN resultat; END ' ; ``` ``` sql SELECT addition (1,2); ``` ``` default addition ---------- 3 ``` ### Exemple de fonction SQL {#dev42-ch3-4-2} Même fonction en SQL pur : ``` sql 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 : ``` sql 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 : ``` sql 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` : ``` sql 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') ; $$ ; ``` ``` sql EXPLAIN (ANALYZE) SELECT matricule, num_service, nom, prenom FROM employes_big WHERE employe_eligible_prime_sql (num_service, date_embauche) ; ``` ``` default 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 : ``` sql 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 ; $$ ; ``` ``` sql EXPLAIN (ANALYZE) SELECT matricule, num_service, nom, prenom FROM employes_big WHERE employe_eligible_prime_pl (num_service, date_embauche) ; ``` ``` default 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](https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions) 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 {#dev42-ch3-4-3} ``` sql 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 INTO ` suffit à récupérer une valeur depuis une table dans une variable. ``` sql SELECT nb_lignes_table ('public', 'pgbench_accounts'); ``` ``` default nb_lignes_table ----------------- 10000000 ``` ### Exemple de fonction PL/Perl complexe {#dev42-ch3-4-4} - 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 : ``` perl 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 {#dev42-ch3-4-5} - 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. ``` sql 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 {#dev42-ch3-4-6} ``` sql 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 : ``` sql 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. ``` sql 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 {#dev42-ch3-4-7} - Bloc procédural anonyme en PL/pgSQL : ``` sql 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 : ``` sh 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`](https://docs.postgresql.fr/current/app-psql.html#R2-APP-PSQL-4).) 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 {#dev42-ch3-5} ### Invocation d’une fonction ou procédure {#dev42-ch3-5-1} - Appeler une procédure : ordre spécifique `CALL` ``` sql CALL ma_procedure('arg1'); ``` - Appeler une fonction : dans une requête ``` sql 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`](https://docs.postgresql.fr/current/sql-call.html). 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) : ``` sql SELECT ma_fonction('arg1', 'arg2'); ``` - dans un `SELECT`, en passant en argument les valeurs d’une colonne d’une table : ``` sql 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` : ``` sql SELECT result FROM ma_fonction() AS f(result); ``` - dans un `INSERT` pour générer la valeur à insérer : ``` sql 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 ») : ``` sql 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()` : ``` sql 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 {#dev42-ch3-6} - `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 : ``` sql 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 $$; ``` ``` default 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 : ``` sql 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 : ``` sql 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 {#dev42-ch3-7} ### Création {#dev42-ch3-7-1} - `CREATE FUNCTION` - `CREATE PROCEDURE` Voici la syntaxe complète pour une fonction d’après la [documentation](https://www.postgresql.org/docs/current/sql-createfunction.html) : ``` sql 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](https://www.postgresql.org/docs/current/sql-createprocedure.html) : ``` sql 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 {#dev42-ch3-7-2} - Reprenons le code montré plus haut : ``` sql 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) {#dev42-ch3-7-3} - `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 {#dev42-ch3-7-4} - 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 : ``` sql <> -- le code (blocs DECLARE, BEGIN-END, et EXCEPTION) ``` ou bien (pour une boucle) ``` sql [ <> ] 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 : ``` sql EXIT [mon_label] WHEN compteur > 1; ``` ### Modification du code d’une routine {#dev42-ch3-7-5} - `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 {#dev42-ch3-7-6} - `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 {#dev42-ch3-7-7} - Une routine est définie par son nom et ses arguments : ``` sql DROP FUNCTION addition (integer, integer) ; ``` ``` sql 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 {#dev42-ch3-7-8} - 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 : ``` sql 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. : ``` sql requete := requete || '' AND vin LIKE ''''bordeaux%'''' AND xyz '' ``` En voilà une simplification grâce aux dollars : ``` sql 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 {#dev42-ch3-8} ### Version minimaliste {#dev42-ch3-8-1} ``` sql 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 {#dev42-ch3-8-2} ``` sql CREATE FUNCTION cree_utilisateur ( nom text, -- IN type_id int DEFAULT 0 -- IN ) RETURNS id_utilisateur int AS … ``` ``` sql 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 {#dev42-ch3-8-3} - Fonctions uniquement ``` sql 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) {#dev42-ch3-8-4} Comment obtenir ceci ? ``` sql SELECT * FROM explose_date ('31-12-2020'); ``` ``` default jour | mois | annee ------+------+------- 31 | 0 | 2020 ``` ### Type en retour : 1 ligne, plusieurs champs {#dev42-ch3-8-5} 3 options : - Type composé dédié ``` sql CREATE TYPE ma_structure AS ( … ) ; CREATE FUNCTION … RETURNS ma_structure ; ``` - Paramètres `OUT` ``` sql CREATE FUNCTION explose_date (IN d date, OUT jour int, OUT mois int, OUT annee int) AS … ``` - `RETURNS TABLE` ``` sql 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 : ``` sql 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; ``` ``` sql SELECT * FROM explose_date ('31-12-2020'); ``` ``` default 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 : ``` 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 {#dev42-ch3-8-6} - 1 seul champ ou plusieurs ? ``` sql RETURNS SETOF type -- int, text, type personnalisé ``` ``` sql RETURNS TABLE ( col1 type, col2 type … ) ``` - Ligne à ligne ou en bloc ? ``` sql RETURN NEXT … ``` ``` sql 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 : ``` sql 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 $$ ; ``` ``` sql SELECT * FROM liste_entiers_setof (3) ; ``` ``` default 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 : ``` sql 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 $$ ; ``` ``` sql SELECT * FROM tables_by_owner ('pgbench'); ``` ``` default 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`) : ``` sql 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 ; $$ ; ``` ``` sql SELECT * FROM tables_jamais_analyzees() \gx ``` ``` default -[ 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 : ``` sql 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; ``` ``` default 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`.) ``` sql 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 : ``` sql SELECT * FROM ma_fonction(); ``` Une alternative est d’utiliser : ``` sql 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 : ``` sql SELECT * FROM pg_control_system () ; ``` ``` default pg_control_version | catalog_version_no | system_identifier | pg_control_… --------------------+--------------------+---------------------+------------- 1201 | 201909212 | 6744959735975969621 | 2021-09-17 … (1 ligne) ``` ``` sql SELECT pg_control_system () ; ``` ``` default pg_control_system --------------------------------------------------------------- (1201,201909212,6744959735975969621,"2021-09-17 18:24:05+02") (1 ligne) ``` ### Gestion des valeurs NULL {#dev42-ch3-8-7} 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 {#dev42-ch3-9} ### Clause DECLARE {#dev42-ch3-9-1} - Dans le source, partie `DECLARE` : ``` sql 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) : ``` sql answer integer := 42; ``` - sa valeur par défaut, si on veut autre chose que `NULL` : ``` sql 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) : ``` sql answer integer NOT NULL DEFAULT 42; ``` - le collationnement à utiliser, pour les variables de type chaîne de caractères : ``` sql 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 {#dev42-ch3-9-2} - Clause supplémentaire `CONSTANT` : ``` sql 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 {#dev42-ch3-9-3} - Récupérer le type d’une autre variable avec `%TYPE` : ``` sql quantite integer ; total quantite%TYPE ; ``` - Récupérer le type de la colonne d’une table : ``` sql quantite ma_table.ma_colonne%TYPE ; ``` Cela permet d’écrire des routines plus génériques. ### Type ROW - 1 {#dev42-ch3-9-4} - Pour renvoyer plusieurs valeurs à partir d’une fonction - Utiliser un type composite : ``` sql CREATE TYPE ma_structure AS ( un_entier integer, une_chaine text, …); ``` ``` sql CREATE FUNCTION ma_fonction () RETURNS ma_structure …; ``` ### Type ROW - 2 {#dev42-ch3-9-5} - Utiliser le type composite défini par la ligne d’une table ``` sql 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 {#dev42-ch3-9-6} - `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 {#dev42-ch3-9-7} ``` sql 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 {#dev42-ch3-10} ### Requête dans un bloc PL/pgSQL {#dev42-ch3-10-1} - 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 : ``` sql 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 : ``` sql 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 : ``` sql 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](https://docs.postgresql.fr/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING). ### Affectation d’une valeur à une variable {#dev42-ch3-10-2} - Utiliser l’opérateur `:=` : ``` sql un_entier := 5; ``` - Utiliser `SELECT INTO` : ``` sql 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 : ``` sql 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 {#dev42-ch3-10-3} - Affectation de la ligne : ``` sql 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 : ``` default 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) : ``` sql 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 ; $$ ; ``` ``` sql # SELECT compte_tables (); ``` ``` default 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 {#dev42-ch3-10-4} - `PERFORM` : résultat ignoré ``` sql 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 : ``` sql 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` : ``` sql 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 : ``` sql 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` : ``` sql 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 {#dev42-ch3-11} ### EXECUTE d’une requête {#dev42-ch3-11-1} ``` sql 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 {#dev42-ch3-11-2} Si `nom` vaut : « `'Robert' ; DROP TABLE eleves ;` » que renvoie ceci ? ``` sql 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 : ``` 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 ; ``` ``` default 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](https://xkcd.com/327/). > 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`](https://docs.postgresql.fr/current/app-psql.html#APP-PSQL-META-COMMANDS). En SQL pur, la protection contre les injections SQL est un argument pour utiliser les [requêtes préparées](https://docs.postgresql.fr/current/sql-prepare.html), dont l’ordre `EXECUTE` diffère de celui-ci du PL/pgSQL ci-dessous. ### EXECUTE & requête dynamique : 3 possibilités {#dev42-ch3-11-3} ``` sql EXECUTE 'UPDATE tbl SET ' || quote_ident(nom_colonne) || ' = ' || quote_literal(nouvelle_valeur) || ' WHERE cle = ' || quote_literal(valeur_cle) ; ``` ``` sql EXECUTE format('UPDATE matable SET %I = %L ' 'WHERE clef = %L', nom_colonne, nouvelle_valeur, valeur_clef); ``` ``` sql 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](https://fr.wikipedia.org/wiki/Injection_SQL) (voir plus loin). Le second est plus lisible grâce à la fonction de formatage [`format`](https://docs.postgresql.fr/current/functions-string.html#FUNCTIONS-STRING-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](https://docs.postgresql.fr/current/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE)). L’exemple complet suivant [tiré de la documentation officielle](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE) utilise `EXECUTE` pour rafraîchir des vues matérialisées en masse. ``` sql 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) {#dev42-ch3-11-4} ``` sql 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 {#dev42-ch3-11-5} - `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 : ``` sql SELECT format( 'SELECT %I FROM %I WHERE %1$I=%3$L', 'MaColonne', 'ma_table', $$l'été$$ ); ``` ``` default format ------------------------------------------------------------- SELECT "MaColonne" FROM ma_table WHERE "MaColonne"='l''été' ``` ## Structures de contrôle en PL/pgSQL {#dev42-ch3-12} - But du PL : les traitements procéduraux ### Tests conditionnels - 2 {#dev42-ch3-12-1} Exemple : ``` sql 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 {#dev42-ch3-12-2} ``` sql 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`](https://docs.postgresql.fr/current/functions-conditional.html#FUNCTIONS-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 : ``` sql 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 {#dev42-ch3-12-3} - 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](https://docs.postgresql.fr/current/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS). ### Boucle LOOP/EXIT/CONTINUE : exemple {#dev42-ch3-12-4} ``` sql 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 {#dev42-ch3-12-5} ``` sql WHILE condition LOOP … END LOOP; ``` - Boucle jusqu’à ce que la condition soit fausse - Label possible ### Boucle FOR : syntaxe {#dev42-ch3-12-6} ``` sql 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 : ``` sql FOR variable in 1..10 BY 5… ``` L’option `REVERSE` permet de faire défiler les valeurs en ordre inverse : ``` sql FOR variable in REVERSE 10..1 … ``` ### Boucle FOR … IN … LOOP : parcours de résultat de requête {#dev42-ch3-12-7} ``` sql FOR ligne IN ( SELECT * FROM ma_table ) LOOP … END LOOP; ``` - Pour boucler dans les lignes résultats d’une requête - `ligne` de type `RECORD`, `ROW`, ou liste de variables séparées par des virgules - Utilise un curseur en interne - Label possible Cette syntaxe très pratique permet de parcourir les lignes résultant d’une requête sans avoir besoin de créer et parcourir un curseur. Souvent on utilisera une variable de type `ROW` ou `RECORD` (comme dans l’exemple de la fonction `rafraichir_vuemat` plus haut), mais l’utilisation directe de variables (déclarées préalablement) est possible : ``` sql FOR a, b, c, d IN (SELECT col_a, col_b, col_c, col_d FROM ma_table) LOOP -- instructions utilisant ces variables … END LOOP; ``` Attention de ne pas utiliser les variables en question hors de la boucle, elles auront gardé la valeur acquise dans la dernière itération. ### Boucle FOREACH {#dev42-ch3-12-8} ``` sql FOREACH variable [SLICE n] IN ARRAY expression LOOP … END LOOP ; ``` - Pour boucler sur les éléments d’un tableau - `variable` va obtenir les différentes valeurs du tableau retourné par `expression` - `SLICE` permet de jouer sur le nombre de dimensions du tableau à passer à la variable - Label possible Voici deux exemples permettant d’illustrer l’utilité de `SLICE` : - sans `SLICE` : ``` sql DO $$ DECLARE a int[] := ARRAY[[1,2],[3,4],[5,6]]; b int; BEGIN FOREACH b IN ARRAY a LOOP RAISE INFO 'var: %', b; END LOOP; END $$ ; ``` ``` default INFO: var: 1 INFO: var: 2 INFO: var: 3 INFO: var: 4 INFO: var: 5 INFO: var: 6 ``` - avec `SLICE` : ``` sql DO $$ DECLARE a int[] := ARRAY[[1,2],[3,4],[5,6]]; b int[]; BEGIN FOREACH b SLICE 1 IN ARRAY a LOOP RAISE INFO 'var: %', b; END LOOP; END $$; ``` ``` default INFO: var: {1,2} INFO: var: {3,4} INFO: var: {5,6} ``` et avec `SLICE 2`, on obtient : ``` default INFO: var: {{1,2},{3,4},{5,6}} ``` ## Autres propriétés des fonctions {#dev42-ch3-13} - Sécurité - Optimisations - Parallélisation ### Politique de sécurité {#dev42-ch3-13-1} - `SECURITY INVOKER` : défaut - `SECURITY DEFINER` - « sudo de la base de données » - potentiellement dangereux - ne pas laisser à **public** ! Une fonction `SECURITY INVOKER` s’exécute avec les droits de l’appelant. C’est le mode par défaut. Une fonction `SECURITY DEFINER` s’exécute avec les droits du créateur. Cela permet, au travers d’une fonction, de permettre à un utilisateur d’outrepasser ses droits de façon contrôlée. C’est l’équivalent du `sudo` d’Unix. > Bien sûr, une fonction `SECURITY DEFINER` doit faire l’objet d’encore plus d’attention qu’une fonction normale. Elle peut facilement constituer un trou béant dans la sécurité de votre base. C’est encore plus important si le propriétaire de la fonction est un superutilisateur, car celui-ci a la possibilité d’accéder aux fichiers de PostgreSQL et au système d’exploitation. > Plusieurs points importants sont à noter pour `SECURITY DEFINER` : > > - Par défaut, toute fonction créée dans **public** est exécutable par le rôle **public**. La première chose à faire est donc de révoquer ce droit. Mieux : créer la fonction dans un schéma séparé est recommandé pour gérer plus finalement les accès. > > - Il faut se protéger des variables de session qui pourraient être utilisées pour modifier le comportement de la fonction, en particulier le *search_path* (qui pourrait faire pointer vers des tables de même nom dans un autre schéma). Il doit donc **impérativement** être positionné en dur dans cette fonction (soit d’emblée, avec un `SET` en début de fonction, soit en positionnant un `SET` dans le `CREATE FUNCTION`) ; et/ou les fonctions doivent préciser systématiquement le schéma dans les appels de tables (`SELECT … FROM nomschema.nomtable …`). **Exemple d’une fonction en `SECURITY DEFINER` avec un *search path* sécurisé** : ``` sql \c pgbench pgbench -- A exécuter en tant que pgbench, propriétaire de la base pgbench CREATE SCHEMA pgbench_util ; CREATE OR REPLACE FUNCTION pgbench_util.accounts_balance (pbid integer) RETURNS integer LANGUAGE sql IMMUTABLE PARALLEL SAFE SECURITY DEFINER SET search_path TO '' -- précaution supplémentaire AS $function$ SELECT bbalance FROM public.pgbench_branches br WHERE br.bid = pbid ; $function$ ; GRANT USAGE ON SCHEMA pgbench_util TO lecteur ; GRANT EXECUTE ON FUNCTION pgbench_util.accounts_balance TO lecteur ; ``` L’utilisateur **lecteur** peut bien lire le résultat de la fonction sans accès à la table : ``` sql \c pgbench lecteur SELECT pgbench_util.accounts_balance (5) ; ``` ``` default accounts_balance ------------------ 0 ``` **Exemple de fonction laxiste et d’attaque** : ``` sql -- Exemple sur une base pgbench, appartenant à pgbench -- créée par exemple ainsi : -- createdb pgbench -O pgbench -- pgbench -U pgbench -i -s 1 pgbench -- Deux utilisateurs : -- pgbench -- attaquant qui a son propre schéma \set timing off \set ECHO all \set ON_ERROR_STOP 1 \c pgbench pgbench -- Fonction non sécurisée fournie par l'utilisateur pgbench -- à tout le monde par public CREATE OR REPLACE FUNCTION public.accounts_balance_insecure(pbid integer) RETURNS integer LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE SECURITY DEFINER -- oublié : SET search_path TO '' AS $function$ BEGIN RETURN bbalance FROM /* pas de schéma */ pgbench_branches br WHERE br.bid = pbid ; END $function$ ; -- Droits trop ouverts GRANT EXECUTE ON FUNCTION accounts_balance_insecure TO public ; -- Résultat normal : renvoie 0 SELECT * FROM accounts_balance_insecure (1) ; -- Création d'un utilisateur avec droit d'écrire dans un schéma \c pgbench postgres DROP SCHEMA IF EXISTS piege CASCADE ; --DROP ROLE attaquant ; CREATE ROLE attaquant LOGIN ; -- pg_hba.conf laissé en exercice au lecteur -- Il faut que l'attaquant ait un schéma où écrire, -- et puisse donner l'accès à la victime. -- Le schéma public convient parfaitement pour cela avant PostgreSQL 15… CREATE SCHEMA piege ; GRANT ALL ON SCHEMA piege TO attaquant WITH GRANT OPTION ; \c pgbench attaquant \conninfo -- Résultat normal (accès peut-être indu mais pour le moment sans danger) SELECT * FROM accounts_balance_insecure (1) ; -- L'attaquant peut voir la fonction et étudier comment la détourner \sf accounts_balance_insecure -- Fonction que l'attaquant veut faire exécuter à pgbench CREATE FUNCTION piege.lit_donnees_cachees () RETURNS TABLE (bid int, bbalance int) LANGUAGE plpgsql AS $$ DECLARE n int ; BEGIN -- affichage de l'utilisateur pgbench RAISE NOTICE 'Entrée dans fonction piégée en tant que %', current_user ; -- copie de données non autorisées dans le schéma de l'attaquant CREATE TABLE piege.donnees_piratees AS SELECT * FROM pgbench_tellers ; GRANT ALL ON piege.donnees_piratees TO attaquant ; -- destruction de données… DROP TABLE IF EXISTS pgbench_history ; -- sortie propre impérative pour éviter le rollback RETURN QUERY SELECT 666 AS bid, 42 AS bbalance ; END ; $$ ; -- Vue d'enrobage pour « masquer » la vraie table de même nom CREATE OR REPLACE VIEW piege.pgbench_branches AS SELECT * FROM piege.lit_donnees_cachees () ; -- Donner les droits au compte attaqué sur les objets -- de l'attaquant GRANT USAGE,CREATE ON SCHEMA piege TO pgbench ; GRANT ALL ON piege.pgbench_branches TO pgbench ; GRANT ALL ON FUNCTION piege.lit_donnees_cachees TO pgbench ; -- Détournement du chemin d'accès SET search_path TO piege,public ; -- Attaque SELECT * FROM accounts_balance_insecure (666) ; -- Lecture des données piratées SELECT COUNT (*) as nb_lignes_recuperees FROM piege.donnees_piratees ; ``` ### Optimisation des fonctions {#dev42-ch3-13-2} - Fonctions uniquement - À destination de l’optimiseur - `COST cout_execution` - coût estimé pour l’exécution de la fonction - `ROWS nb_lignes_resultat` - nombre estimé de lignes que la fonction renvoie `COST` est un coût représenté en unité de `cpu_operator_cost` (100 par défaut). `ROWS` vaut par défaut 1000 pour les fonctions `SETOF` ou `TABLE`, et 1 pour les autres. Ces deux paramètres ne modifient pas le comportement de la fonction. Ils ne servent que pour aider l’optimiseur de requête à estimer le coût d’appel à la fonction, afin de savoir, si plusieurs plans sont possibles, lequel est le moins coûteux par rapport au nombre d’appels de la fonction et au nombre d’enregistrements qu’elle retourne. ### Parallélisation {#dev42-ch3-13-3} - Fonctions uniquement - La fonction peut-elle être exécutée en parallèle ? - `PARALLEL UNSAFE` (défaut) - `PARALLEL RESTRICTED` - `PARALLEL SAFE` `PARALLEL UNSAFE` indique que la fonction ne peut pas être exécutée dans le mode parallèle. La présence d’une fonction de ce type dans une requête SQL force un plan d’exécution en série. C’est la valeur par défaut. Une fonction est non parallélisable si elle modifie l’état d’une base ou si elle fait des changements sur la transaction. `PARALLEL RESTRICTED` indique que la fonction peut être exécutée en mode parallèle mais l’exécution est restreinte au processus principal d’exécution. Une fonction peut être déclarée comme restreinte si elle accède aux tables temporaires, à l’état de connexion des clients, aux curseurs, aux requêtes préparées. `PARALLEL SAFE` indique que la fonction s’exécute correctement dans le mode parallèle sans restriction. En général, si une fonction est marquée sûre ou restreinte à la parallélisation alors qu’elle ne l’est pas, elle pourrait renvoyer des erreurs ou fournir de mauvaises réponses lorsqu’elle est utilisée dans une requête parallèle. En cas de doute, les fonctions doivent être marquées comme `UNSAFE`, ce qui correspond à la valeur par défaut. ## Utilisation de fonctions dans les index {#dev42-ch3-14} - Fonctions uniquement ! - `IMMUTABLE | STABLE | VOLATILE` - Ce mode précise la « volatilité » de la fonction. - Permet de réduire le nombre d’appels - Index : fonctions immutables uniquement (sinon problèmes !) On peut indiquer à PostgreSQL le niveau de volatilité (ou de stabilité) d’une fonction. Ceci permet d’aider PostgreSQL à optimiser les requêtes utilisant ces fonctions, mais aussi d’interdire leur utilisation dans certains contextes. Une fonction est « **immutable** » si son exécution ne dépend que de ses paramètres. Elle ne doit donc dépendre ni du contenu de la base (pas de `SELECT`, ni de modification de donnée de quelque sorte), ni d’**aucun** autre élément qui ne soit pas un de ses paramètres. Les fonctions arithmétiques simples (`+`, `*`, `abs`…) sont immutables. À l’inverse, `now()` n’est évidemment pas immutable. Une fonction sélectionnant des données d’une table non plus. `to_char()` n’est pas non plus immutable, car son comportement dépend des paramètres de session, par exemple `to_char(timestamp with time zone, text)` dépend du paramètre de session `timezone`… Une fonction est « **stable** » si son exécution donne toujours le même résultat sur toute la durée d’un ordre SQL, pour les mêmes paramètres en entrée. Cela signifie que la fonction ne modifie pas les données de la base. Une fonction n’exécutant que des `SELECT` sur des tables (pas des fonctions !) sera stable. `to_char()` est stable. L’optimiseur peut réduire ainsi le nombre d’appels sans que ce soit en pratique toujours le cas. Une fonction est « **volatile** » dans tous les autres cas. `random()` est volatile. Une fonction volatile peut même modifier les donneés. Une fonction non déclarée comme stable ou immutable est volatile par défaut. La volatilité des fonctions intégrées à PostgreSQL est déjà définie. C’est au développeur de préciser la volatilité des fonctions qu’il écrit. Ce n’est pas forcément évident. Une erreur peut poser des problèmes quand le plan est mis en cache, ou, on le verra, dans des index. Quelle importance cela a-t-il ? Prenons une table d’exemple sur les heures de l’année 2020 : ``` sql -- Une ligne par heure dans l année, 8784 lignes CREATE TABLE heures AS SELECT i, '2020-01-01 00:00:00+01:00'::timestamptz + i * interval '1 hour' AS t FROM generate_series (1,366*24) i; ``` Définissons une fonction un peu naïve ramenant le premier jour du mois, volatile faute de mieux : ``` sql CREATE OR REPLACE FUNCTION premierjourdumois(t timestamptz) RETURNS timestamptz LANGUAGE plpgsql VOLATILE AS $$ BEGIN RAISE notice 'appel premierjourdumois' ; -- trace des appels RETURN date_trunc ('month', t); END $$ ; ``` Demandons juste le plan d’un appel ne portant que sur le dernier jour : ``` sql EXPLAIN SELECT * FROM heures WHERE t > premierjourdumois('2020-12-31 00:00:00+02:00'::timestamptz) LIMIT 10 ; ``` ``` default QUERY PLAN ------------------------------------------------------------------------- Limit (cost=0.00..8.04 rows=10 width=12) -> Seq Scan on heures (cost=0.00..2353.80 rows=2928 width=12) Filter: (t > premierjourdumois( '2020-12-30 23:00:00+01'::timestamp with time zone)) ``` Le nombre de lignes attendues (2928) est le tiers de la table, alors que nous ne demandons que le dernier mois. Il s’agit de l’estimation forfaitaire que PostgreSQL utilise faute d’informations sur ce que va retourner la fonction. Demander à voir le résultat mène à l’affichage de milliers de `NOTICE` : la fonction est appelée à chaque ligne pour calculer s’il faut filtrer la valeur. En effet, une fonction volatile sera systématiquement exécutée à chaque appel, et, selon le plan, ce peut être pour chaque ligne parcourue ! Cependant notre fonction ne fait que des calculs à partir du paramètre, sans effet de bord. Déclarons-la donc stable : ``` sql ALTER FUNCTION premierjourdumois(timestamp with time zone) STABLE ; ``` Une fonction stable peut en théorie être remplacée par son résultat pendant l’exécution de la requête. Mais c’est impossible de le faire plus tôt, car on ne sait pas forcément dans quel contexte la fonction va être appelée (par exemple, en cas de requête préparée, les paramètres de la session ou les données de la base peuvent même changer entre la planification et l’exécution). Dans notre cas, le même `EXPLAIN` simple mène à ceci : ``` default NOTICE: appel premierjourdumois QUERY PLAN ------------------------------------------------------------------------- Limit (cost=0.00..32.60 rows=10 width=12) -> Seq Scan on heures (cost=0.00..2347.50 rows=720 width=12) Filter: (t > premierjourdumois( '2020-12-30 23:00:00+01'::timestamp with time zone)) ``` Comme il s’agit d’un simple `EXPLAIN`, la requête n’est pas exécutée. Or le message `NOTICE` est renvoyé : la fonction est donc exécutée pour une simple planification. Un appel unique suffit, puisque la valeur d’une fonction stable ne change pas pendant toute la durée de la requête pour les mêmes paramètres (ici une constante). Cet appel permet d’affiner la volumétrie des valeurs attendues, ce qui peut avoir un impact énorme. Cependant, à l’exécution, les `NOTICE` apparaîtront pour indiquer que la fonction est à nouveau appelée à chaque ligne. Pour qu’un seul appel soit effectué pour toute la requête, il faudrait déclarer la fonction comme immutable, ce qui serait faux, puisqu’elle dépend implicitement du fuseau horaire. Dans l’idéal, une fonction immutable peut être remplacée par son résultat avant même la planification d’une requête l’utilisant. C’est le cas avec les calculs arithmétiques par exemple : ``` sql EXPLAIN SELECT * FROM heures WHERE i > abs(364*24) AND t > '2020-06-01'::date + interval '57 hours' ; ``` La valeur est substituée très tôt, ce qui permet de les comparer aux statistiques : ``` default Seq Scan on heures (cost=0.00..179.40 rows=13 width=12) Filter: ((i > 8736) AND (t > '2020-06-03 09:00:00'::timestamp without time zone)) ``` Pour forcer un appel unique quand on sait que la fonction renverra une constante, du moins le temps de la requête, même si elle est volatile, une astuce est de signifier à l’optimiseur qu’il n’y aura qu’une seule valeur de comparaison, même si on ne sait pas laquelle : ``` sql EXPLAIN (ANALYZE) SELECT * FROM heures WHERE t > (SELECT premierjourdumois('2020-12-31 00:00:00+02:00'::timestamptz)) ; ``` ``` default NOTICE: appel premierjourdumois QUERY PLAN -------------------------------------------------------------------------------- Seq Scan on heures (cost=0.26..157.76 rows=2920 width=12) (actual time=1.090..1.206 rows=721 loops=1) Filter: (t > $0) Rows Removed by Filter: 8039 InitPlan 1 (returns $0) -> Result (cost=0.00..0.26 rows=1 width=8) (actual time=0.138..0.139 rows=1 loops=1) Planning Time: 0.058 ms Execution Time: 1.328 ms ``` On note qu’il n’y a qu’un appel. On comprend donc l’intérêt de se poser la question à l’écriture de chaque fonction. La volatilité est encore plus importante quand il s’agit de créer des fonctions sur index : ``` sql CREATE INDEX ON heures (premierjourdumois( t )) ; ``` ``` default ERROR: functions in index expression must be marked IMMUTABLE ``` Ceci n’est possible que si la fonction est immutable. En effet, si le résultat de la fonction dépend de l’état de la base ou d’autres paramètres, la fonction exécutée au moment de la création de la clé d’index pourrait ne plus retourner le même résultat quand viendra le moment de l’interroger. PostgreSQL n’acceptera donc que les fonctions immutables dans la déclaration des index fonctionnels. > Déclarer hâtivement une fonction comme immutable juste pour pouvoir l’utiliser dans un index est dangereux : en cas d’erreur, les résultats d’une requête peuvent alors dépendre du plan d’exécution, selon que les index seront utilisés ou pas ! Cela est particulièrement fréquent quand les fuseaux horaires ou les dictionnaires sont impliqués. Vérifiez bien que vous n’utilisez que des fonctions immutables dans les index fonctionnels, les pièges sont nombreux. Par exemple, si l’on veut une version immutable de la fonction précédente, il faut fixer le fuseau horaire dans l’appel à `date_trunc`. En effet, on peut voir avec `df+ date_trunc` que la seule version immutable de `date_trunc` n’accepte que des `timestamp` (sans fuseau), et en renvoie un. Notre fonction devient donc : ``` sql CREATE OR REPLACE FUNCTION premierjourdumois_utc(t timestamptz) RETURNS timestamptz LANGUAGE plpgsql IMMUTABLE AS $$ DECLARE jour1 timestamp ; --sans TZ BEGIN jour1 := date_trunc ('month', (t at time zone 'UTC')::timestamp) ; RETURN jour1 AT TIME ZONE 'UTC'; END $$ ; ``` Testons avec une date dans les dernières heures de septembre en Alaska, qui correspond au tout début d’octobre en temps universel, et par exemple aussi au Japon : ``` default \x ``` ``` sql SET timezone TO 'US/Alaska'; SELECT d, d AT TIME ZONE 'UTC' AS d_en_utc, premierjourdumois_utc (d), premierjourdumois_utc (d) AT TIME ZONE 'UTC' as pjm_en_utc FROM (SELECT '2020-09-30 18:00:00-08'::timestamptz AS d) x; ``` ``` default -[ RECORD 1 ]---------+----------------------- d | 2020-09-30 18:00:00-08 d_en_utc | 2020-10-01 02:00:00 premierjourdumois_utc | 2020-09-30 16:00:00-08 pjm_en_utc | 2020-10-01 00:00:00 ``` ``` sql SET timezone TO 'Japan'; SELECT d, d AT TIME ZONE 'UTC' AS d_en_utc, premierjourdumois_utc (d), premierjourdumois_utc (d) AT TIME ZONE 'UTC' as pjm_en_utc FROM (SELECT '2020-09-30 18:00:00-08'::timestamptz AS d) x; ``` ``` default -[ RECORD 1 ]---------+----------------------- d | 2020-10-01 11:00:00+09 d_en_utc | 2020-10-01 02:00:00 premierjourdumois_utc | 2020-10-01 09:00:00+09 pjm_en_utc | 2020-10-01 00:00:00 ``` Malgré les différences d’affichage dues au fuseau horaire, c’est bien le même moment (la première seconde d’octobre en temps universel) qui est retourné par la fonction. Pour une fonction aussi simple, la version SQL est même préférable : ``` sql CREATE OR REPLACE FUNCTION premierjourdumois_utc(t timestamptz) RETURNS timestamptz LANGUAGE sql IMMUTABLE AS $$ SELECT (date_trunc ('month', (t at time zone 'UTC')::timestamp ) ) AT TIME ZONE 'UTC'; $$ ; ``` Enfin, la volatilité a également son importance lors d’autres opérations d’optimisation, comme l’exclusion de partitions. Seules les fonctions immutables sont compatibles avec le *partition pruning* effectué à la planification, mais les fonctions stable sont éligibles au *dynamic partition pruning* (à l’exécution) apparu avec PostgreSQL 11. ## Conclusion {#dev42-ch3-15} - Grand nombre de structure de contrôle (test, boucle, etc.) - Facile à utiliser et à comprendre ### Pour aller plus loin {#dev42-ch3-15-1} - Documentation officielle - « Chapitre 40. PL/pgSQL - Langage de procédures SQL » - Formation Dalibo : - variadic, routines polymorphes - triggers, tables de transition - curseurs - gestion des erreurs - sécurité - optimisation La documentation officielle sur le langage PL/pgSQL peut être consultée en français [à cette adresse](https://docs.postgresql.fr/current/plpgsql.html). ### Questions {#dev42-ch3-15-2} ``` sql FOR q IN (SELECT * FROM questions ) LOOP répondre (q) ; END LOOP ; ``` ## Quiz {#dev42-ch3-16} **** ## Travaux pratiques {#dev42-ch3-17} La version en ligne des solutions de ces TP est disponible sur . L’exercice sur les index fonctionnels utilise la base **magasin**. Si vous ne l’avez pas déjà, la base **magasin** (dump de 96 Mo, pour 667 Mo sur le disque au final) peut être téléchargée et restaurée comme suit dans une nouvelle base **magasin** : ``` bash createdb magasin curl -kL https://dali.bo/tp_magasin -o /tmp/magasin.dump pg_restore -d magasin /tmp/magasin.dump # le message sur public préexistant est normal rm -- /tmp/magasin.dump ``` Les données sont dans deux schémas, **magasin** et **facturation**. Penser au `search_path`. Pour ce TP, figer les paramètres suivants : ``` sql SET max_parallel_workers_per_gather to 0; SET seq_page_cost TO 1 ; SET random_page_cost TO 4 ; ``` ### Hello {#dev42-ch3-17-1} **But** : Premières fonctions > Écrire une fonction `hello()` qui renvoie la chaîne de caractère « Hello World! » en SQL. > Écrire une fonction `hello_pl()` qui renvoie la chaîne de caractère « Hello World! » en PL/pgSQL. > Comparer les coûts des deux plans d’exécutions de ces requêtes. Expliquer ces coûts. ### Division {#dev42-ch3-17-2} **But** : Fonction avec calcul simple > Écrire en PL/pgSQL une fonction de division appelée `division`. Elle acceptera en entrée deux arguments de type entier et renverra un nombre réel (`numeric`). > Écrire cette même fonction en SQL. > Comment corriger le problème de la division par zéro ? Écrire cette nouvelle fonction dans les deux langages. (Conseil : dans ce genre de calcul impossible, il est possible d’utiliser la constante `NaN` (*Not A Number*) ). ### SELECT sur des tables dans les fonctions {#dev42-ch3-17-3} **But** : Utiliser une table à l’intérieur d’une fonction Ce TP utilise les tables de la base **employes_services**. Si vous n’avez pas déjà la base **employes**, son script de création se télécharge et s’installe ainsi : ``` bash curl -kL https://dali.bo/tp_employes_services -o employes_services.sql createdb employes psql employes < employes_services.sql ``` Les quelques tables occupent environ 80 Mo sur le disque. > Créer une fonction qui ramène le nombre d’employés embauchés une année donnée (à partir du champ `employes.date_embauche`). > Utiliser la fonction `generate_series()` pour lister le nombre d’embauches pour chaque année entre 2000 et 2010. > Créer une fonction qui fait la même chose avec deux années en paramètres une boucle `FOR … LOOP`, `RETURNS TABLE` et `RETURN NEXT`. ### Multiplication {#dev42-ch3-17-4} **But** : Fonctions avec de nombreuses conditions, des manipulations de types, et un message. > Écrire une fonction de multiplication dont les arguments sont des chiffres en toute lettre, inférieurs ou égaux à « neuf ». Par exemple, `multiplication ('deux','trois')` doit renvoyer 6. > Si ce n’est déjà fait, faire en sorte que `multiplication` appelle une autre fonction pour faire la conversion de texte en chiffre, et n’effectue que le calcul. > Essayer de multiplier « deux » par 4. Qu’obtient-on et pourquoi ? > Corriger la fonction pour tomber en erreur si un argument est numérique (utiliser `RAISE EXCEPTION `). ### Salutations {#dev42-ch3-17-5} **But** : Fonction plus complexe > Écrire une fonction en PL/pgSQL qui prend en argument le nom de l’utilisateur, puis lui dit « Bonjour » ou « Bonsoir » suivant l’heure de la journée. Utiliser la fonction `to_char()`. > Écrire la même fonction avec un paramètre `OUT`. > Pour calculer l’heure courante, utiliser plutôt la fonction `extract`. > Réécrire la fonction en SQL. ### Inversion de chaîne {#dev42-ch3-17-6} **But** : Manipuler des chaînes > Écrire une fonction `inverser` qui inverse une chaîne (pour « toto » en entrée, afficher « otot » en sortie), à l’aide d’une boucle `WHILE` et des fonctions `char_length` et `substring`. ### Jours fériés {#dev42-ch3-17-7} **But** : Calculs complexes avec des dates Le calcul de la date de Pâques est [complexe](https://fr.wikipedia.org/wiki/Calcul_de_la_date_de_P%C3%A2ques). On peut écrire la fonction suivante : ``` sql CREATE OR REPLACE FUNCTION paques (annee integer) RETURNS date AS $$ DECLARE a integer ; b integer ; r date ; BEGIN a := (19*(annee % 19) + 24) % 30 ; b := (2*(annee % 4) + 4*(annee % 7) + 6*a + 5) % 7 ; SELECT (annee::text||'-03-31')::date + (a+b-9) INTO r ; RETURN r ; END ; $$ LANGUAGE plpgsql ; ``` **Principe** : Soit `m` l’année. On calcule successivement : - le reste de `m/19` : c’est la valeur de `a`. - le reste de `m/4` : c’est la valeur de `b`. - le reste de `m/7` : c’est la valeur de `c`. - le reste de `(19a + p)/30` : c’est la valeur de `d`. - le reste de `(2b + 4c + 6d + q)/7` : c’est la valeur de `e`. Les valeurs de `p` et de `q` varient de 100 ans en 100 ans. De 2000 à 2100, `p` vaut 24, `q` vaut 5. La date de Pâques est le `(22 + d + e)` mars ou le `(d + e - 9)` avril. > Afficher les dates de Pâques de 2018 à 2025. > Écrire une fonction qui calcule la date de l’Ascension, soit le jeudi de la sixième semaine après Pâques. Pour simplifier, on peut aussi considérer que l’Ascension se déroule 39 jours après Pâques. > Pour écrire une fonction qui renvoie tous les jours fériés d’une année (libellé et date), en France métropolitaine : > > - Prévoir un paramètre supplémentaire pour l’Alsace-Moselle, où le Vendredi saint (précédant le dimanche de Pâques) et le 26 décembre sont aussi fériés (ou toute autre variation régionale). > - Cette fonction doit renvoyer plusieurs lignes : utiliser `RETURN NEXT`. > - Plusieurs variantes sont possibles : avec `SETOF record`, avec des paramètres `OUT`, ou avec `RETURNS TABLE (libelle, jour)`. > - Enfin, il est possible d’utiliser `RETURN QUERY`. ### Index fonctionnels {#dev42-ch3-17-8} **But** : Cas d’usage d’un index fonctionnel Pour répondre aux exigences de stockage, l’application a besoin de pouvoir trouver rapidement les produits dont le volume est compris entre certaines bornes (nous négligeons ici le facteur de forme, qui est problématique dans le cadre d’un véritable stockage en entrepôt !). > Écrire une requête permettant de renvoyer l’ensemble des produits (table `magasin.produits`) dont le volume ne dépasse pas 1 litre (les unités de longueur sont en mm, 1 litre = 1 000 000 mm³). > Quel index permet d’optimiser cette requête ? (Utiliser une fonction est possible, mais pas obligatoire.) ## Travaux pratiques (solutions) {#dev42-ch3-18} ### Hello {#dev42-ch3-18-1} > Écrire une fonction `hello()` qui renvoie la chaîne de caractère « Hello World! » en SQL. ``` sql CREATE OR REPLACE FUNCTION hello() RETURNS text AS $BODY$ SELECT 'hello world !'::text; $BODY$ LANGUAGE SQL; ``` > Écrire une fonction `hello_pl()` qui renvoie la chaîne de caractère « Hello World! » en PL/pgSQL. ``` sql CREATE OR REPLACE FUNCTION hello_pl() RETURNS text AS $BODY$ BEGIN RETURN 'hello world !'; END $BODY$ LANGUAGE plpgsql; ``` > Comparer les coûts des deux plans d’exécutions de ces requêtes. Expliquer ces coûts. Requêtage : ``` sql EXPLAIN SELECT hello(); ``` ``` default QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=32) ``` ``` sql EXPLAIN SELECT hello_pl(); ``` ``` default QUERY PLAN ------------------------------------------ Result (cost=0.00..0.26 rows=1 width=32) ``` Par défaut, si on ne précise pas le coût (`COST`) d’une fonction, cette dernière a un coût par défaut de 100. Ce coût est à multiplier par la valeur du paramètre `cpu_operator_cost`, par défaut à 0,0025. Le coût total d’appel de la fonction `hello_pl` est donc par défaut de : 100\*cpu_operator_cost + cpu_tuple_cost Ce n’est pas valable pour la fonction en SQL pur, qui est ici intégrée à la requête. ### Division {#dev42-ch3-18-2} > Écrire en PL/pgSQL une fonction de division appelée `division`. Elle acceptera en entrée deux arguments de type entier et renverra un nombre réel (`numeric`). Attention, sous PostgreSQL, la division de deux entiers est par défaut entière : il faut donc transtyper. ``` sql CREATE OR REPLACE FUNCTION division (arg1 integer, arg2 integer) RETURNS numeric AS $BODY$ BEGIN RETURN arg1::numeric / arg2::numeric; END $BODY$ LANGUAGE plpgsql; ``` ``` sql SELECT division (3,2) ; ``` ``` default division -------------------- 1.5000000000000000 ``` > Écrire cette même fonction en SQL. ``` sql CREATE OR REPLACE FUNCTION division_sql (a integer, b integer) RETURNS numeric AS $$ SELECT a::numeric / b::numeric; $$ LANGUAGE SQL; ``` > Comment corriger le problème de la division par zéro ? Écrire cette nouvelle fonction dans les deux langages. (Conseil : dans ce genre de calcul impossible, il est possible d’utiliser la constante `NaN` (*Not A Number*) ). Le problème se présente ainsi : ``` sql SELECT division(1,0); ``` ``` default ERROR: division by zero CONTEXTE : PL/pgSQL function division(integer,integer) line 3 at RETURN ``` Pour la version en PL : ``` sql CREATE OR REPLACE FUNCTION division(arg1 integer, arg2 integer) RETURNS numeric AS $BODY$ BEGIN IF arg2 = 0 THEN RETURN 'NaN'; ELSE RETURN arg1::numeric / arg2::numeric; END IF; END $BODY$ LANGUAGE plpgsql; ``` ``` sql SELECT division (3,0) ; ``` ``` default division ---------- NaN ``` Pour la version en SQL : ``` sql CREATE OR REPLACE FUNCTION division_sql(a integer, b integer) RETURNS numeric AS $$ SELECT CASE $2 WHEN 0 THEN 'NaN' ELSE $1::numeric / $2::numeric END; $$ LANGUAGE SQL; ``` ### SELECT sur des tables dans les fonctions {#dev42-ch3-18-3} Ce TP utilise les tables de la base **employes_services**. Si vous n’avez pas déjà la base **employes**, son script de création se télécharge et s’installe ainsi : ``` bash curl -kL https://dali.bo/tp_employes_services -o employes_services.sql createdb employes psql employes < employes_services.sql ``` Les quelques tables occupent environ 80 Mo sur le disque. > Créer une fonction qui ramène le nombre d’employés embauchés une année donnée (à partir du champ `employes.date_embauche`). ``` sql CREATE OR REPLACE FUNCTION nb_embauches (v_annee integer) RETURNS integer AS $BODY$ DECLARE nb integer; BEGIN SELECT count(*) INTO nb FROM employes WHERE extract (year from date_embauche) = v_annee ; RETURN nb; END $BODY$ LANGUAGE plpgsql ; ``` Test : ``` sql SELECT nb_embauches (2006); ``` ``` default nb_embauches -------------- 9 ``` > Utiliser la fonction `generate_series()` pour lister le nombre d’embauches pour chaque année entre 2000 et 2010. ``` sql SELECT n, nb_embauches (n) FROM generate_series (2000,2010) n ORDER BY n; ``` ``` default n | nb_embauches ------+-------------- 2000 | 2 2001 | 0 2002 | 0 2003 | 1 2004 | 0 2005 | 2 2006 | 9 2007 | 0 2008 | 0 2009 | 0 2010 | 0 ``` > Créer une fonction qui fait la même chose avec deux années en paramètres une boucle `FOR … LOOP`, `RETURNS TABLE` et `RETURN NEXT`. ``` sql CREATE OR REPLACE FUNCTION nb_embauches (v_anneedeb int, v_anneefin int) RETURNS TABLE (annee int, nombre_embauches int) AS $BODY$ BEGIN FOR i in v_anneedeb..v_anneefin LOOP SELECT i, nb_embauches (i) INTO annee, nombre_embauches ; RETURN NEXT ; END LOOP; RETURN; END $BODY$ LANGUAGE plpgsql; ``` Le nom de la fonction a été choisi identique à la précédente, mais avec des paramètres différents. Cela ne gêne pas le requêtage : ``` sql SELECT * FROM nb_embauches (2006,2010); ``` ``` default annee | nombre_embauches -------+------------------ 2006 | 9 2007 | 0 2008 | 0 2009 | 0 2010 | 0 ``` ### Multiplication {#dev42-ch3-18-4} > Écrire une fonction de multiplication dont les arguments sont des chiffres en toute lettre, inférieurs ou égaux à « neuf ». Par exemple, `multiplication ('deux','trois')` doit renvoyer 6. ``` sql CREATE OR REPLACE FUNCTION multiplication (arg1 text, arg2 text) RETURNS integer AS $BODY$ DECLARE a1 integer; a2 integer; BEGIN IF arg1 = 'zéro' THEN a1 := 0; ELSEIF arg1 = 'un' THEN a1 := 1; ELSEIF arg1 = 'deux' THEN a1 := 2; ELSEIF arg1 = 'trois' THEN a1 := 3; ELSEIF arg1 = 'quatre' THEN a1 := 4; ELSEIF arg1 = 'cinq' THEN a1 := 5; ELSEIF arg1 = 'six' THEN a1 := 6; ELSEIF arg1 = 'sept' THEN a1 := 7; ELSEIF arg1 = 'huit' THEN a1 := 8; ELSEIF arg1 = 'neuf' THEN a1 := 9; END IF; IF arg2 = 'zéro' THEN a2 := 0; ELSEIF arg2 = 'un' THEN a2 := 1; ELSEIF arg2 = 'deux' THEN a2 := 2; ELSEIF arg2 = 'trois' THEN a2 := 3; ELSEIF arg2 = 'quatre' THEN a2 := 4; ELSEIF arg2 = 'cinq' THEN a2 := 5; ELSEIF arg2 = 'six' THEN a2 := 6; ELSEIF arg2 = 'sept' THEN a2 := 7; ELSEIF arg2 = 'huit' THEN a2 := 8; ELSEIF arg2 = 'neuf' THEN a2 := 9; END IF; RETURN a1*a2; END $BODY$ LANGUAGE plpgsql; ``` Test : ``` sql SELECT multiplication('deux', 'trois'); ``` ``` default multiplication ---------------- 6 ``` ``` sql SELECT multiplication('deux', 'quatre'); ``` ``` default multiplication ---------------- 8 ``` > Si ce n’est déjà fait, faire en sorte que `multiplication` appelle une autre fonction pour faire la conversion de texte en chiffre, et n’effectue que le calcul. ``` sql CREATE OR REPLACE FUNCTION texte_vers_entier(arg text) RETURNS integer AS $BODY$ DECLARE ret integer; BEGIN IF arg = 'zéro' THEN ret := 0; ELSEIF arg = 'un' THEN ret := 1; ELSEIF arg = 'deux' THEN ret := 2; ELSEIF arg = 'trois' THEN ret := 3; ELSEIF arg = 'quatre' THEN ret := 4; ELSEIF arg = 'cinq' THEN ret := 5; ELSEIF arg = 'six' THEN ret := 6; ELSEIF arg = 'sept' THEN ret := 7; ELSEIF arg = 'huit' THEN ret := 8; ELSEIF arg = 'neuf' THEN ret := 9; END IF; RETURN ret; END $BODY$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION multiplication(arg1 text, arg2 text) RETURNS integer AS $BODY$ DECLARE a1 integer; a2 integer; BEGIN a1 := texte_vers_entier(arg1); a2 := texte_vers_entier(arg2); RETURN a1*a2; END $BODY$ LANGUAGE plpgsql; ``` > Essayer de multiplier « deux » par 4. Qu’obtient-on et pourquoi ? ``` sql SELECT multiplication('deux', 4::text); ``` ``` default multiplication ---------------- ``` Par défaut, les variables internes à la fonction valent NULL. Rien n’est prévu pour affecter le second argument, on obtient donc NULL en résultat. > Corriger la fonction pour tomber en erreur si un argument est numérique (utiliser `RAISE EXCEPTION `). ``` sql CREATE OR REPLACE FUNCTION texte_vers_entier(arg text) RETURNS integer AS $BODY$ DECLARE ret integer; BEGIN IF arg = 'zéro' THEN ret := 0; ELSEIF arg = 'un' THEN ret := 1; ELSEIF arg = 'deux' THEN ret := 2; ELSEIF arg = 'trois' THEN ret := 3; ELSEIF arg = 'quatre' THEN ret := 4; ELSEIF arg = 'cinq' THEN ret := 5; ELSEIF arg = 'six' THEN ret := 6; ELSEIF arg = 'sept' THEN ret := 7; ELSEIF arg = 'huit' THEN ret := 8; ELSEIF arg = 'neuf' THEN ret := 9; ELSE RAISE EXCEPTION 'argument "%" invalide', arg; ret := NULL; END IF; RETURN ret; END $BODY$ LANGUAGE plpgsql; ``` ``` sql SELECT multiplication('deux', 4::text); ``` ``` default ERROR: argument "4" invalide CONTEXTE : PL/pgSQL function texte_vers_entier(text) line 26 at RAISE PL/pgSQL function multiplication(text,text) line 7 at assignment ``` ### Salutations {#dev42-ch3-18-5} > Écrire une fonction en PL/pgSQL qui prend en argument le nom de l’utilisateur, puis lui dit « Bonjour » ou « Bonsoir » suivant l’heure de la journée. Utiliser la fonction `to_char()`. ``` sql CREATE OR REPLACE FUNCTION salutation(utilisateur text) RETURNS text AS $BODY$ DECLARE heure integer; libelle text; BEGIN heure := to_char(now(), 'HH24'); IF heure > 12 THEN libelle := 'Bonsoir'; ELSE libelle := 'Bonjour'; END IF; RETURN libelle||' '||utilisateur||' !'; END $BODY$ LANGUAGE plpgsql; ``` Test : ``` sql SELECT salutation ('Guillaume'); ``` ``` default salutation --------------------- Bonsoir Guillaume ! ``` > Écrire la même fonction avec un paramètre `OUT`. ``` sql CREATE OR REPLACE FUNCTION salutation(IN utilisateur text, OUT message text) AS $BODY$ DECLARE heure integer; libelle text; BEGIN heure := to_char(now(), 'HH24'); IF heure > 12 THEN libelle := 'Bonsoir'; ELSE libelle := 'Bonjour'; END IF; message := libelle||' '||utilisateur||' !'; END $BODY$ LANGUAGE plpgsql; ``` Elle s’utilise de la même manière : ``` sql SELECT salutation ('Guillaume'); ``` ``` default salutation --------------------- Bonsoir Guillaume ! ``` > Pour calculer l’heure courante, utiliser plutôt la fonction `extract`. ``` sql CREATE OR REPLACE FUNCTION salutation(IN utilisateur text, OUT message text) AS $BODY$ DECLARE heure integer; libelle text; BEGIN SELECT INTO heure extract(hour from now())::int; IF heure > 12 THEN libelle := 'Bonsoir'; ELSE libelle := 'Bonjour'; END IF; message := libelle||' '||utilisateur||' !'; END $BODY$ LANGUAGE plpgsql; ``` > Réécrire la fonction en SQL. Le `CASE … WHEN` remplace aisément un `IF … THEN` : ``` sql CREATE OR REPLACE FUNCTION salutation_sql(nom text) RETURNS text AS $$ SELECT CASE extract(hour from now()) > 12 WHEN 't' THEN 'Bonsoir '|| nom ELSE 'Bonjour '|| nom END::text; $$ LANGUAGE SQL; ``` ### Inversion de chaîne {#dev42-ch3-18-6} > Écrire une fonction `inverser` qui inverse une chaîne (pour « toto » en entrée, afficher « otot » en sortie), à l’aide d’une boucle `WHILE` et des fonctions `char_length` et `substring`. ``` sql CREATE OR REPLACE FUNCTION inverser(str_in varchar) RETURNS varchar AS $$ DECLARE str_out varchar ; -- à renvoyer position integer ; BEGIN -- Initialisation de str_out, sinon sa valeur reste à NULL str_out := ''; -- Position initialisée ç la longueur de la chaîne position := char_length(str_in); -- La chaîne est traitée ç l'envers -- Boucle: Inverse l'ordre des caractères d'une chaîne de caractères WHILE position > 0 LOOP -- la chaîne donnée en argument est parcourue -- à l'envers, -- et les caractères sont extraits individuellement str_out := str_out || substring(str_in, position, 1); position := position - 1; END LOOP; RETURN str_out; END; $$ LANGUAGE plpgsql; ``` ``` sql SELECT inverser (' toto ') ; ``` ``` default inverser ---------- otot ``` ### Jours fériés {#dev42-ch3-18-7} La fonction suivante calcule la date de Pâques d’une année : ``` sql CREATE OR REPLACE FUNCTION paques (annee integer) RETURNS date AS $$ DECLARE a integer ; b integer ; r date ; BEGIN a := (19*(annee % 19) + 24) % 30 ; b := (2*(annee % 4) + 4*(annee % 7) + 6*a + 5) % 7 ; SELECT (annee::text||'-03-31')::date + (a+b-9) INTO r ; RETURN r ; END ; $$ LANGUAGE plpgsql ; ``` > Afficher les dates de Pâques de 2018 à 2025. ``` sql SELECT paques (n) FROM generate_series (2018, 2025) n ; ``` ``` default paques ------------ 2018-04-01 2019-04-21 2020-04-12 2021-04-04 2022-04-17 2023-04-09 2024-03-31 2025-04-20 ``` > Écrire une fonction qui calcule la date de l’Ascension, soit le jeudi de la sixième semaine après Pâques. Pour simplifier, on peut aussi considérer que l’Ascension se déroule 39 jours après Pâques. Version complexe : ``` sql CREATE OR REPLACE FUNCTION ascension(annee integer) RETURNS date AS $$ DECLARE r date; BEGIN SELECT paques(annee)::date + 40 INTO r; SELECT r + (4 - extract(dow from r))::integer INTO r; RETURN r; END; $$ LANGUAGE plpgsql; ``` Version simple : ``` sql CREATE OR REPLACE FUNCTION ascension(annee integer) RETURNS date AS $$ SELECT (paques (annee) + INTERVAL '39 days')::date ; $$ LANGUAGE sql; ``` Test : ``` sql SELECT paques (n), ascension(n) FROM generate_series (2018, 2025) n ; ``` ``` default paques | ascension ------------+------------ 2018-04-01 | 2018-05-10 2019-04-21 | 2019-05-30 2020-04-12 | 2020-05-21 2021-04-04 | 2021-05-13 2022-04-17 | 2022-05-26 2023-04-09 | 2023-05-18 2024-03-31 | 2024-05-09 2025-04-20 | 2025-05-29 ``` > Pour écrire une fonction qui renvoie tous les jours fériés d’une année (libellé et date), en France métropolitaine : > > - Prévoir un paramètre supplémentaire pour l’Alsace-Moselle, où le Vendredi saint (précédant le dimanche de Pâques) et le 26 décembre sont aussi fériés (ou toute autre variation régionale). > - Cette fonction doit renvoyer plusieurs lignes : utiliser `RETURN NEXT`. > - Plusieurs variantes sont possibles : avec `SETOF record`, avec des paramètres `OUT`, ou avec `RETURNS TABLE (libelle, jour)`. > - Enfin, il est possible d’utiliser `RETURN QUERY`. **Version avec SETOF record** : ``` sql CREATE OR REPLACE FUNCTION vacances ( annee integer, alsace_moselle boolean DEFAULT false ) RETURNS SETOF record AS $$ DECLARE f integer; r record; BEGIN SELECT 'Jour de l''an'::text, (annee::text||'-01-01')::date INTO r; RETURN NEXT r; SELECT 'Pâques'::text, paques(annee)::date + 1 INTO r; RETURN NEXT r; SELECT 'Ascension'::text, ascension(annee)::date INTO r; RETURN NEXT r; SELECT 'Fête du travail'::text, (annee::text||'-05-01')::date INTO r; RETURN NEXT r; SELECT 'Victoire 1945'::text, (annee::text||'-05-08')::date INTO r; RETURN NEXT r; SELECT 'Fête nationale'::text, (annee::text||'-07-14')::date INTO r; RETURN NEXT r; SELECT 'Assomption'::text, (annee::text||'-08-15')::date INTO r; RETURN NEXT r; SELECT 'La toussaint'::text, (annee::text||'-11-01')::date INTO r; RETURN NEXT r; SELECT 'Armistice 1918'::text, (annee::text||'-11-11')::date INTO r; RETURN NEXT r; SELECT 'Noël'::text, (annee::text||'-12-25')::date INTO r; RETURN NEXT r; IF alsace_moselle THEN SELECT 'Vendredi saint'::text, paques(annee)::date - 2 INTO r; RETURN NEXT r; SELECT 'Lendemain de Noël'::text, (annee::text||'-12-26')::date INTO r; RETURN NEXT r; END IF; RETURN; END; $$ LANGUAGE plpgsql; ``` Le requêtage implique de nommer les colonnes : ``` sql SELECT * FROM vacances(2020, true) AS (libelle text, jour date) ORDER BY jour ; ``` ``` default libelle | jour --------------------+------------ Jour de l'an | 2020-01-01 Vendredi saint | 2020-04-10 Pâques | 2020-04-13 Fête du travail | 2020-05-01 Victoire 1945 | 2020-05-08 Ascension | 2020-05-21 Fête nationale | 2020-07-14 Assomption | 2020-08-15 La toussaint | 2020-11-01 Armistice 1918 | 2020-11-11 Noël | 2020-12-25 Lendemain de Noël | 2020-12-26 ``` **Version avec paramètres OUT** : Une autre forme d’écriture possible consiste à indiquer les deux colonnes de retour comme des paramètres `OUT` : ``` sql CREATE OR REPLACE FUNCTION vacances( annee integer, alsace_moselle boolean DEFAULT false, OUT libelle text, OUT jour date) RETURNS SETOF record LANGUAGE plpgsql AS $function$ DECLARE f integer; r record; BEGIN SELECT 'Jour de l''an'::text, (annee::text||'-01-01')::date INTO libelle, jour; RETURN NEXT; SELECT 'Pâques'::text, paques(annee)::date + 1 INTO libelle, jour; RETURN NEXT; SELECT 'Ascension'::text, ascension(annee)::date INTO libelle, jour; RETURN NEXT; SELECT 'Fête du travail'::text, (annee::text||'-05-01')::date INTO libelle, jour; RETURN NEXT; SELECT 'Victoire 1945'::text, (annee::text||'-05-08')::date INTO libelle, jour; RETURN NEXT; SELECT 'Fête nationale'::text, (annee::text||'-07-14')::date INTO libelle, jour; RETURN NEXT; SELECT 'Assomption'::text, (annee::text||'-08-15')::date INTO libelle, jour; RETURN NEXT; SELECT 'La toussaint'::text, (annee::text||'-11-01')::date INTO libelle, jour; RETURN NEXT; SELECT 'Armistice 1918'::text, (annee::text||'-11-11')::date INTO libelle, jour; RETURN NEXT; SELECT 'Noël'::text, (annee::text||'-12-25')::date INTO libelle, jour; RETURN NEXT; IF alsace_moselle THEN SELECT 'Vendredi saint'::text, paques(annee)::date - 2 INTO libelle, jour; RETURN NEXT; SELECT 'Lendemain de Noël'::text, (annee::text||'-12-26')::date INTO libelle, jour; RETURN NEXT; END IF; RETURN; END; $function$; ``` La fonction s’utilise alors de façon simple : ``` sql SELECT * FROM vacances(2020) ORDER BY jour ; ``` ``` default libelle | jour -----------------+------------ Jour de l'an | 2020-01-01 Pâques | 2020-04-13 Fête du travail | 2020-05-01 Victoire 1945 | 2020-05-08 Ascension | 2020-05-21 Fête nationale | 2020-07-14 Assomption | 2020-08-15 La toussaint | 2020-11-01 Armistice 1918 | 2020-11-11 Noël | 2020-12-25 ``` **Version avec `RETURNS TABLE`** : Seule la déclaration en début diffère de la version avec les paramètres `OUT` : ``` sql CREATE OR REPLACE FUNCTION vacances( annee integer,alsace_moselle boolean DEFAULT false) RETURNS TABLE (libelle text, jour date) LANGUAGE plpgsql AS $function$ … ``` L’utilisation est aussi simple que la version précédente. **Version avec RETURN QUERY** : C’est peut-être la version la plus compacte : ``` sql CREATE OR REPLACE FUNCTION vacances(annee integer,alsace_moselle boolean DEFAULT false) RETURNS TABLE (libelle text, jour date) LANGUAGE plpgsql AS $function$ BEGIN RETURN QUERY SELECT 'Jour de l''an'::text, (annee::text||'-01-01')::date ; RETURN QUERY SELECT 'Pâques'::text, paques(annee)::date + 1 ; RETURN QUERY SELECT 'Ascension'::text, ascension(annee)::date ; RETURN QUERY SELECT 'Fête du travail'::text, (annee::text||'-05-01')::date ; RETURN QUERY SELECT 'Victoire 1945'::text, (annee::text||'-05-08')::date ; RETURN QUERY SELECT 'Fête nationale'::text, (annee::text||'-07-14')::date ; RETURN QUERY SELECT 'Assomption'::text, (annee::text||'-08-15')::date ; RETURN QUERY SELECT 'La toussaint'::text, (annee::text||'-11-01')::date ; RETURN QUERY SELECT 'Armistice 1918'::text, (annee::text||'-11-11')::date ; RETURN QUERY SELECT 'Noël'::text, (annee::text||'-12-25')::date ; IF alsace_moselle THEN RETURN QUERY SELECT 'Vendredi saint'::text, paques(annee)::date - 2 ; RETURN QUERY SELECT 'Lendemain de Noël'::text, (annee::text||'-12-26')::date ; END IF; RETURN; END; $function$; ``` ### Index fonctionnel {#dev42-ch3-18-8} Ce TP utilise la base **magasin**. > Écrire une requête permettant de renvoyer l’ensemble des produits (table `magasin.produits`) dont le volume ne dépasse pas 1 litre (les unités de longueur sont en mm, 1 litre = 1 000 000 mm³). Concernant le volume des produits, la requête est assez simple : ``` sql SELECT * FROM produits WHERE longueur * hauteur * largeur < 1000000 ; ``` > Quel index permet d’optimiser cette requête ? (Utiliser une fonction est possible, mais pas obligatoire.) L’option la plus simple est de créer l’index de cette façon, sans avoir besoin d’une fonction : ``` sql CREATE INDEX ON produits((longueur * hauteur * largeur)); ``` En général, il est plus propre de créer une fonction. On peut passer la ligne entière en paramètre pour éviter de fournir 3 paramètres. Il faut que cette fonction soit `IMMUTABLE` pour être indexable : ``` sql CREATE OR REPLACE function volume (p produits) RETURNS numeric AS $$ SELECT p.longueur * p.hauteur * p.largeur; $$ language SQL PARALLEL SAFE IMMUTABLE ; ``` (Elle est même `PARALLEL SAFE` pour la même raison qu’elle est `IMMUTABLE` : elle dépend uniquement des données de la table.) On peut ensuite indexer le résultat de cette fonction : ``` sql CREATE INDEX ON produits (volume(produits)) ; ``` Il est ensuite possible d’écrire la requête de plusieurs manières, la fonction étant ici écrite en SQL et non en PL/pgSQL ou autre langage procédural : ``` sql SELECT * FROM produits WHERE longueur * hauteur * largeur < 1000000 ; SELECT * FROM produits WHERE volume(produits) < 1000000 ; ``` En effet, l’optimiseur est capable de « regarder » à l’intérieur de la fonction SQL pour déterminer que les clauses sont les mêmes, ce qui n’est pas vrai pour les autres langages. En revanche, la requête suivante, où la multiplication est faite dans un ordre différent, n’utilise pas l’index : ``` sql SELECT * FROM produits WHERE largeur * longueur * hauteur < 1000000 ; ``` et c’est notamment pour cette raison qu’il est plus propre d’utiliser la fonction. De part l’origine « relationnel-objet » de PostgreSQL, on peut même écrire la requête de la manière suivante : ``` sql SELECT * FROM produits WHERE produits.volume < 1000000; ``` ## Navigation Pour revenir au chapitre précédent, voir [le chapitre précédent](/fr/dev42/02-types-avances/#dev42-ch2). Pour poursuivre la lecture, voir [le chapitre suivant](/fr/dev42/04-plpgsql-avance/#dev42-ch4).