P1.handout PG SQL
P1.handout PG SQL
P1.handout PG SQL
23.01
Dalibo SCOP
https://dalibo.com/formations
Module P1
TITRE : PL/pgSQL : Les bases
SOUS‐TITRE : Module P1
REVISION: 23.01
DATE: 27 janvier 2023
COPYRIGHT: © 2005‐2023 DALIBO SARL SCOP
LICENCE: Creative Commons BY‐NC‐SA
Postgres®, PostgreSQL® and the Slonik Logo are trademarks or registered trademarks
of the PostgreSQL Community Association of Canada, and used with their permission.
(Les noms PostgreSQL® et Postgres®, et le logo Slonik sont des marques déposées par
PostgreSQL Community Association of Canada.
Voir https://www.postgresql.org/about/policies/trademarks/
Dalibo ne peut retirer les autorisations concédées par la licence tant que vous appliquez
les termes de cette licence selon les conditions suivantes :
Attribution : Vous devez créditer l’œuvre, intégrer un lien vers la licence et indiquer si des
modifications ont été effectuées à l’œuvre. Vous devez indiquer ces informations par tous
les moyens raisonnables, sans toutefois suggérer que Dalibo vous soutient ou soutient la
façon dont vous avez utilisé ce document.
Pas d’Utilisation Commerciale : Vous n’êtes pas autorisé à faire un usage commercial de ce
document, tout ou partie du matériel le composant.
Partage dans les Mêmes Conditions : Dans le cas où vous effectuez un remix, que vous
transformez, ou créez à partir du matériel composant le document original, vous devez
diffuser le document modifié dans les même conditions, c’est à dire avec la même licence
avec laquelle le document original a été diffusé.
Pas de restrictions complémentaires : Vous n’êtes pas autorisé à appliquer des conditions
légales ou des mesures techniques qui restreindraient légalement autrui à utiliser le doc‐
ument dans les conditions décrites par la licence.
Note : Ceci est un résumé de la licence. Le texte complet est disponible ici :
https://creativecommons.org/licenses/by‐nc‐sa/2.0/fr/legalcode
Pour toute demande au sujet des conditions d’utilisation de ce document, envoyez vos
questions à [email protected] !
1
mailto:[email protected]
Chers lectrices & lecteurs,
Au‐delà du contenu technique en lui‐même, notre intention est de transmettre les valeurs
qui animent et unissent les développeurs de PostgreSQL depuis toujours : partage, ou‐
verture, transparence, créativité, dynamisme... Le but premier de nos formations est de
vous aider à mieux exploiter toute la puissance de PostgreSQL mais nous espérons égale‐
ment qu'elles vous inciteront à devenir un membre actif de la communauté en partageant
à votre tour le savoir‐faire que vous aurez acquis avec nous.
Nous mettons un point d'honneur à maintenir nos manuels à jour, avec des informations
précises et des exemples détaillés. Toutefois malgré nos efforts et nos multiples relec‐
tures, il est probable que ce document contienne des oublis, des coquilles, des impréci‐
sions ou des erreurs. Si vous constatez un souci, n'hésitez pas à le signaler via l'adresse
[email protected] !
Table des Matières
Licence Creative Commons BY‐NC‐SA 2.0 FR 5
9
PL/pgSQL : Les bases
1.1 PRÉAMBULE
• Vous apprendrez :
– à choisir si vous voulez écrire du PL
– à choisir votre langage PL
– les principes généraux des langages PL autres que PL/pgSQL
– les bases de PL/pgSQL
stockées et les différents langages disponibles. Puis il aborde les bases du langage
PL/pgSQL, autrement dit :
1.1.1 AU MENU
1.1.2 OBJECTIFS
1.2 INTRODUCTION
• PL = Procedural Language
• 3 langages activés par défaut :
– C
– SQL
– PL/pgSQL
• PL/PgSQL2 est intégré par défaut dans toute nouvelle base (de par sa présence
dans la base modèle template1) ;
• PL/Tcl3 (existe en version trusted et untrusted) ;
• PL/Perl4 (existe en version trusted et untrusted) ;
• PL/Python5 (uniquement en version untrusted).
D'autres langages PL sont accessibles en tant qu'extensions tierces. Les plus stables sont
mentionnés dans la documentation6 , comme PL/Java7 ou PL/R8 . Ils réclament générale‐
ment d'installer les bibliothèques du langage sur le serveur.
Une liste plus large est par ailleurs disponible sur le wiki PostgreSQL9 , Il en ressort qu'au
moins 16 langages sont disponibles, dont 10 installables en production. De plus, il est
possible d'en ajouter d'autres, comme décrit dans la documentation10 .
2
https://docs.postgresql.fr/current/plpgsql.html
3
https://docs.postgresql.fr/current/pltcl.html
4
https://docs.postgresql.fr/current/plperl.html
5
https://docs.postgresql.fr/current/plpython.html
6
https://docs.postgresql.fr/current/external‐pl.html
7
https://tada.github.io/pljava/
8
https://github.com/postgres‐plr/plr
9
https://wiki.postgresql.org/wiki/PL_Matrix
10
https://docs.postgresql.fr/current/plhandler.html
12
1.2 Introduction
Les langages de confiance ne peuvent accéder qu'à la base de données. Ils ne peuvent pas
accéder aux autres bases, aux systèmes de fichiers, au réseau, etc. Ils sont donc confinés,
ce qui les rend moins facilement utilisables pour compromettre le système. PL/pgSQL est
l'exemple typique. Mais de ce fait, ils offrent moins de possibilités que les autres langages.
Seuls les superutilisateurs peuvent créer une routine dans un langage untrusted. Par con‐
tre, ils peuvent ensuite donner les droits d'exécution à ces routines aux autres rôles dans
la base :
Centralisation du code :
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 de la base
au client est souvent sous‐estimée.
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. Si vous avez besoin de réaliser du traitement de chaîne puissant, ou
de la manipulation de fichiers, PL/Perl ou PL/Python seront probablement des options
plus intéressantes car plus performantes, là aussi utilisables dans la base.
La grande variété des différents langages PL supportés par PostgreSQL permet normale‐
ment d'en trouver un correspondant aux besoins et aux langages déjà maîtrisés dans
l'entreprise.
Les langages PL permettent donc de rajouter une couche d'abstraction et d'effectuer des
traitements avancés directement en base.
Le PL/pgSQL permet d'écrire des requêtes 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.
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.
PL/pgSQL est trusted. Tous les utilisateurs peuvent donc créer des routines dans ce lan‐
gage (par défaut). Vous pouvez toujours soit supprimer le langage, soit retirer les droits à
un utilisateur sur ce langage (via la commande SQL REVOKE).
Les langages PL « autres », comme PL/perl11 et PL/Python (les deux plus utilisés après
PL/pgSQL), 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 main‐
tenir des caches, voire, pour leur version untrusted, peuvent effectuer des appels sys‐
tè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/R12 . 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.
11
https://docs.postgresql.fr/current/plperl‐builtins.html
12
https://github.com/postgres‐plr/plr/blob/master/userguide.md
https://dalibo.com/formations 15
PL/pgSQL : Les bases
Il existe aussi un langage qui est, du moins sur le papier, plus rapide que tous les langages
cités précédemment : vous pouvez écrire des procédures stockées en C13 , directement.
Elles seront compilées à l'extérieur de PostgreSQL, en respectant un certain formalisme,
puis seront 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 ne faire ceci qu'en dernière extrémité.
Le gros défaut est simple et commun à tous ces langages : ils ne sont pas spécialement
conçus pour s'exécuter en tant que langage de procédures stockées. Ce que vous utilisez
quand vous écrivez du PL/Perl est donc du code Perl, avec quelques fonctions supplé‐
mentaires (préfixées par spi) pour accéder à la base de données ; de même en C. L'accès
aux données est assez fastidieux au niveau syntaxique, comparé à PL/pgSQL.
Un autre problème des langages PL (autre que C et PL/pgSQL), est que ces langages
n'ont pas les mêmes types natifs que PostgreSQL, et s'exécutent dans un interpréteur
relativement séparé. Les performances sont donc moindres que PL/pgSQL et C, pour
les traitements dont le plus consommateur est l'accès aux données. Souvent, le temps
de traitement dans un de ces langages plus évolués est tout de même meilleur grâce au
temps gagné par les autres fonctionnalités (la possibilité d'utiliser un cache, ou une table
de hachage par exemple).
• Procédure stockée
– pas de retour
– contrôle transactionnel : COMMIT / ROLLBACK
– PostgreSQL 11 ou +
• 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 » :
13
https://docs.postgresql.fr/current/xfunc‐c.html
16
1.2 Introduction
• 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 /* selon version, voir plus bas
*/ ).
• Les procédures offrent le support du contrôle transactionnel, c'est‐à‐dire la capac‐
ité 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).
https://dalibo.com/formations 17
PL/pgSQL : Les bases
1.3 INSTALLATION
• SQL, C et PL/pgSQL
– compilés et installés par défaut
• Paquets du PGDG pour la plupart des langages :
yum|dnf install postgresql14-plperl
apt install postgresql-plpython3-14
• Autres langages :
– à compiler soi‐même
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'
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 14, les paquets sont postgresql14-plperl (depuis yum.postgresql.org) ou
postgresql-plperl-14 (depuis apt.postgresql.org).
Ainsi, la bibliothèque plperl.so que l'on trouvera dans ces répertoires contiendra les fonc‐
tions qui permettent l'utilisation du langage PL/Perl. Elle 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 ver‐
sions très récentes, ou des langages plus exotiques, peuvent nécessiter une compilation
de l'extension.
18
1.3 Installation
Le langage est activé uniquement dans la base dans laquelle la commande est lancée.
S'il faut l'activer sur plusieurs bases, il sera nécessaire d'exécuter cet ordre SQL sur les
différentes bases ciblées.
Activer un langage dans la base modèle template1 l'activera aussi pour toutes les bases
créées par la suite.
lanname | lanpltrusted
---------+--------------
plpgsql | t
Si un langage est trusted, tous les utilisateurs peuvent créer des procédures dans ce lan‐
gage. 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 « com‐
plet ». La version trusted n'a pas le droit d'ouvrir des fichiers, des sockets, ou autres appels
systèmes qui seraient dangereux.
base=# \dx
Liste des extensions installées
Nom | Version | Schéma | Description
-------------+---------+------------+---------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
20
1.4 Exemples de fonctions & procédures
addition
----------
3
• Intérêt : planification !
• Syntaxe allégée possible en v14
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).
À partir de PostgreSQL 14, il est possible de se passer des guillemets encadrants, pour les
fonctions SQL uniquement. La même fonction devient donc :
https://dalibo.com/formations 21
PL/pgSQL : Les bases
LANGUAGE sql
IMMUTABLE
RETURN entier1 + entier2 ;
Cette nouvelle écriture respecte mieux le standard SQL. Surtout, elle autorise un pars‐
ing 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 :
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 :
22
1.4 Exemples de fonctions & procédures
END ;
$$ ;
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 wiki14 décrit les conditions pour que l'inlining des fonctions SQL fonctionne : obliga‐
tion d'un seul SELECT, interdiction de certains fonctionnalités…
Dans cet exemple, on récupère l'estimation du nombre de lignes actives d'une table
passée en paramètres.
L'intérêt majeur du PL/pgSQL et du SQL sur les autres langages est la facilité d'accès aux
données. Ici, un simple SELECT <champ> INTO <variable> suffit à récupérer une valeur
depuis une table dans une variable.
14
https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions
https://dalibo.com/formations 23
PL/pgSQL : Les bases
nb_lignes_table
-----------------
10000000
$id_facture = $rv->{rows}[0]->{id_facture};
24
1.4 Exemples de fonctions & procédures
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_*
Pour éviter les conflits avec les objets de la base, il est conseillé de préfixer les variables.
https://dalibo.com/formations 25
PL/pgSQL : Les bases
-- 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
return v_id_facture;
END;
$function$ ;
Cette procédure tronque des tables de la base d'exemple pgbench, et annule si dry_run
est vrai.
Les procédures sont récentes dans PostgreSQL (à partir de la version 11). Elles 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 de‐
hors de toute transaction.
Comme pour les fonctions, il est possible d'utiliser le SQL pur dans les cas les plus simples,
26
1.4 Exemples de fonctions & procédures
Toujours pour les procédures en SQL, il existe une variante sans guillemets, à partir de
PostgreSQL 14, 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.
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
https://dalibo.com/formations 27
PL/pgSQL : Les bases
(Pour ce genre de SQL dynamique, si l'on est sous psql , il est souvent plus pratique
d'utiliser \gexec15 .)
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).
15
https://docs.postgresql.fr/current/app‐psql.html#R2‐APP‐PSQL‐4
28
1.5 Utiliser une fonction ou une procédure
Demander l'exécution d'une procédure se fait en utilisant un ordre SQL spécifique : CALL16
. 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.
• dans un SELECT, en passant en argument les valeurs d'une colonne d'une table :
• dans le FROM d'un SELECT, la fonction renvoit ici généralement plusieurs lignes (SETOF),
et un résultat de type RECORD :
16
https://docs.postgresql.fr/current/sql‐call.html
https://dalibo.com/formations 29
PL/pgSQL : Les bases
• dans une création d'index (index fonctionnel, la fonction sera réellement ap‐
pelée lors des mises à jour de l'index… attention la fonction doit être déclarée
« immutable ») :
• 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() :
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.).
30
1.6 Création et maintenance des fonctions et procédures
1.6.1 CRÉATION
• CREATE FUNCTION
• CREATE PROCEDURE
Voici la syntaxe complète pour une fonction d'après la documentation17 :
17
https://www.postgresql.org/docs/current/sql‐createfunction.html
18
https://www.postgresql.org/docs/current/sql‐createprocedure.html
https://dalibo.com/formations 31
PL/pgSQL : Les bases
1.6.2 LANGAGE
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.
Une routine en pur SQL indiquera LANGUAGE sql. On rencontrera aussi plperl, plpython3u,
etc. en fonction des besoins.
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).
32
1.6 Création et maintenance des fonctions et procédures
• 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.
<<mon_label>>
-- le code (blocs DECLARE, BEGIN-END, et EXCEPTION)
[ <<mon_label>> ]
LOOP
ordres …
END LOOP [ mon_label ];
https://dalibo.com/formations 33
PL/pgSQL : Les bases
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 :
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 iden‐
tiques, 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.
Toutes les méta‐données discutées plus haut sont modifiables avec un ALTER.
34
1.6 Création et maintenance des fonctions et procédures
Une fonction pouvant exister en plusieurs exemplaires, avec le même nom et des argu‐
ments de type différents, il faudra parfois parfois préciser ces derniers.
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 $.
Ce peut être utile aussi dans tout code réalisant une concaténation de chaînes de carac‐
tères contenant des guillemets. La syntaxe traditionnelle impose de les multiplier pour
les protéger, et le code devient difficile à lire. :
requete := requete || '' AND vin LIKE ''''bordeaux%'''' AND xyz ''
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.
36
1.7 Paramètres et retour des fonctions et procédures
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.
Si le mode d'un argument est omis, IN est la valeur implicite : la valeur en entrée ne sera
pas modifiée.
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 mais sera également mod‐
ifié.
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.
https://dalibo.com/formations 37
PL/pgSQL : Les bases
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).
Jusque PostgreSQL 13 inclus, les procédures ne supportent pas les arguments OUT, seule‐
ment IN et INOUT.
• Fonctions uniquement
RETURNS type -- int, text, etc
Avant la version 11, il n'était pas possible de créer une procédure, mais il était possible de
créer une fonction se comportant globalement comme une procédure en utilisant le type
de retour void.
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).
38
1.7 Paramètres et retour des fonctions et procédures
3 options :
• Type composé dédié
CREATE TYPE ma_structure AS ( … ) ;
CREATE FUNCTION … RETURNS ma_structure ;
• Paramètres OUT
CREATE FUNCTION explose_date (IN d date, OUT jour int, OUT mois int, OUT annee int) AS …
• RETURNS TABLE
CREATE FUNCTION explose_date_table (d date)
RETURNS TABLE (jour integer, mois integer, annee integer) AS…
S'il y a besoin de renvoyer plusieurs valeurs à la fois, une possibilité est de renvoyer un
type composé défini auparavant.
Une alternative courante est d'utiliser plusieurs paramètres OUT (et pas de clause RETURN
dans l'entête) pour obtenir un enregistrement composite :
CREATE OR REPLACE FUNCTION explose_date (IN d date, OUT jour int, OUT mois int, OUT annee int)
AS $$
SELECT extract (day FROM d)::int, extract(month FROM d)::int, extract (year FROM d)::int
$$
LANGUAGE SQL;
La clause TABLE est une autre alternative, sans doute plus claire. Cet exemple devient
alors, toujours en pur SQL :
https://dalibo.com/formations 39
PL/pgSQL : Les bases
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 :
S'il y 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 :
40
1.7 Paramètres et retour des fonctions et procédures
schemaname | tablename
------------+------------------
public | pgbench_accounts
public | pgbench_branches
public | pgbench_history
public | pgbench_tellers
(4 lignes)
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 :
(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 en‐
registrement composé d'une copie de toutes les variables, au moment de l'appel à RETURN
NEXT.)
La variante RETURN QUERY EXECUTE … est destinée à des requêtes en SQL dynamique.
https://dalibo.com/formations 41
PL/pgSQL : Les bases
Les fonctions avec RETURN QUERY ou RETURN NEXT stockent tout le résultat avant de le
retourner en bloc. Le paramètre work_mem permet de définir la mémoire utilisée avant
l'utilisation d'un fichier temporaire, qui a bien sûr un impact sur les performances.
Si RETURNS TABLE est peut‐être le plus souple et 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.
Quand plusieurs lignes sont renvoyées, tout est conservé en mémoire jusqu'à la fin de la
fonction. Donc, si beaucoup de données sont renvoyées, cela poser des problèmes de
latence, voire de mémoire.
SELECT ma_fonction();
pour récupérer un résultat d'une seule colonne, scalaire, type composite ou RECORD suivant
la fonction.
# SELECT pg_control_system () ;
pg_control_system
---------------------------------------------------------------
(1201,201909212,6744959735975969621,"2021-09-17 18:24:05+02")
(1 ligne)
42
1.7 Paramètres et retour des fonctions et procédures
Si une fonction est définie comme STRICT et qu'un des arguments d'entrée est NULL, Post‐
greSQL 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.
https://dalibo.com/formations 43
PL/pgSQL : Les bases
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 :
• sa valeur initiale (si rien n'est précisé, ce sera NULL par défaut) :
• 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) :
Pour les fonctions complexes, avec plusieurs niveaux de boucle par exemple, il est possi‐
ble 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.
44
1.8 Variables en PL/pgSQL
1.8.2 CONSTANTES
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.
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
https://dalibo.com/formations 45
PL/pgSQL : Les bases
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.
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.
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.
46
1.9 Exécution de requête dans un bloc PL/pgSQL
Dans ce cas, l'expression myvar > 0 sera préparée par le moteur de la façon suivante :
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.
• Utiliser l'opérateur := :
un_entier := 5;
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 :
19
https://docs.postgresql.fr/current/plpgsql‐implementation.html#PLPGSQL‐PLAN‐CACHING
https://dalibo.com/formations 47
PL/pgSQL : Les bases
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 fonc‐
tionner, mais c'est très peu lisible, et donc non recommandé.
• Affectation de la ligne :
SELECT *
INTO ma_variable_ligne -- type ROW ou RECORD
FROM …;
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 :
Dans le cas du type ROW, la définition de la ligne doit correspondre parfaitement à la défi‐
nition 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) :
48
1.9 Exécution de requête dans un bloc PL/pgSQL
RETURN n ;
END ;
$$ ;
• Variable FOUND
– si une ligne est affectée par l'instruction
• Nombre de lignes :
GET DIAGNOSTICS variable = ROW_COUNT;
On peut déterminer qu'aucune ligne n'a été trouvée par la requête en utilisant la variable
FOUND :
PERFORM 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 :
https://dalibo.com/formations 49
PL/pgSQL : Les bases
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.
50
1.10 SQL dynamique
EXECUTE dans un bloc PL/pgSQL permet notamment du SQL dynamique : l'ordre peut être
construit dans une variable.
Un danger du SQL dynamique est de faire aveuglément confiance aux valeurs des vari‐
ables en construisant un ordre SQL :
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 XKCD20 .
20
https://xkcd.com/327/
https://dalibo.com/formations 51
PL/pgSQL : Les bases
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 \gexec21 . En
SQL pur, la protection contre les injections SQL est un argument pour utiliser les requêtes
préparées22 , dont l'ordre EXECUTE diffère de celui‐ci du PL/pgSQL ci‐dessous.
Le second est plus lisible grâce à la fonction de formatage format24 qui évite ces con‐
caté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).
52
1.10 SQL dynamique
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;
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.
26
https://www.postgresql.org/docs/current/plpgsql‐statements.html#PLPGSQL‐QUOTE‐LITERAL‐EXAMPLE
https://dalibo.com/formations 53
PL/pgSQL : Les bases
• 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 :
SELECT format(
'SELECT %I FROM %I WHERE %1$I=%3$L',
'MaColonne',
'ma_table',
$$l'été$$
);
format
-------------------------------------------------------------
SELECT "MaColonne" FROM ma_table WHERE "MaColonne"='l''été'
54
1.11 Structures de contrôle en PL/pgSQL
Exemple :
IF nombre = 0 THEN
resultat := 'zero';
ELSEIF nombre > 0 THEN
resultat := 'positif';
ELSEIF nombre < 0 THEN
resultat := 'négatif';
ELSE
resultat := 'indéterminé';
END IF;
CASE nombre
WHEN nombre = 0 THEN 'zéro'
WHEN variable > 0 THEN 'positif'
WHEN variable < 0 THEN 'négatif'
ELSE 'indéterminé'
END CASE
ou :
CASE current_setting ('server_version_num')::int/10000
WHEN 8,9 THEN RAISE NOTICE 'Version non supportée !!' ;
WHEN 10,11,12,13,14 THEN RAISE NOTICE 'Version supportée' ;
ELSE RAISE NOTICE 'Version inconnue au 1/11/2021 ?' ;
END CASE ;
L'instruction CASE WHEN est proche de l'expression CASE27 des requêtes SQL dans son
principe (à part qu'elle se clôt par END en SQL, et END CASE en PL/pgSQL).
Exemple complet :
DO $$
BEGIN
CASE current_setting ('server_version_num')::int/10000
WHEN 8,9 THEN RAISE NOTICE 'Version non supportée !!' ;
27
https://docs.postgresql.fr/current/functions‐conditional.html#FUNCTIONS‐CASE
https://dalibo.com/formations 55
PL/pgSQL : Les bases
• 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]
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.
28
https://docs.postgresql.fr/current/plpgsql‐control‐structures.html#PLPGSQL‐CONTROL‐STRUCTURES‐LOOPS
56
1.11 Structures de contrôle en PL/pgSQL
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 :
FOR a, b, c, d IN
(SELECT col_a, col_b, col_c, col_d FROM ma_table)
https://dalibo.com/formations 57
PL/pgSQL : Les bases
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.
• sans SLICE :
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 $$ ;
INFO: var: 1
INFO: var: 2
INFO: var: 3
INFO: var: 4
INFO: var: 5
INFO: var: 6
• avec SLICE :
DO $$
DECLARE a int[] := ARRAY[[1,2],[3,4],[5,6]];
b int[];
BEGIN
FOREACH b SLICE 1 IN ARRAY a LOOP
58
1.12 Autres propriétés des fonctions
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.
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.
• 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. Créer la fonction dans un
schéma séparé permet aussi de gérer plus finalement les accès.
https://dalibo.com/formations 59
PL/pgSQL : Les bases
• Il faut se protéger des variables de session qui pourraient être utilisées pour modi‐
fier 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 im‐
pérativement être positionné en dur dans cette fonction (soit d'emblée, avec un
SET dans la fonction, soit en positionnant un SET dans le CREATE FUNCTION) ; ou
bien les fonctions doivent préciser systématiquement le schéma (SELECT … FROM
nomschema.nomtable …).
• 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
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.
1.12.3 PARALLÉLISATION
• 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.
60
1.12 Autres propriétés des fonctions
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 cas de doute, les fonctions doivent être marquées comme UNSAFE, ce qui correspond à
la valeur par défaut.
https://dalibo.com/formations 61
PL/pgSQL : Les bases
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 com‐
portement 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.
62
1.13 Utilisation de fonctions dans les index
Définissons une fonction un peu naïve ramenant le premier jour du mois, volatile faute
de mieux :
Demandons juste le plan d'un appel ne portant que sur le dernier jour :
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 deman‐
dons 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 ap‐
pelé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 :
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,
https://dalibo.com/formations 63
PL/pgSQL : Les bases
les paramètres de la session ou les données de la base peuvent même changer entre la
planification et l'exécution).
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 à nou‐
veau 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 :
La valeur est substituée très tôt, ce qui permet de les comparer aux statistiques :
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 :
64
1.13 Utilisation de fonctions dans les index
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 :
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 :
https://dalibo.com/formations 65
PL/pgSQL : Les bases
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 :
\x
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;
-[ 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
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;
-[ 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 :
66
1.14 Conclusion
1.14 CONCLUSION
• Grand nombre de structure de contrôle (test, boucle, etc.)
• Facile à utiliser et à comprendre
• Attention à la compatibilité ascendante
• Documentation officielle
– « Chapitre 40. PL/pgSQL ‐ Langage de procédures SQL »
1.14.2 QUESTIONS
répondre (q) ;
END LOOP ;
29
https://docs.postgresql.fr/current/plpgsql.html
https://dalibo.com/formations 67
PL/pgSQL : Les bases
1.15 QUIZ
https://dali.bo/p1_quiz
68
1.16 Travaux pratiques
1.16.1 HELLO
1.16.2 DIVISION
https://dalibo.com/formations 69
PL/pgSQL : Les bases
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.
1.16.4 MULTIPLICATION
70
1.16 Travaux pratiques
1.16.5 SALUTATIONS
É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.
https://dalibo.com/formations 71
PL/pgSQL : Les bases
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.
Enfin, écrire une fonction qui renvoie tous les jours fériés d'une
année (libellé et date).
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
30
https://fr.wikipedia.org/wiki/Calcul_de_la_date_de_P%C3%A2ques
72
1.16 Travaux pratiques
https://dalibo.com/formations 73
PL/pgSQL : Les bases
1.17.1 HELLO
Requêtage :
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=32)
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
74
1.17 Travaux pratiques (solutions)
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.
1.17.2 DIVISION
Attention, sous PostgreSQL, la division de deux entiers est par défaut entière : il faut donc
transtyper.
division
--------------------
1.5000000000000000
https://dalibo.com/formations 75
PL/pgSQL : Les bases
SELECT division(1,0);
ERROR: division by zero
CONTEXTE : PL/pgSQL function division(integer,integer) line 3 at RETURN
Pour la version en PL :
division
----------
NaN
76
1.17 Travaux pratiques (solutions)
Test :
nb_embauches
--------------
9
n | nb_embauches
------+--------------
2000 | 2
2001 | 0
2002 | 0
2003 | 1
2004 | 0
2005 | 2
2006 | 9
2007 | 0
2008 | 0
https://dalibo.com/formations 77
PL/pgSQL : Les bases
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.
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 :
annee | nombre_embauches
-------+------------------
2006 | 9
2007 | 0
2008 | 0
2009 | 0
2010 | 0
1.17.4 MULTIPLICATION
78
1.17 Travaux pratiques (solutions)
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;
https://dalibo.com/formations 79
PL/pgSQL : Les bases
END IF;
RETURN a1*a2;
END
$BODY$
LANGUAGE plpgsql;
Test :
multiplication
----------------
6
multiplication
----------------
8
Si ce n'est déjà fait, créer une autre fonction pour faire la con‐
version de texte en chiffre, que multiplication appellera avant
d'effectuer le calcul.
80
1.17 Travaux pratiques (solutions)
RETURN ret;
END
$BODY$
LANGUAGE plpgsql;
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.
https://dalibo.com/formations 81
PL/pgSQL : Les bases
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;
1.17.5 SALUTATIONS
82
1.17 Travaux pratiques (solutions)
IF heure > 12
THEN
libelle := 'Bonsoir';
ELSE
libelle := 'Bonjour';
END IF;
Test :
salutation
---------------------
Bonsoir Guillaume !
salutation
---------------------
Bonsoir Guillaume !
https://dalibo.com/formations 83
PL/pgSQL : Les bases
84
1.17 Travaux pratiques (solutions)
É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.
inverser
----------
otot
https://dalibo.com/formations 85
PL/pgSQL : Les bases
paques
------------
2018-04-01
2019-04-21
2020-04-12
2021-04-04
2022-04-17
Version complexe :
86
1.17 Travaux pratiques (solutions)
RETURN r;
END;
$$
LANGUAGE plpgsql;
Version simple :
Test :
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
Enfin, écrire une fonction qui renvoie tous les jours fériés d'une
année (libellé et date).
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.
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.
https://dalibo.com/formations 87
PL/pgSQL : Les bases
RETURN;
END;
$$
LANGUAGE plpgsql;
SELECT *
FROM vacances(2020, true) AS (libelle text, jour date)
ORDER BY jour ;
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
88
1.17 Travaux pratiques (solutions)
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
Une autre forme d'écriture possible consiste à indiquer les deux colonnes de retour
comme des paramètres OUT :
https://dalibo.com/formations 89
PL/pgSQL : Les bases
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$;
SELECT *
FROM vacances(2020)
ORDER BY jour ;
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
https://dalibo.com/formations 91
NOTES
NOTES
NOS AUTRES PUBLICATIONS
FORMATIONS
• DBA1 : Administra on PostgreSQL
https://dali.bo/dba1
LIVRES BLANCS
• Migrer d’Oracle à PostgreSQL
• Industrialiser PostgreSQL
TÉLÉCHARGEMENT GRATUIT
Les versions électroniques de nos publica ons sont disponibles gratuitement sous licence
open-source ou sous licence Crea ve Commons. Contactez-nous à l’adresse contact@
dalibo.com pour plus d’informa on.
DALIBO, L’EXPERTISE POSTGRESQL
Depuis 2005, DALIBO met à la disposi on de ses clients son savoir-faire dans le domaine
des bases de données et propose des services de conseil, de forma on et de support aux
entreprises et aux ins tu onnels.