Postgresql et PL/pgsql: une petite initiation aux fonctions

26 juin 201926 juin 2019 Atilio Francois 2 Comments

PL/pgsql est un puissant langage de script SQL fortement influencé par PL/SQL, le langage de procédure stocké distribué avec Oracle. Il est inclus dans la grande majorité des installations de PostgreSQL comme une partie standard du produit, donc il ne nécessite aucune configuration pour être utilisé.
PL/pgsql a aussi un petit côté secret . Les développeurs PostgreSQL ne veulent pas que vous sachiez que c’est un langage de développement SQL à part entière, capable de faire à peu près tout dans la base de données PostgreSQL.
Pourquoi est-ce un secret ? Pendant des années, PostgreSQL n’a pas prétendu avoir des procédures stockées.
Les fonctions PL/pgsql ont été conçues à l’origine pour retourner des valeurs scalaires et étaient destinées à des tâches mathématiques simples et à une manipulation triviale de chaînes.
Au fil des ans, PL/pgsql a développé un ensemble riche de structures de contrôle et a acquis la capacité d’être utilisé par les déclencheurs (triggers), opérateurs et index. En fin de compte, les développeurs ont été contraints d’admettre à contrecœur qu’ils avaient un système complet de développement de procédures stockées entrer leurs mains.
En cours de route, l’objectif de PL/pgsql a changé de fournir des fonctions scalaires simplistes vers la fourniture d’une structure de contrôle complète. La liste complète de ce qui est disponible dans la version actuelle se trouve à http://www.postgresql.org/docs/current/static/plpgsql-overview.html.

Les fonctions sont créées à partir d’une fenêtre SQL, avec la commande CREATE FUNCTION. Elle sont ajoutées dans l’onglet « functions » de votre schéma de base de données:

Structure d’une fonction PL/pgsql

La structure d’une fonction en PL/pgsql est très simple

CREATE FUNCTION nom_de_la_fonction(paramètres de la fonction)
RETURNS résultat_de_la_fonction
AS
$$
BEGIN
code_de_la_fonction;
END
$$
LANGUAGE plpgsql;

Voyons les différents éléments un peu plus en détail.

nom_de_la_fonction(paramètres de la fonction)

Si nous voyons ici le nom de la fonction ET les paramètres de la fonction, c’est parce que, en PL/pgsql ces deux éléments sont indissociables. En effet, la surcharge (overloading) est une autre caractéristique des fonctions PostgreSQL, qui permet plusieurs procédures utilisant le même nom, mais des paramètres différents.

Voyons un exemple concret avec la fonction st_buffer incluse dans l’extension Postgis

Dans ce cas, nous avons neuf déclarations de la fonction, chacune ayant deux ou trois paramètres de type différent. Dans cet exemple, la surcharge est utilisée pour implémenter la construction d’un buffer à partir de trois types d’entités différentes :géométrie, géographie ou un texte contenant une géométrie ( le premier paramètre), un rayon (deuxième paramètre), et un troisième paramètre permettant de contrôler le type de buffer résultant.

Au moment de l’appel de la fonction, Postgresql compare les types de paramètres passés et exécute la fonction correspondante. S’il ne trouve pas de correspondance vous aurez un message du type:

Il faut absolument définir le type de donnée de chaque paramètre, par exemple

fct_mid(varchar, integer, integer)

Dans ce cas vous pourrez vous référer dans le code de la fonction à chaque paramètre par son numéro d’ordre

substring($1,$2,$3)

Mais c’est plus facile à lire si vous donnez un nom de variable à chaque paramètre:

fct_mid(chaine varchar, cardeb integer, longueur integer)

Et dans le code:

substring(chaine,cardeb,longueur)

résultat_de_la_fonction

Le résultat le plus simple d’une fonction est un scalaire. Vous aurez alors une ligne indiquant quel type de donnée est retournée par la fonction:

RETURNS varchar

RETURNS integer

etc…

Mais il y a des retours plus complexes qui sont possibles. En premier, c’est le retour d’un ou plusieurs enregistrements d’une table existante. Par exemple:

CREATE OR REPLACE FUNCTION af_test()
RETURNS setof cercle
AS $$
BEGIN
RETURN QUERY SELECT * FROM cercle WHERE id<8;
END;
$$ LANGUAGE plpgsql;

Cette fonction sélectionne tous les enregistrements de la table « cercle » » avec un identifiant inférieur à 8. Le résultat est:

Ce qui indique qu’on veut des enregistrements en sortie est le terme SETOF. Ici suivi par le nom de la table concernée.

Mais il se peut aussi que le résultat soit une série d’enregistrements calculés lors de l’exécution de la fonction, par exemple si l’on souhaite l’intersection de deux couches. Le résultat n’est ni l’une ni l’autre couche.

On déclare alors

RETURNS SETOF RECORD

est on ajoute une définition des variables qui vont faire partie de cet enregistrement. Il y a plusieurs façons de faire ceci, mais la plus simple est de rajouter dans la liste des paramètres de la fonction les attributs en sortie.

Ceci se fait en définissant les paramètres avec le mot OUT:

CREATE OR REPLACE FUNCTION public.af_union(
tblA character varying,
idA character varying,
geomA character varying,
tblB character varying,
idB character varying,
geomB character varying,
OUT id1 integer,
OUT id2 integer,
OUT the_geom geometry)
RETURNS SETOF record

Dans cet exemple, la fonction qui possède 6 paramètres en entrée, aura comme résultat une série d’enregistrements avec trois attributs: id1, id2 et the_geom.

Codage de la fonction

Il est impossible de décrire ici toutes les possibilités de codage. Voyons les principux oncepts. Le but de réaliser une fonction est de pouvoir manipuler les requêtes SQL. Le langage SQL ne permet pas de traduire un algorithme conditionnel tandis que le PL/pgql est justement fait pour cela.

Trois fonctions conditionnelles principales permettent de déterminer les requêtes SQL à réaliser selon le cas:

la fonction CASE

De type:

CASE nom_variable
WHEN valeurA
THEN résultat_si_oui
ELSE résultat_si_non
END,

la fonction IF

De type:

IF(nom_variable =valeurA, résultat_si_oui , résultat_si_non)

La fonction LOOP

De type:

LOOP
Code à exécuter
END LOOP;

La substitution de variables dans les requêtes

Une des utilisations les plus courantes des fonctions est de stocker des traitements et de pouvoir les exécuter sur des couches différentes.

Dans un article précédent(L’analyse spatiale avec SQL (4)), nous avons vu un exemple de requête pour extraire l’intersection de deux couches appelées cercle et rectangles:

select cercle.id as id1,rectangles.id as id2, st_intersection(cercle.geom,rectangles.geom) as the_geom
from cercle,rectangles
where st_intersects(cercle.geom,rectangles.geom)
group by cercle.id,rectangles.id

Le résultat est une nouvelle couche avec trois attributs: les deux identifiants des couches intersectées et la surface d’intersection. On peut stocker cette requête et chaque fois que nous voulons l’utiliser, la charger et remplacer manuellement les noms des deux couches.

Mais on peut aussi stocker cette requête sous forme d’une fonction, que nous appellerons par exemple mon_intersection, en lui donnant comme paramètres les deux noms de tables à intersecter. On pourra alors rentrer simplement

SELECT * FROM mon_intersection(‘tableA’,’tableB’)

pour obtenir le même résultat.

Passons à l’écriture de la fonction:

Ici on placera le code

END;
$$ LANGUAGE plpgsql;

Nous reprenons les éléments vus plus haut: le retour ce sera un ensemble d’enregistrements nouveaux, les attributs sont déclarés comme paramètres OUT. Et les deux paramètres en entrée sont les noms des deux tables à intersecter.

Le code du select que nous voulons transformer en fonction est:

Nous allons tout ‘abord le mettre entre guillemets pour le transformer en chaîne de texte.

‘ select cercle.id as id1,rectangles.id as id2, st_intersection(cercle.geom,rectangles.geom) as the_geom
from cercle,rectangles
where st_intersects(cercle.geom,rectangles.geom)
group by cercle.id,rectangles.id ‘

Nous allons utiliser l’opérateur de concaténation || pour remplacer cercle par tableA et rectangles par tableB

‘ select ‘||tableA||’.id as id1,’||tableB||’.id as id2, st_intersection(‘||tableA||’.geom,’||tableB||’.geom) as the_geom
from ‘||tableA||’,’||tableB||’
where st_intersects(‘||tableA||’.geom,’||tableB||’.geom)
group by ‘||tableA||’.id,’||tableB||’.id ‘

Au moment de l’exécution, les variables tableA et tableB de la chaîne de caractères seront remplacées par les noms des tables fournies en entrée.

Nous ajoutons une variable dans les déclarations pour le texte de la requête:

DECLARE
rec record;
sql text;

Pour finir, on doit ajouter une boucle qui transforme chaque résultat de la requête en un enregistrement en sortie

FOR rec IN EXECUTE sql

LOOP
id1 := rec.id1;
id2 := rec.id2;
the_geom := rec.the_geom;
RETURN NEXT;
END LOOP;

Ce code indique que chaque fois que la requête sql renvoi un résultat (dans la variable rec), on place les attributs contenus dans rec dans la file de sortie en tant que nouvel enregistrement.

Et voici le code complet:

