Fonction renvoyant des données de plusieurs tables

Les fonctions PL/pgSQL étudiées dans les articles précédents envoyaient en sortie des rangées contenant les données d'une seule table. Nous allons maintenant travailler sur un exemple où ce n'est plus le cas.
Soient un ensemble de tables liées par des relations d'intégrité référentielle telles que représentées sur ce schéma:

Les tables frcp, frdepartem et frregions sont présentées ici. Il est question de la table service et de la table employés dans l'article relatif à l' intégrité référentielle. Quant à la table gestion, elle a été introduite dans cet article.
Le challenge consiste à construire une fonction donnant une liste contenant le matricule, le nom, le nom du département de résidence des employés d'un service donné, le nom du service étant transmis en tant que paramètre de la fonction.
Tout d'abord nous plaçons dans une variable code le code du service (service_id) tiré à partir de son nom fourni en input ($1):
SELECT service_id
INTO code
FROM services
WHERE dénomination = $1
Une fois le code récupéré, nous accédons via la table gestion aux matricules et aux noms grâce à un join avec la table employés. Partant de la table employés nous pouvons atteindre le nom du département en effectuant des joins avec les tables frcp et frdepartem. 
Voici la requête qui sera utilisée:
SELECT a.matricule, a.nom, c.departem_nom
FROM employés a, frcp b, frdepartem c, gestion d
WHERE a.code_postal = b.code_postal
AND a.cp_seq = b.cp_seq
AND b.code_departem = c.departem_id
AND a.matricule = d.matricule
AND d.service = code
ORDER BY 3, 1 
Cela étant, différentes possibilités s'offre à nous en ce qui concerne la fonction proprement dite.
Nous pouvons par exemple utiliser un curseur:

CREATE FUNCTION stat1services(text)
RETURNS SETOF refcursor
AS $$
DECLARE
  code services.service_id%TYPE;
  service alias FOR $1;
  cursemp CURSOR (code text) IS
  SELECT a.matricule, a.nom, c.departem_nom
  FROM employés a, frcp b, frdepartem c, gestion d
  WHERE a.code_postal = b.code_postal
  AND a.cp_seq = b.cp_seq
  AND b.code_departem = c.departem_id
  AND a.matricule = d.matricule
  AND d.service = code
  ORDER BY 3, 1;
BEGIN
  SELECT service_id
  INTO code
  FROM services
  WHERE dénomination = service;
  FOR empr IN cursemp (code) LOOP
   RETURN NEXT empr;
  END LOOP;
  RETURN; 
END;
$$ LANGUAGE 'plpgsql';

La variable empr est  créée d'office et ne doit pas être déclarée. chaque rangée lue par le curseur lui est assignée. Le curseur est ouvert automatiquement avec l'instruction FOR.

Résultat:

EDIT: SETOF refcursor ne fonctionne plus. Il faut donc indiquer après SETOF un type correspondant aux rangées émises. Ce type doit être créé comme indiqué plus loin.
SELECT * FROM ... donnera alors un output plus agréable.

Cet output peut-être amélioré car la variable empr possède une sous-structure qui est définie au moment où une valeur lui est assignée:

CREATE FUNCTION stat11services(text)
RETURNS SETOF refcursor
AS $$
DECLARE
  code services.service_id%TYPE;
  service alias FOR $1;
  cursemp CURSOR (code text) IS
  SELECT a.matricule, a.nom, c.departem_nom AS département
  FROM employés a, frcp b, frdepartem c, gestion d
  WHERE a.code_postal = b.code_postal
  AND a.cp_seq = b.cp_seq
  AND b.code_departem = c.departem_id
  AND a.matricule = d.matricule
  AND d.service = code
  ORDER BY 3, 1;
BEGIN
  SELECT service_id
  INTO code
  FROM services
  WHERE dénomination = service;
  RETURN NEXT 'matr | nom                            | département';
  RETURN NEXT '-----+--------------------------------+------------------------------------';
  FOR empr IN cursemp (code) LOOP
   RETURN NEXT empr.matricule || ' | ' || rpad(empr.nom, 30) || ' | ' || empr.département;
  END LOOP;
  RETURN; 
