Révision 23.01 (27 janvier 2023)
Dalibo SCOP
Creative Commons BY-NC-SA
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 :
PL est l’acronyme de « Procedural Languages ». En dehors du C et du SQL, tous les langages acceptés par PostgreSQL sont des PL.
Par défaut, trois langages sont installés et activés : C, SQL et PL/pgSQL.
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 :
D’autres langages PL sont accessibles en tant qu’extensions tierces. Les plus stables sont mentionnés dans la documentation, comme PL/Java ou PL/R. Ils réclament généralement d’installer les bibliothèques du langage sur le serveur.
Une liste plus large est par ailleurs disponible sur le wiki PostgreSQL, 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 documentation.
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 contre, ils peuvent ensuite donner les droits d’exécution à ces routines aux autres rôles dans la base :
GRANT EXECUTE ON FUNCTION nom_fonction TO un_role ;
La question se pose souvent de placer la logique applicative du côté de la base, dans un langage PL, ou des clients. Il peut y avoir de nombreuses raisons en faveur de la première option. 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 mise en place d’identifiants pour liens entre ces tables, peut évidemment être écrite côté client. Il est quelquefois plus pratique de l’écrire sous forme de PL. Les avantages sont :
Centralisation du code :
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 de la base au client est souvent sous-estimée.
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. 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 normalement 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 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.
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 langage (par défaut). Vous pouvez toujours
soit supprimer le langage, 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, peu de pièges, et il dispose d’une gestion des erreurs évoluée (gestion d’exceptions).
Les langages PL « autres », comme PL/perl 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 maintenir des caches, voire, pour leur version untrusted, peuvent effectuer des appels systèmes. Dans ce cas, il devient possible d’appeler un service web par exemple, ou d’écrire des données dans un fichier externe.
Il existe des langages PL spécialisés. Le plus emblématique d’entre eux est PL/R. R est un langage utilisé par les statisticiens pour manipuler de gros jeux de données. PL/R permet donc d’effectuer ces traitements R directement en base, traitements qui seraient très pénibles à écrire dans d’autres langages, et avec une latence dans le transfert des données.
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 C, 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).
Les programmes écrits à l’aide des langages PL sont habituellement enregistrés sous forme de « routines » :
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 :
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 */ ).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.CALL
; les fonctions peuvent être appelées dans la plupart
des ordres DML/DQL (notamment SELECT
), mais pas par
CALL
.TRIGGER
,
agrégat ou fonction de fenêtrage).Pour savoir si PL/Perl ou PL/Python a été compilé, on peut demander à
pg_config
:
pg_config --configure
'--prefix=/usr/local/pgsql-10_icu' '--enable-thread-safety'
'--with-openssl' '--with-libxml' '--enable-nls' '--with-perl' '--enable-debug'
'ICU_CFLAGS=-I/usr/local/include/unicode/'
'ICU_LIBS=-L/usr/local/lib -licui18n -licuuc -licudata' '--with-icu'
Si besoin, les emplacements exacts d’installation des bibliothèques
peuvent être récupérés à l’aide des options --libdir
et
--pkglibdir
de pg_config
.
Cependant, dans les paquets fournis par le PGDG, il faudra installer
explicitement le paquet dédié à plperl
pour la version
majeure de PostgreSQL concernée. Pour PostgreSQL 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 fonctions 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.
De même pour Python 3 (paquets postgresql14-plpython3
ou
postgresql-plython3-14
).
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.
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.
Voici un exemple d’interrogation de pg_language
:
SELECT lanname, lanpltrusted
FROM pg_language
WHERE lanname='plpgsql';
lanname | lanpltrusted---------+--------------
plpgsql | t
Si un langage est trusted, tous les utilisateurs peuvent créer des procédures dans ce langage. 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.
C, PL/TclU, PL/PerlU, et PL/PythonU (et les variantes spécifiques aux versions PL/Python2U et PL/Python3U) sont untrusted.
Les langages PL sont généralement installés par le biais d’extensions :
base=# \dx
Liste des extensions installées
Nom | Version | Schéma | Description
-------------+---------+------------+---------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
SELECT addition (1,2);
addition
----------
3
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 :
CREATE OR REPLACE FUNCTION addition (entier1 integer, entier2 integer)
integer
RETURNS
LANGUAGE sql
IMMUTABLERETURN entier1 + entier2 ;
Cette nouvelle écriture respecte mieux le standard SQL. Surtout, elle autorise un parsing et une vérification des objets impliqués dès la déclaration, et non à l’utilisation. Les dépendances entre fonctions et objets utilisés sont aussi mieux tracées.
L’avantage principal des fonctions en pur SQL est, si elles sont assez simples, leur intégration lors de la réécriture interne de la requête (inlining) : elles ne sont donc pas pour l’optimiseur des « boîtes noires ». À l’inverse, l’optimiseur ne sait rien du contenu d’une fonction PL/pgSQL.
Dans l’exemple suivant, la fonction sert de filtre à la requête.
Comme elle est en pur SQL, elle permet d’utiliser l’index sur la colonne
date_embauche
de la table employes_big
:
CREATE OR REPLACE function employe_eligible_prime_sql (service int, date_embauche date)
boolean
RETURNS
LANGUAGE sqlAS $$
SELECT ( service !=3 AND date_embauche < '2003-01-01') ; -- ancien employé, sauf un service
$$ ;
EXPLAIN (ANALYZE) SELECT matricule, num_service, nom, prenom
FROM employes_big
WHERE employe_eligible_prime_sql (num_service, date_embauche) ;
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using employes_big_date_embauche_idx on employes_big
(cost=0.42..1.54 rows=1 width=22) (actual time=0.008..0.009 rows=1 loops=1)
Index Cond: (date_embauche < '2003-01-01'::date)
Filter: (num_service <> 3)
Rows Removed by Filter: 1
Planning Time: 0.102 ms
Execution Time: 0.029 ms
Avec une version de la même fonction en PL/pgSQL, le planificateur ne voit pas le critère indexé. Il n’a pas d’autre choix que de lire toute la table et d’appeler la fonction pour chaque ligne, ce qui est bien sûr plus lent :
CREATE OR REPLACE function employe_eligible_prime_pl (service int, date_embauche date)
boolean
RETURNS AS $$
LANGUAGE plpgsql BEGIN
RETURN ( service !=3 AND date_embauche < '2003-01-01') ;
END ;
$$ ;
EXPLAIN (ANALYZE) SELECT matricule, num_service, nom, prenom
FROM employes_big
WHERE employe_eligible_prime_pl (num_service, date_embauche) ;
QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on employes_big (cost=0.00..134407.90 rows=166338 width=22)
(actual time=0.069..269.121 rows=1 loops=1)
Filter: employe_eligible_prime_pl(num_service, date_embauche)
Rows Removed by Filter: 499014
Planning Time: 0.038 ms
Execution Time: 269.157 ms
Le wiki
décrit les conditions pour que l’inlining des fonctions SQL
fonctionne : obligation d’un seul SELECT
, interdiction de
certains fonctionnalités…
Dans cet exemple, on récupère l’estimation du nombre de lignes actives d’une table passée en paramètres.
L’intérêt majeur du PL/pgSQL et du SQL sur les autres langages est la
facilité d’accès aux données. Ici, un simple
SELECT <champ> INTO <variable>
suffit à
récupérer une valeur depuis une table dans une variable.
SELECT nb_lignes_table ('public', 'pgbench_accounts');
nb_lignes_table
-----------------
10000000
Voici l’exemple de la fonction :
CREATE OR REPLACE FUNCTION
public.demo_insert_perl(nom_client text, titre_facture text)
integer
RETURNS
LANGUAGE plperl
STRICTAS $function$
use strict;
=@_;
my ($nom_client, $titre_facture)
my $rv;
my $id_facture;
my $id_client;
'il ?
# Le client existe t $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 :
=$rv->{rows}[0]->{id_client};
$id_client
# Insérons maintenant la facture= spi_exec_query(
$rv 'INSERT INTO mes_factures (titre_facture, id_client) VALUES ('
", $id_client ) RETURNING id_facture"
. quote_literal($titre_facture) .
);
= $rv->{rows}[0]->{id_facture};
$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 :
spi_*
Pour éviter les conflits avec les objets de la base, il est conseillé de préfixer les variables.
CREATE OR REPLACE FUNCTION
public.demo_insert_plpgsql(p_nom_client text, p_titre_facture text)
integer
RETURNS
LANGUAGE plpgsql
STRICTAS $function$
DECLARE
int;
v_id_facture int;
v_id_client 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$ ;
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 dehors de toute transaction.
Comme pour les fonctions, il est possible d’utiliser le SQL pur dans les cas les plus simples, sans contrôle transactionnel notamment :
CREATE OR REPLACE PROCEDURE vide_tables ()
AS '
TRUNCATE TABLE pgbench_history ;
TRUNCATE TABLE pgbench_accounts CASCADE ;
TRUNCATE TABLE pgbench_tellers CASCADE ;
TRUNCATE TABLE pgbench_branches CASCADE ;
' LANGUAGE sql;
Toujours pour les procédures en SQL, il existe une variante sans guillemets, à 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.
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 ;
Les blocs anonymes sont utiles pour des petits scripts ponctuels qui nécessitent des boucles ou du conditionnel, voire du transactionnel, sans avoir à créer une fonction ou une procédure. Ils ne renvoient rien. Ils sont habituellement en PL/pgSQL mais tout langage procédural installé est possible.
L’exemple ci-dessus lance un ANALYZE
sur toutes les
tables où les statistiques n’ont pas été calculées d’après la vue
système, et donne aussi un exemple de SQL dynamique. Le résultat est par
exemple :
NOTICE: Analyze public.pgbench_history
NOTICE: Analyze public.pgbench_tellers
NOTICE: Analyze public.pgbench_accounts
NOTICE: Analyze public.pgbench_branches
DO
Temps : 141,208 ms
(Pour ce genre de SQL dynamique, si l’on est sous psql
, il est souvent plus pratique d’utiliser
\gexec
.)
Noter que les ordres constituent une transaction unique, à moins de
rajouter des COMMIT
ou ROLLBACK
explicitement
(ce n’est autorisé qu’à partir de la version 11).
Demander l’exécution d’une procédure se fait en utilisant un ordre
SQL spécifique : CALL
. Il suffit
de fournir les paramètres. Il n’y a pas de code retour.
Les fonctions ne sont quant à elles pas directement compatibles avec
la commande CALL
, il faut les invoquer dans le contexte
d’une commande SQL. Elles sont le plus couramment appelées depuis des
commandes de type DML (SELECT
, INSERT
, etc.),
mais on peut aussi les trouver dans d’autres commandes.
Voici quelques exemples :
SELECT
(la fonction ne doit renvoyer qu’une
seule ligne) :SELECT ma_fonction('arg1', 'arg2');
SELECT
, en passant en argument les valeurs
d’une colonne d’une table :SELECT ma_fonction(ma_colonne) FROM ma_table;
FROM
d’un SELECT
, la fonction
renvoit ici généralement plusieurs lignes (SETOF
), et un
résultat de type RECORD
:SELECT result FROM ma_fonction() AS f(result);
INSERT
pour générer la valeur à insérer :INSERT INTO ma_table(ma_colonne) VALUES ( ma_fonction() );
CREATE INDEX ON ma_table ( ma_fonction(ma_colonne) );
ma_fonction()
(qui doit renvoyer une seule ligne) est passé
en argument d’entrée de la procédure ma_procedure()
:CALL ma_procedure( ma_fonction() );
Par ailleurs, certaines fonctions sont spécialisées et ne peuvent être invoquées que dans le contexte pour lequel elles ont été conçues (fonctions trigger, d’agrégat, de fenêtrage, etc.).
Voici la syntaxe complète pour une fonction d’après la documentation :
CREATE [ OR REPLACE ] FUNCTION
DEFAULT | = } default_expr ] [, …] ] )
name ( [ [ argmode ] [ argname ] argtype [ {
[ RETURNS rettypeTABLE ( column_name column_type [, …] ) ]
| RETURNS
{ LANGUAGE lang_nameFOR TYPE type_name } [, … ]
| TRANSFORM {
| WINDOW
| { IMMUTABLE | STABLE | VOLATILE }NOT ] LEAKPROOF
| [ ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
| { CALLED DEFINER }
| { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY PARALLEL { UNSAFE | RESTRICTED | SAFE }
| COST execution_cost
| ROWS result_rows
|
| SUPPORT support_functionSET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
|
| sql_body } …
Voici la syntaxe complète pour une procédure d’après la documentation :
CREATE [ OR REPLACE ] PROCEDURE
DEFAULT | = } default_expr ] [, …] ] )
name ( [ [ argmode ] [ argname ] argtype [ {
{ LANGUAGE lang_nameFOR TYPE type_name } [, … ]
| TRANSFORM { DEFINER
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
|
| sql_body } …
Nous allons décrire les clauses importantes ci-dessous.
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.
Ici ce sera surtout : LANGUAGE plpgsql
.
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).
L’opérateur de comparaison est =
, l’opérateur
d’affectation :=
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.
Indiquer le nom d’un label ainsi :
<<mon_label>>
-- le code (blocs DECLARE, BEGIN-END, et EXCEPTION)
ou bien (pour une boucle)
<<mon_label>> ]
[ LOOP
ordres …END LOOP [ mon_label ];
Bien sûr, il est aussi possible d’utiliser des labels pour des
boucles FOR
, WHILE
, FOREACH
.
On sort d’un bloc ou d’une boucle avec la commande EXIT
,
on peut aussi utiliser CONTINUE
pour passer à l’exécution
suivante d’une boucle sans terminer l’itération courante.
Par exemple :
WHEN compteur > 1; EXIT [mon_label]
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.
Toutes les méta-données discutées plus haut sont modifiables avec un
ALTER
.
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.
Définir une fonction entre guillemets simples ('
)
devient très pénible dès que la fonction doit en contenir parce qu’elle
contient elle-même des chaînes de caractères. PostgreSQL permet de
remplacer les guillemets par $$
, ou tout mot encadré de
$
.
Par exemple, on peut reprendre la syntaxe de déclaration de la
fonction addition()
précédente en utilisant cette
méthode :
CREATE FUNCTION addition(entier1 integer, entier2 integer)
integer
RETURNS
LANGUAGE plpgsql
IMMUTABLEAS $ma_fonction_addition$
DECLARE
integer;
resultat BEGIN
:= entier1 + entier2;
resultat RETURN resultat;
END
$ma_fonction_addition$;
Ce peut être utile aussi dans tout code réalisant une concaténation de chaînes de caractères contenant des guillemets. La syntaxe traditionnelle impose de les multiplier pour les protéger, et le code devient difficile à lire. :
:= requete || '' AND vin LIKE ''''bordeaux%'''' AND xyz '' requete
En voilà une simplification grâce aux dollars :
:= requete || $sql$ AND vin LIKE 'bordeaux%' AND xyz $sql$ requete
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.
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
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).
Jusque PostgreSQL 13 inclus, les procédures ne supportent pas les
arguments OUT
, seulement IN
et
INOUT
.
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 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.
Le type de retour (clause RETURNS
dans l’entête) est
obligatoire pour les fonctions et interdit pour les procédures.
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).
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;
SELECT * FROM explose_date ('31-12-2020');
jour | mois | annee
------+------+-------
31 | 0 | 2020
(Noter que l’exemple ci-dessus est en simple SQL.)
La clause TABLE
est une autre alternative, sans doute
plus claire. Cet exemple devient alors, toujours en pur SQL :
CREATE OR REPLACE FUNCTION explose_date_table (d date)
TABLE (jour integer, mois integer, annee integer)
RETURNS
LANGUAGE sqlAS $$
SELECT extract (day FROM d)::int, extract(month FROM d)::int, extract (year FROM d)::int ;
$$ ;
Pour renvoyer plusieurs lignes, la première possibilité est de
déclarer un type de retour SETOF
. Cet exemple utilise
RETURN NEXT
pour renvoyer les lignes une à une :
CREATE OR REPLACE FUNCTION liste_entiers_setof (limite int)
integer
RETURNS SETOF
LANGUAGE plpgsqlAS $$
BEGIN
FOR i IN 1..limite LOOP
RETURN NEXT i;
END LOOP;
END
$$ ;
SELECT * FROM liste_entiers_setof (3) ;
liste_entiers_setof
---------------------
1
2
3
(3 lignes)
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 :
CREATE TYPE pgt AS (schemaname text, tablename text) ;
CREATE OR REPLACE FUNCTION tables_by_owner (p_owner text)
RETURNS SETOF pgt
LANGUAGE plpgsqlAS $$
BEGIN
RETURN QUERY SELECT schemaname::text, tablename::text
FROM pg_tables WHERE tableowner=p_owner
ORDER BY tablename ;
END ; $$ ;
SELECT * FROM tables_by_owner ('pgbench');
schemaname | tablename
------------+------------------
public | pgbench_accounts
public | pgbench_branches
public | pgbench_history
public | pgbench_tellers
(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 :
CREATE OR REPLACE FUNCTION liste_entiers_table (limite int)
TABLE (j int)
RETURNS AS $$
BEGIN
FOR i IN 1..limite LOOP
= i ;
j RETURN NEXT ; -- renvoie la valeur de j en cours
END LOOP;
END $$ LANGUAGE plpgsql;
SELECT * FROM liste_entiers_table (3) ;
j
---
1
2
3
(3 lignes)
(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
.)
CREATE OR REPLACE FUNCTION tables_by_owner (p_owner text)
TABLE (schemaname text, tablename text)
RETURNS
LANGUAGE plpgsqlAS $$
BEGIN
RETURN QUERY SELECT schemaname::text, tablename::text
FROM pg_tables WHERE tableowner=p_owner
ORDER BY tablename ;
END ; $$ ;
La variante RETURN QUERY EXECUTE …
est destinée à des
requêtes en SQL dynamique.
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.
En général, l’appel se fait ainsi pour obtenir des lignes :
SELECT * FROM ma_fonction();
Une alternative est d’utiliser :
SELECT ma_fonction();
pour récupérer un résultat d’une seule colonne, scalaire, type
composite ou RECORD
suivant la fonction.
Cette différence concerne aussi les fonctions système :
SELECT * FROM pg_control_system () ; #
pg_control_version | catalog_version_no | system_identifier | pg_control_last_modified
--------------------+--------------------+---------------------+--------------------------
1201 | 201909212 | 6744959735975969621 | 2021-09-17 18:24:05+02
(1 ligne)
SELECT pg_control_system () ; #
pg_control_system
---------------------------------------------------------------
(1201,201909212,6744959735975969621,"2021-09-17 18:24:05+02")
(1 ligne)
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
.
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 :
IN
,
INOUT
ou OUT
) ;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) :
integer := 42; answer
sa valeur par défaut, si on veut autre chose que
NULL
:
integer DEFAULT 42; answer
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) :
integer NOT NULL DEFAULT 42; answer
le collationnement à utiliser, pour les variables de type chaîne de caractères :
"en_GB"; question text COLLATE
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.
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.
Cela permet d’écrire des routines plus génériques.
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.
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