CREATE OR REPLACE FUNCTION mon_intersection(
tableA character varying,
tableB character varying,
OUT id1 integer,
OUT id2 integer,
OUT the_geom geometry)
RETURNS setof RECORD
AS $$
DECLARE
rec record;
sql text;
BEGIN
sql := ‘ select ‘||tableA||’.id as id1,’||tableB||’.id as id2, st_intersection(‘||tableA||’.geom,’||tableB||’.geom) as the_geom
from ‘||tableA||’,’||tableB||’
where st_intersects(‘||tableA||’.geom,’||tableB||’.geom)
group by ‘||tableA||’.id,’||tableB||’.id ‘

FOR rec IN EXECUTE sql

LOOP
id1 := rec.id1;
id2 := rec.id2;
the_geom := rec.the_geom;
RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql;

Lors de l’exécution on a le résultat:

Notez qu’il faut passer le nom des tables comme du texte, entre apostrophes, et nom comme nom de tables.

Un exemple plus complexe

Dans l’article L’analyse spatiale avec SQL: 5-fonction d’agrégation (Union d’ArcGis) nous avons vu comment écrire la requête SQL pour réaliser cette opération d’agrégation, présente dans ArcGis et absente, pour le moment, dans Postgis.

La requête est:

select *
from
(select cercle.id as id1,rectangles.id as id2,st_intersection(cercle.geom,rectangles.geom) as the_geom
from cercle,rectangles
where st_intersects(cercle.geom,rectangles.geom)
group by cercle.id,rectangles.id
union all
select 0 as id1, rectangles.id as id2,st_difference(rectangles.geom,(
select st_multi(st_union(the_geom) ) as the_geom from (
select st_intersection(cercle.geom,rectangles.geom) as the_geom
from cercle,rectangles
where st_intersects(cercle.geom,rectangles.geom) )
as the_geom)) as the_geom
from rectangles
union all
select cercle.id as id1, 0 as id2,st_difference(cercle.geom,(
select st_multi(st_union(the_geom) ) as the_geom from (
select st_intersection(cercle.geom,rectangles.geom) as the_geom
from cercle,rectangles
where st_intersects(cercle.geom,rectangles.geom) )
as the_geom)) as the_geom
from cercle
)as T1

L’intérêt de garder ceci comme fonction paramétrable est clair.

Voici la fonction correspondante:

AS $BODY$
DECLARE
sql text;
rec record;
BEGIN
sql := ‘select ‘||tblA||’.’||idA||’ as id1,’||tblB||’.’||idB||’ as id2,st_intersection(‘||tblA||’.’||geomA||’,’||tblB||’.’||geomB||’) as the_geom
from ‘||tblA||’,’||tblB||’
where st_intersects(‘||tblA||’.’||geomA||’,’||tblB||’.’||geomB||’)
group by ‘||tblA||’.’||idA||’,’||tblB||’.’||idB|| »;

FOR rec IN EXECUTE sql
LOOP
— Return record
id1 := rec.id1;
id2 := rec.id2;
the_geom := rec.the_geom;
RETURN NEXT;
END LOOP;
sql := ‘select 0 as id1, ‘||tblB||’.’||idB||’ as id2,st_difference(‘||tblB||’.’||geomB||’,(
select st_multi(st_union(the_geom) ) as the_geom from (
select st_intersection(‘||tblA||’.’||geomA||’,’||tblB||’.’||geomB||’) as the_geom
from ‘||tblA||’,’||tblB||’
where st_intersects(‘||tblA||’.’||geomA||’,’||tblB||’.’||geomB||’) )
as the_geom)) as the_geom
from ‘||tblB|| ‘ ‘;

FOR rec IN EXECUTE sql
LOOP
— Return record
id1 := rec.id1;
id2 := rec.id2;
the_geom := rec.the_geom;
RETURN NEXT;
END LOOP;

sql := ‘select ‘||tblA||’.’||idA||’ as id1, 0 as id2,st_difference(‘||tblA||’.’||geomA||’,(
select st_multi(st_union(the_geom) ) as the_geom from (
select st_intersection(‘||tblA||’.’||geomA||’,’||tblB||’.’||geomB||’) as the_geom
from ‘||tblA||’,’||tblB||’
where st_intersects(‘||tblA||’.’||geomA||’,’||tblB||’.’||geomB||’) )
as the_geom)) as the_geom
from ‘||tblA||’ ‘;
FOR rec IN EXECUTE sql
LOOP
— Return record
id1 := rec.id1;
id2 := rec.id2;
the_geom := rec.the_geom;
RETURN NEXT;
END LOOP;
RETURN;

END;
$BODY$;

Par rapport à l’exemple précédent, nous avons pour chaque table en entrée ajouté la possibilité d’indiquer le nom de l’attribut identifiant et du champ géométrie. L’opérateur UNION ALL est remplacé ici par l’exécution séquentielle des 3 boucles FOR qui remplissent la table en sortie.

Si cet article vous a intéressé et que vous pensez qu'il pourrait bénéficier à d'autres personnes, n'hésitez pas à le partager sur vos réseaux sociaux en utilisant les boutons ci-dessous. Votre partage est apprécié !