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';
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.