END;
$$ LANGUAGE 'plpgsql';

Regardons ce que ça donne:

EDIT: SETOF refcursor ne fonctionne plus: il faut donc procéder comme ci-après

L'idéal serait de pouvoir indiquer dans la clause RETURNS un type correspondant aux rangées émises. C'est facile quand les rangées émises proviennent d'une seule table, mais ce n'est pas le cas.
Il faut donc créer le type dont on a besoin:

CREATE TYPE emp AS
(matricule integer,
nom char(50),
département char(30))

Nous pouvons maintenant dans la définition de la fonction stat1services remplacer SETOF refcursor par SETOF emp.
Appelons stat12services la nouvelle fonction ainsi obtenue. SELECT * FROM permet alors d'avoir facilement un format de sortie nettement plus convivial:

On peut même se passer de déclarer un curseur:

CREATE FUNCTION stat2services(text)
RETURNS SETOF emp
AS $$
DECLARE
  code services.service_id%TYPE;
  service alias FOR $1;
  empr RECORD;  
BEGIN
  SELECT service_id
  INTO code
  FROM services
  WHERE dénomination = service;
  FOR empr IN
    SELECT a.matricule, a.nom, c.departem_nom
    FROM employés a, frcp b, frdepartem c, gestion d
    WHERE a.code_postal = b.code_postal
    AND a.cp_seq = b.cp_seq
    AND b.code_departem = c.departem_id
    AND a.matricule = d.matricule
    AND d.service = code
    ORDER BY 3, 1
  LOOP
  RETURN NEXT empr;
  END LOOP;
  RETURN; 
END;
$$ LANGUAGE 'plpgsql';

Si nous ne souhaitons pas créer un nouveau type, il est possible d'utiliser la clause RETURNS TABLE associée à RETURN QUERY:

CREATE FUNCTION stat3services(text)
RETURNS TABLE (matricule integer, nom character(50), département character(30))
AS $$
DECLARE
  code services.service_id%TYPE;
  service alias FOR $1;
BEGIN
  SELECT service_id
  INTO code
  FROM services
  WHERE dénomination = service;
  RETURN QUERY SELECT a.matricule, a.nom, c.departem_nom
  FROM employés a, frcp b, frdepartem c, gestion d
  WHERE a.code_postal = b.code_postal
  AND a.cp_seq = b.cp_seq
  AND b.code_departem = c.departem_id
  AND a.matricule = d.matricule
  AND d.service = code
  ORDER BY 3, 1;
END;
$$ LANGUAGE 'plpgsql';

Fonction PL/pgSQL retournant une table

Nous allons montrer sur un exemple comment utiliser la clause
RETURNS TABLE
associée à
RETURN QUERY
permet d'améliorer l'output d'une fonction PL/pgSQL renvoyant un ensemble de rangées.

Pour ce faire, créons la fonction fdépenses:

CREATE FUNCTION fdépenses(text)
RETURNS TABLE (ref char(9), date_paie date, montants numeric(13,12), détail character varying(50)) AS $$
DECLARE
  code dépenses.code_u%TYPE;
  usage alias for $1;
BEGIN
  SELECT code_u
  INTO code
  FROM utilisations
  WHERE signification = usage;
  RETURN QUERY SELECT référence, date_exec, montant, détails
  FROM dépenses WHERE code_u =  code  ORDER BY référence;        
END;
$$ LANGUAGE 'plpgsql';

Attention les noms des colonnes figurant dans la clause RETURNS TABLE doivent être différents des noms qui apparaissent dans le query. Si par exemple en ligne 2 on écrit 'montant' (sans s), aucun problème n'apparaît à la création de la fonction. Par contre à l'utilisation:

Supposons que tout est en ordre, donc que la fonction a été créée avec 'montants' écrit en ligne 2.
La fonction est maintenant utilisable:

