Fonction bilan

A la fin du billet Variables dans psql, nous avions montré comment impacter le résultat d'instructions SQL (contenues dans un fichier) par initialisation d'une variable.
Ainsi

bdtest=> \set an 2013 
bdtest=> \i bilan.sql

donnera en ouput le bilan de 2013 pour autant que les instructions SQL de bilan.sql contiennent des clauses WHERE du genre:

WHERE an = :'an'

Nous pouvons aussi créer un fichier bilanx contenant la commande

psql bdtest -v an=$1 -f bilan.sql

L'année du bilan sera de la sorte transmise en paramètre au programme.
Il faut rendre bilanx exécutable:

toto@aldebaran:~/SQL/bdtest$ chmod +x bilanx

avant de pouvoir l'utiliser:

bilan.sql qui contient l'ensemble des instructions SQL se trouve sur le poste client (où est exécuté bilanx).
Nous voudrions ne pas avoir besoin de fichier contenant les instructions SQL et que celles-ci se trouvent sur le serveur.
Pour ce faire il nous faut créer une fonction (PL/pgsql). Si nous voulons retourner le même genre de bilan que dans le billet Sous-totaux dans terminal psql, cette fonction sera par exemple créée par:

DROP function fbilan (integer);
CREATE FUNCTION fbilan (integer)
RETURNS TABLE (mois text, "sous-total" text, ref char(9), datexec text, cred numeric(13, 2), deb numeric(13, 2), solde numeric(13,2), usage char(5))
AS $$
DECLARE
année alias for $1;
BEGIN
RETURN QUERY  
SELECT (A.mois_n ||' ' || A.mois), ' ', reference,
date_exec::text, crédit, débit,
  (SELECT SUM(B.solde)
  FROM opérationsv B
  WHERE B.reference <= A.reference
  AND B.an = A.an
  AND B.mois_n = A.mois_n),
  contrepartie
FROM opérationsv A
WHERE an::numeric = année
UNION
SELECT (C.mois_n ||' ' || C.mois), 'SOUS-TOTAL', '', '',
SUM(crédit), SUM(débit), SUM(C.solde),
'' 
FROM opérationsv C
WHERE an::numeric = année
GROUP BY C.mois_n, C.mois
UNION
SELECT 'TOTAL', '', '', '',
SUM(crédit), SUM(débit), SUM(D.solde),
''
FROM opérationsv D
WHERE an::numeric = année
ORDER by 1, 2 , 3;
END;
$$ LANGUAGE 'plpgsql';

Un problème se pose: la commande

psql bdtest -v an=$1 -c 'select * from fbilan(:an)'

conduit à une erreur:

alors que ceci:

fonctionne et donne le résultat escompté après un appui sur ENTER.

L'instruction  SQL constitue ce qui est appelé un here-document. Quelques éléments concernant cette notion se trouve dans le billet Les secrets de cat.

En conséquence, créons le fichier fbilax:

psql bdtest -v an=$1 -P numericlocale -P footer \
-P border=2 -P linestyle=u \
-P title='Bilan '$1 <<FIN
SELECT * 
FROM fbilan(:an)
; 
FIN

Nous le rendons exécutable, puis nous le testons:

Ça fonctionne, mais si nous voulons améliorer l'output nous avons intérêt à utiliser awk.
Pour ce faire, créons le fichier fbilanx:

fichier=$(mktemp)
psql bdtest -v an=$1 -P numericlocale -P footer \
-P border=2 -P linestyle=u \
-P title='Bilan '$1 -o $fichier <<FIN
SELECT * FROM fbilan(:an); 
FIN
echo
awk 'NR==4 {y=$0};/SOUS-TOTAL/ {print y;print;print y};!/SOUS-TOTAL/' $fichier

dont l'utilisation après qu'il ait été rendu exécutable, nous fournit le résultat souhaité:

Grâce à l'option -o nous écrivons l'output de psql dans un fichier temporaire (créé par mktemp). Ce fichier temporaire sert d'input à awk. La ligne 4 (qui vient après le nom des colonnes) est alors mise en mémoire (première instruction awk) en vue d'une impression avant et après les lignes contenant le mot 'SOUS-TOTAL'. Pour les autres lignes, l'action par défaut (print) est exécutée. Le billet awk (introduction) contient tout ce qui est nécessaire pour la compréhension de ce qui précède.

Notons que si on est déjà dans un terminal psql, il n'est nul besoin de sortir pour procéder: