Tubes dans psql

Un terminal psql est un terminal connecté à une base de données PostgreSQL qui permet d'exécuter des instructions sql.

On peut aussi y introduire des méta-commandes: commandes précédées d'une barre oblique inversée (backslash). Certaines méta-commandes ont déjà été présentées dans ce blog telle

\o [FICHIER]

qui envoie le résultat des requêtes ultérieures dans le fichier FICHIER.

Mais cette méta-commande permet également d'envoyer les résultats des requêtes dans un tube (pipe). Dans ce billet, nous allons montrer comment tirer parti de cette dernière possibilité.

Connectons nous à notre base de données de test et retrouvons grâce à la méta-commande \dS les caractéristiques de la table opération:

Nous avons aussi créé une vue opérationv avec l'instruction

CREATE VIEW opérationv 
AS
SELECT jour, seq,  
CASE
 WHEN montant > 0 THEN  montant  
 ELSE 0 
END AS crédit, 
CASE
 WHEN montant < 0 THEN  -montant  
 ELSE 0 
END AS débit, 
contrepartie 
FROM opération ;

Avec cette vue, la dernière requête du billet précédent, qui paraissait monstrueuse, prend tout de suite un visage plus sympathique:

SELECT jour, sum(crédit) AS crédit,
sum(débit) AS débit, 
sum(crédit) - sum(débit) AS solde
FROM
opérationv
GROUP BY jour
UNION
SELECT 'TOTAL' AS jour, sum(crédit) AS crédit,
sum(débit) AS débit, 
sum(crédit) - sum(débit) AS solde
FROM
opérationv
ORDER BY jour
;

Nous avons placé cette requête dans le fichier bilan.sql.
Exécutons la:

Nous retrouvons bien le même résultat que précédemment.
Avant de procéder avec bilan.sql, nous avons exécuté deux méta-commandes. La méta-commande "\pset numericlocale" a déjà été commentée précédemment.
Quant à la méta-commande "\pset footer off", il suffit de jeter un coup d'oeil distrait sur le résultat situé à la fin du billet précédent pour saisir quel est son objet.

On voudrait améliorer le résultat affiché et obtenir par exemple quelque chose comme:

   jour   |  crédit  | débit  | solde  
----------+----------+--------+--------
 2011-255 | 1.460,45 | 673,29 | 787,16
 2011-256 |    57,49 |  48,00 |   9,49
 2011-257 |        0 |  28,27 | -28,27
=======================================
 TOTAL    | 1.517,94 | 749,56 | 768,38

Pour arriver à nos fins, nous allons à l'aide d'un tube envoyer le résultat vers la commande:

awk '/^ TOTAL/ {while (a++<=length($0)) x=x "="; print x }; 1'

Un billet entier est consacré dans ce blog à awk.

Le script de la commande awk (ce qui est compris entre les deux apostrophes) comprend ici deux instructions (séparées par le point-virgule qui est devant le 1).

Rappelons qu'une instruction awk est constituée d'une condition suivie de une où plusieurs actions qui s'exécutent seulement si la condition est vérifiée.

awk va lire une à une les différentes lignes du résultat et effectuer (éventuellement) les actions comprises dans les instructions.

Analysons la première instruction:

/^ TOTAL/ {while (a++<=length($0)) x=x "="; print x }

Elle comprend une condition suivie de deux actions placées entre crochets et séparées par un point-virgule.

En rouge la condition: avoir le mot "TOTAL" en deuxième position.

En bleu l'action 1: construire avec "=" une chaîne de longueur égale à la longueur de la ligne lue (qui se trouve dans la variable $0).

En vert l'action 2: imprimer (envoyer vers la sortie standard) la chaîne qui vient d'être construite.  

La deuxième instruction est très simple: elle comprend simplement la condition "1" qui est toujours vérifiée et réalise donc à chaque fois l'action par défaut "print".

Cette deuxième instruction sert simplement à imprimer (envoyer vers la sortie standard) tout ce qui a été lu.
Ajoutons en tête du fichier bilan.sql, la méta-commande:
\o | awk '/^ TOTAL/ {while (a++<=length($0)) x=x "="; print x }; 1'
ce qui aura pour effet d'envoyer via le tube " | " l'output de la requête sql vers awk.
Et pour remettre les chose en état après exécution du query, terminons le fichier avec
\o
Et voilà:

Il y a plus simple.
Par exemple envoyer la sortie vers

awk 'NR==2 {y=$0};/^ TOTAL/ {print y};1'

Cette fois le script awk comprend trois instructions.
Instruction 1: si la ligne lue est la ligne 2, la sauvegarder dans y
Instruction 2: si la ligne lue contient "TOTAL" en position 2, imprimer y
Instruction 3: imprimer de manière inconditionnelle la ligne lue.
Et voici le résultat obtenu:

Il est possible d'améliorer encore le résultat (pour par exemple afficher un titre), mais cela va compliquer (un tout petit peu) le script awk. Pour plus de clarté, plaçons le script modifié dans un fichier que nous appelons bilan.awk (les conditions sont en rouge):

NR == 1 {
  while (a++<=length($0)/2-5) x=x " "
  print "\n" x "Bilan" "\n" x "+++++" "\n"
}
NR == 2 {
  y=gensub(/-/,"=","G")
}
/^ TOTAL/ {
   print y 
}
1

Instruction 1: elle comprend deux actions conditionnées par NR == 1, donc qui s'exécutent seulement à la lecture de la première ligne.
Action 1: construction d'une chaîne d'espaces dans le but de centrer le mot "Bilan"
Action 2: impression du titre ("\n" est le caractère de contrôle nouvelle ligne)
Instruction 2: on sauvegarde dans y la ligne lue (ligne 2) dans laquelle on a remplacé tous les "-" par des "=".
Les deux instructions restantes sont inchangées.

Plaçons en tête du fichier bilan.sql la méta-commande:

\o | awk -f bilan.awk

Testons le résultat:

Si nous voulons envoyer le résultat obtenu vers un fichier bilan.txt tout en gardant l'affichage à l'écran, il suffit d'utiliser un deuxième tube:
\o | awk -f bilan.awk | tee bilan.txt
Il nous est bien sûr loisible de ne rien envoyer vers l'écran:
\o | awk -f bilan.awk > bilan.txt