Caramba! Ça ne fonctionne pas: malgré le RETURNS TABLE l'output est toujours aussi calamiteux.

En fait il faut encore faire  SELECT * FROM:

Et voilà!

Pour avoir la liberté de donner aux colonnes de la table affichée en output les mêmes noms que ceux des colonnes de la table d'origine (dépenses), il suffit de qualifier le nom des colonnes dans le query, comme ceci:

RETURN QUERY SELECT a.référence, a.date_exec, a.montant, a.détails
FROM dépenses a WHERE code_u =  code  ORDER BY référence;    

ou alors nous pouvons utiliser:

RUN QUERY EXECUTE chaîne de commande

Essayons avec:

RETURN QUERY EXECUTE 'SELECT référence, date_exec, montant, détails
FROM dépenses WHERE code_u = code ORDER BY référence';

Lorsqu'une commande est exécutée à l'intérieur d'une fonction PL/pgSQL, chaque variable apparaissant dans la commande  est traitée comme un paramètre auquel est assigné la valeur de la variable. C'était le cas auparavant pour la variable code. Ici ce n'est plus le cas: la commande est exécutée à l'extérieur de la fonction et code est considéré comme le nom d'une colonne.

Ceci:

RETURN QUERY EXECUTE 'SELECT référence, date_exec, montant, détails
FROM dépenses WHERE code_u = ' || code || ' ORDER BY référence'; 

conduit à une autre erreur:

On peut contourner le problème comme ceci:

RETURN QUERY EXECUTE 'SELECT référence, date_exec, montant, détails 
FROM dépenses WHERE code_u::numeric = ' || code || ' ORDER BY référence';  

ou le résoudre comme cela:

RETURN QUERY EXECUTE 'SELECT référence, date_exec, montant, détails 
FROM dépenses WHERE code_u = ' || quote_literal(code) || ' ORDER BY référence';  

mais le plus simple est quand même d'éviter les concaténations comme ci-après:

CREATE FUNCTION f2dépenses(text)
RETURNS TABLE (ref char(9), date_exec date, montant numeric(13,12), détails character varying(50))
AS $$
DECLARE
  code dépenses.code_u%TYPE;
  usage alias for $1;
BEGIN
  SELECT code_u
  INTO code
  FROM utilisations
  WHERE signification = usage;
  RETURN QUERY EXECUTE 'SELECT référence, date_exec, montant, détails
  FROM dépenses WHERE code_u = $1 ORDER BY référence' USING code;
END;
$$ LANGUAGE 'plpgsql';

Formater des rangées dans une fonction PL/pgSQL

Dans le billet précédent nous nous sommes plaints de l'output calamiteux offert par les fonctions PL/pgSQL lorsqu'elles retournaient un ensemble de rangées:

EDIT: pour une fonction telle que celle-ci, SELECT * FROM fedépenses('Santé') donnera un bel output à condition d'avoir dans la clause RETURNS le type 'dépenses' et non pas 'refcursor' qui ne fonctionne plus. C'est possible car le retour de la fonction est constitué de rangées complètes (avec toutes les colonnes) de la seule table dépenses.

Les fonctions de formatage utilisées par un client psql (où un autre client)  ne sont pas accessibles depuis l'intérieur d'une procédure stockée sur le serveur (c'est le cas de la procédure PL/pgSQL utilisée ici). Donc pour améliorer l'output nous devons recourir aux fonctions PostgreSQL de formatage des chaînes.
Pour ce faire, remplaçons la fonction fedépenses telle que définie auparavant par celle-ci:

CREATE FUNCTION fe4dépenses (text)
RETURNS SETOF refcursor AS $$
DECLARE
 code dépenses.code_u%TYPE;
 usage alias for $1;
 l integer;
 fedépenses refcursor;
 d RECORD;
BEGIN
 SELECT code_u
 INTO code
 FROM utilisations
 WHERE signification = usage;
 SELECT max(length(détails))
 INTO l
 FROM dépenses
 WHERE code_u = code;
 IF l < 7 OR l is NULL THEN 
    l :=7;
 END IF;
 OPEN fedépenses FOR SELECT * FROM dépenses WHERE code_u = code ORDER BY référence;
 LOOP 
   FETCH NEXT FROM fedépenses INTO d;
   EXIT WHEN NOT FOUND;
   IF d.détails IS NULL THEN
      d.détails := '';
   END IF;
   RETURN NEXT d.référence||' | '||d.date_exec||' | '||to_char(d.montant, '9G999D99')||' | '||rpad(d. détails, l)||' | '||d.code_u;
 END LOOP;
 CLOSE fedépenses;
 RETURN;
END;
$$ LANGUAGE 'plpgsql';

L'utilisation de la clause
RETURNS SETOF dépenses
conduit à l'erreur
ERROR: RETURN NEXT must specify a record or row variable in function returning row:

D'où l'utilisation de
RETURNS SETOF refcursor

La variable d de type RECORD dans la quelle on met toute la rangée possède une sous-structure qui est déterminée au moment du fetch (lorsqu'une valeur lui est assignée).
Nous aurions pu aussi déclarer cette variable comme ceci:
d dépenses%ROWTYPE
Dans ce cas la sous-structure est définie au niveau du DECLARE
Mais dans les deux cas, on peut retourner les différents champs individuels et transformer ceux-ci à l'aide des fonctions de formatage PostgreSQL.
Ainsi 'montant' est transformé en une chaîne de caractères de longueur définie. Le motif utilisé dépend évidemment des montants concernés.
De même 'détails' est porté à une longueur fixe déterminée par
SELECT max(length(détails))
INTO l
FROM dépenses
WHERE code_u = code;
IF l < 7 OR l is NULL THEN 
  l :=7;
END IF;
Les différents champs individuels sont ensuite concaténés entre eux(opérateur ||) et aussi avec le séparateur  '  |  ' de manière à ce qu'une chaîne unique soit renvoyée.
Attention: si un seul des champs individuels présent dans la chaîne renvoyée est NULL, alors toute la chaîne est renvoyée avec la valeur NULL.
D'où la présence du test:
IF d.détails IS NULL THEN
   d.détails := '';
END IF;
(à supposer que détails soit le seul champ susceptible d'être NULL).
Voici le résultat:

On peut pousser l'amélioration encore plus loin en introduisant avant LOOP le code:

RETURN NEXT 'référence |    date    |   montant |'||rpad(' détails', l+1)|| ' | '||'code';
RETURN NEXT '----------+------------+-----------+'||lpad('-', l+2, '-') || '+'||'------';

Ce qui donne:

Pas mal, non?

Remarquons que nous n'étions pas obligé de reprendre toutes les colonnes dans l'output. Nous aurions pu par exemple nous passer de la colonne code.

Il est possible de modifier de la même façon la fonction fe2dépenses et fe3dépenses du billet précédent.

Curseurs et pgsql

Dans le billet précédent, nous avons parlé de l'utilisation des variables dans des instructions SQL.
Nous avons regretté le fait que
SELECT ...
INTO ...
sert à créer une nouvelle table et non pas à donner une valeur à une variable.
En effet nous aurions aimé pouvoir exécuter
SELECT code_u
INTO :code
FROM utilisations
WHERE signification = 'Santé';
et ensuite
SELECT *
FROM dépenses
WHERE code_u = :'code';
Mais cela ne fonctionne pas.

D'où l'idée de créer avec le langage PL/pgSQL une procédure (appelée par une fonction), procédure dans laquelle SELECT... INTO... fonctionne comme nous le voulons.
Voici les instructions qui nous ont servi à créer cette fonction:

CREATE FUNCTION fedépenses (text)
RETURNS SETOF dépenses AS $$
DECLARE
code dépenses.code_u%TYPE;
usage alias for $1;
fedépenses refcursor;
dépenses RECORD;
BEGIN
SELECT code_u
INTO code
FROM utilisations
WHERE signification = usage;
OPEN fedépenses FOR SELECT * FROM dépenses WHERE code_u = code ORDER BY référence;
 LOOP 
 FETCH NEXT FROM fedépenses INTO dépenses;
   EXIT WHEN NOT FOUND;
 RETURN NEXT dépenses;
END LOOP;
CLOSE fedépenses;
RETURN;
END;
$$ LANGUAGE 'plpgsql';

Le 'RETURNS SETOF' est nécessaire si on veut que la fonction puisse retourner un ensemble de rangées.
La variable code est définie avec le même type que le champ dépenses.code_u.
fedépenses est déclaré en tant que curseur non lié. Il est lié à un query seulement lors de son ouverture.
Pour le reste, assez classiquement nous passons en revue avec FETCH les différentes rangées lisibles par le curseur et ce jusqu'à la dernière.
Après avoir créé la fonction, il reste à la tester:

L'output est calamiteux (EDIT: voir plus loin), mais nous avons ce que nous voulons.
Nous avons également testé une variante de la fonction précédente:

CREATE FUNCTION fe2dépenses (text)
RETURNS SETOF dépenses AS $$
DECLARE
code dépenses.code_u%TYPE;
usage alias for $1;
fedépenses CURSOR (code text) IS 
 SELECT * FROM dépenses WHERE code_u = code ORDER BY référence;
BEGIN
SELECT code_u
INTO code
FROM utilisations
WHERE signification = usage;
FOR dépenses IN fedépenses (code) LOOP
RETURN NEXT dépenses;
 END LOOP;
RETURN;
END;
$$ LANGUAGE 'plpgsql';

Le curseur est maintenant déclaré lié à un query paramétré. Le curseur est ouvert automatiquement avec l'instruction FOR (il ne doit pas être ouvert avant) et il est fermé automatiquement lorsque la boucle se termine. Chaque rangée lue par le curseur est successivement assignée au record dépenses qui est créé automatiquement et qui n'existe que pendant la durée de la boucle.
Et voici le test:

Il n'est d'ailleurs pas nécessaire de définir un curseur. Ceci fonctionne également:

CREATE FUNCTION fe3dépenses (text)
RETURNS SETOF dépenses AS $$
DECLARE
code dépenses.code_u%TYPE;
usage alias for $1;
d dépenses%ROWTYPE;
BEGIN
SELECT code_u
INTO code
FROM utilisations
WHERE signification = usage;
 FOR d IN SELECT * FROM dépenses WHERE code_u = code ORDER BY référence
 LOOP 
 RETURN NEXT d;  
END LOOP;
RETURN;
END;
$$ LANGUAGE 'plpgsql';

Mais dans tous les cas, l'output est toujours aussi calamiteux.

EDIT: pour cette fonction (fe3dépenses), SELECT * FROM permet d'éviter l'output calamiteux. Il en est de même pour les deux fonctions précédentes à condition que le type indiqué dans la clause RETURNS soit 'dépenses' et non 'refcursor' qui ne fonctionne plus. Ceci est possible car la sortie de ces fonctions est constituée de rangées complètes (avec toutes les colonnes) d'une seule table.

Alors pourquoi ne pas se servir de PL/pgSQL uniquement pour créer un curseur et utiliser celui dans le terminal psql?
Pour cela, créons la fonction ocdépenses:

CREATE FUNCTION ocdépenses (refcursor, text)
RETURNS refcursor AS $$
DECLARE
usage alias for $2;
code dépenses.code_u%TYPE;
BEGIN
SELECT code_u
INTO code
FROM utilisations
WHERE signification = usage;
OPEN $1 for SELECT * FROM dépenses WHERE code_u = code ORDER BY référence;
RETURN $1;
END;
$$ LANGUAGE 'plpgsql';

Testons:

Ah oui: le curseur n'est vivant que pendant la durée d'une transaction. Or la transaction qui a été implicitement ouverte au lancement du query
select ocdépenses('curs1', 'Carburant')
est fermée automatiquement lorsque celui-ci s'est exécuté.
Corrigeons le tir en ouvrant explicitement la transaction avec BEGIN:

COMMIT termine la transaction et ferme donc le curseur.

Certains diront que l'on pouvait arriver à ce résultat avec:

select *
from dépenses
where code_u =
(select code_u
from utilisations
where signification = 'Carburant')
;
et même avec
select A.*
from dépenses A natural join utilisations B
where signification = 'Carburant'
;

A cela nous répondons:

1. c'est beaucoup moins amusant
2. le SQL se trouve alors au niveau du client ce qui dans le cas de traitement lourd peut présenter certains désavantages (ce n'est pas le cas ici)
3. le but était de parler des variables
4. comment alors présenter nos amis les curseurs?

Et les curseurs offrent de nombreuses possibilités. On n'est pas obligé de se limiter à 'fetch all'. Par exemple, si curs1 a été défini pour 'Grande surface':

etc...

Suite au 'fetch all', le curseur est positionné après la dernière rangée. C'est pourquoi l'instruction suivante nous amène à l'avant-dernière rangée. Le deuxième 'fetch relative -2' montre que l'on remonte bien de 2 positions.

Variables dans psql

Il nous est loisible de définir et d'utiliser dans un terminal psql des variables sur le modèle des host variables qui existent pour du SQL embarqué dans un programme C (ou cobol).
Illustrons ce fait à l'aide d'instruction portant sur la table opérations définie ici:

(Remarquons que la méta-commande \pset numericlocale on concerne uniquement le format de sortie des nombres)
Autre exemple:

Malheureusement l'instruction
SELECT ...
INTO ...
sert au niveau de psql à créer une nouvelle table et non pas à stocker le résultat d'une requête dans une variable (contrairement à ce qui existe pour le SQL embarqué).
Il est cependant possible de contourner le problème comme dans cet exemple:

Les tables dépenses et utilisations ont été définies pour l'article Le piège du null. Depuis le libellé 'Médecin' a été remplacé par 'Santé' et un code a été attribué à toutes les dépenses.

Terminons par un exemple montrant les avantages de l'utilisation de telles variables.
Modifions comme ceci le fichier bilan.sql figurant dans ce billet:

\set QUIET
\set an 2013
\set titre 'Bilan ':an
\pset numericlocale on
\pset footer off
\pset linestyle u
\pset title :titre
\pset border 2
\o | awk 'NR==4 {y=$0};/SOUS-TOTAL/ {print y;print;print y};!/SOUS-TOTAL/'
SELECT mois_n ||'-' || mois AS "Mois",  '' AS " ", reference,
date_exec::text, crédit, débit,
  (SELECT SUM(solde)
  FROM opérationsv B
  WHERE B.reference <= A.reference
  AND B.an = A.an
  AND B.mois_n = A.mois_n) AS solde,
  contrepartie AS "usage"
FROM opérationsv A
WHERE an = :'an'
UNION
SELECT mois_n || '-' || mois, 'SOUS-TOTAL', '', '',
SUM(crédit) AS crédit, SUM(débit) AS débit, SUM(solde) AS solde,
'' AS " "
FROM opérationsv
WHERE an = :'an'
GROUP BY mois_n, mois
UNION
SELECT 'Ensemble', 'TOTAL', '', '',
SUM(crédit) AS crédit, SUM(débit) AS débit, SUM(solde) AS solde,
'' AS " "
FROM opérationsv
WHERE an = :'an'
ORDER by 1, 2 , 3;
\pset footer
\pset title
\pset border 1
\unset QUIET
\o

Comparant avec le fichier original, nous constatons que pour passer du bilan 2013 au bilan 2014, il nous suffit maintenant de remplacer 2013 par 2014 en un seul endroit (au lieu de 4).

Une autre possibilité est de supprimer \set an 2013 du fichier bilan.sql: le même fichier d'instructions pourra alors servir pour n'importe quelle année (il faut bien sûr initialiser la variable an):