Mais cette fois nous allons partir des notions de base et entrer dans plus de détails.
psql est une commande permettant de se connecter à une base de données PostgreSQL.
Ainsi la commande
psql bdtest
ouvre un terminal interactif connecté à la base de données bdtest:
Bien sûr pour que cette commande simplifiée fonctionne il faut notamment qu'il existe un utilisateur PostgreSQL appelé toto et que le serveur PosgreSQL se trouve sur la même machine que le client.
Au début le seul utilisateur PostgreSQL existant est postgres.
Pour démarrer il faut d'abord devenir postgres et créer l'utilisateur PostgreSQL toto en lui donnant le privilège de créer des bases de données:
Etant redevenu toto après exit, il nous est alors loisible de créer bdtest avec la commande createdb.
Bref, tout cela est très loin: bdtest existe depuis longtemps et contient maintenant de multiples données qui ont déjà été présentées et utilisées dans le cadre de ce blog, notamment les tables utilisations et dépenses créées par ces instructions:
CREATE TABLE utilisations
(code_u CHAR(2) PRIMARY KEY,
signification VARCHAR(20))
;
CREATE TABLE dépenses
(référence CHAR(9) PRIMARY KEY,
date_exec DATE,
montant NUMERIC(13,2),
détails VARCHAR(50),
code_u CHAR(2) REFERENCES utilisations)
;
Précisons encore que si nous ne sommes pas (plus) toto, il nous est possible de se connecter en tant que toto avec la commande
psql bdtest toto
(Si les fichiers pg_hba.conf et pg_ident.conf ont été correctement configurés)
Dans le terminal interactif nous pouvons exécuter des commandes ou entrer des instructions SQL telles que:
SELECT *
FROM dépenses
WHERE détails = 'Docteur Lebon'
;
Si cette instruction se trouve déjà dans un fichier (par exemple lebon.sql), il n'est nul besoin de la retaper. Il suffit de lancer la commande:
\i lebon.sql
(lebon.sql doit se trouver là où on est, c'est-à-dire dans ~/SQL/bdtest)
Si nous redirigeons l'entrée standard vers le fichier lebon.sql:
psql bdtest < lebon.sql
nous n'ouvrons pas alors de terminal interactif. L'instruction est exécutée, puis la liaison vers bdtest est coupée:
Autre possibilité:
psql bdtest <<FIN
redirige l'entré standard vers le document qui se trouve ici (here-document), donc vers ce qui sera tapé à l'écran (jusqu'au mot FIN):
Le système attend la saisie suivante.
Après envoi du mot FIN, le résultat s'affiche:
Mais quel est l'avantage de cette procédure par rapport au terminal interactif, puisqu'il faut quand même saisir l'instruction SQL?
L'avantage est que cette procédure peut être utilisée dans un script:le here-document est alors dans le script lui-même.
Soit le script lebonx:
#!/bin/bash
echo
psql bdtest <<FIN
SELECT *
FROM dépenses
WHERE détails = 'Docteur Lebon'
FIN
que nous avons rendu exécutable (et qui se trouve dans ~/SQL/bdtest).
Exécutons le:
Nous pouvons passer en paramètre la valeur du détail demandé, comme dans le script détailx:
#!/bin/bash
echo $1
echo
psql bdtest -v détails="$1" <<FIN
SELECT *
FROM dépenses
WHERE détails = :'détails'
FIN
L'option -v dans la commande crée la variable psql détails avec la valeur $1.
Procédons:
Nous aimerions maintenant un script qui établisse le relevé des dépenses pour une certaine catégorie. Nous ignorons le code lié à cette catégorie: donc on veut passer en clair le nom de cette catégorie: par exemple 'Santé' pour les dépenses de santé et non '01'.
Considérons le script dépensex:
#!/bin/bash
psql bdtest -q \
-v signification="$1" <<FIN
\pset numericlocale
\pset border 2
\pset linestyle u
\pset footer
\pset title 'Dépenses $1 \n'
\echo
SELECT code_u
FROM utilisations
WHERE signification = :'signification'
\gset
SELECT référence, date_exec::text as date, détails, montant
FROM dépenses
WHERE code_u = :'code_u'
UNION
SELECT 'TOTAL', '', '', sum(montant)
FROM dépenses
WHERE code_u = :'code_u'
order by 1
FIN
Nous avons expliqué ici comment utiliser la commande \gset
L'option -q permet d'éliminer les différents messages informatifs résultant des commandes \pset:
Title is "Dépenses Grande surface".
Showing locale-adjusted numeric output.
Border style is 2.
Line style is unicode.
Si le détail passé en paramètre n'existe pas, d'autres messages polluent l'output:
no rows returned for \gset
ERROR: syntax error at or near ":"
LINE 3: WHERE code_u = :'code_u'
^
Ces messages transitent par le canal des erreurs (canal 2). Pour les éliminer nous ajoutons les instructions suivantes au début script:
exec 6>&2
exec 2>/dev/null
Tout d'abord nous sauvegardons les caractéristiques du canal 2 dans le canal 6, ensuite nous redirigeons le canal 2 vers le néant c'est-à-dire /dev/null.
(Nous avons déjà parlé des redirections ici )
(Nous avons déjà parlé des redirections ici )
En fin de script nous avons:
exec 2>&6 6>&-
(A partir du canal 6, remise du canal 2 dans son état initial; fermeture du canal 6)
D'autre part nous voudrions nous servir de awk pour séparer la ligne 'TOTAL' des autres. Pour ce faire nous envoyons l'output de psql dans un fichier temporaire (créé par mktemp) qui sert d'output à awk (\o | awk ... est inopérant dans ce contexte).
Le script définitif est donc:
#!/bin/bash
fichier=$(mktemp)
fichier=$(mktemp)
exec 6>&2
exec 2>/dev/null
psql bdtest toto -q \
-v signification="$1" <<FIN
\pset numericlocale
\pset border 2
\pset linestyle u
\pset footer
\pset title 'Dépenses $1 \n'
\echo
\o $fichier
\o $fichier
SELECT code_u
FROM utilisations
WHERE signification = :'signification'
\gset
SELECT référence, date_exec::text as date, détails, montant
FROM dépenses
WHERE code_u = :'code_u'
UNION
SELECT 'TOTAL', '', '', sum(montant)
FROM dépenses
WHERE code_u = :'code_u'
order by 1
FIN
exec 2>&6 6>&-
awk 'NR==5 {y=$0};/TOTAL/ {print y};1' $fichier
awk sauvegarde la ligne 5 (instruction 1) pour l'imprimer si la ligne lue contient 'TOTAL' (instruction 2), ensuite toutes les lignes lues sont imprimées puisque la condition 1 est toujours vraie (instruction 3)
Testons:
Testons:
Si le libellé n'existe pas ('grande surface'), il n'y a aucun output (les messages d'erreurs ont disparus).
Peut-être on préférerait avoir ceci:
Dans ce cas il suffit d'ajouter dans le here-document (avant le premier SELECT) la meta-commande
\set code_u
ce qui initialise la variable code_u. De ce fait l'instruction SQL qui vient après \gset ne se plante plus.
Certains pourraient dire que les scripts présentés ici ne sont pas des scripts psql mais des scripts bash qui appellent la commande psql.
Certes, mais l'emploi de la ligne shebang
#!/usr/bin/psql bdtest -f
conduit invariablement à une erreur.
Nous avons trouvé ici un shebang qui fonctionne pour psql:
--() { :; }; exec psql -f "$0"
Certains pourraient dire que les scripts présentés ici ne sont pas des scripts psql mais des scripts bash qui appellent la commande psql.
Certes, mais l'emploi de la ligne shebang
#!/usr/bin/psql bdtest -f
conduit invariablement à une erreur.
Nous avons trouvé ici un shebang qui fonctionne pour psql:
--() { :; }; exec psql -f "$0"
Ainsi le script détailpx
--() { :; }; exec psql bdtest -v détails="$1" -f "$0"
\echo :détails
\echo
SELECT *
FROM dépenses
WHERE détails = :'détails'
donne le même résultat que le script détailx présenté plus haut.
Cependant dès l'instant où la commande psql est terminée, nous quittons le script. Il est impossible de faire transiter dans le même script l'output du SELECT par awk comme auparavant.
Si nous partons du script dépensepx:
--() { :; }; exec psql bdtest -q -v signification="$1" -f "$0"
\pset numericlocale
\pset border 2
\pset linestyle u
\pset footer
\set titre 'Dépenses ':signification
\pset title :titre'\n'
\set code_u
\echo
SELECT code_u
FROM utilisations
WHERE signification = :'signification'
\gset
SELECT référence, date_exec::text as date, détails, montant
FROM dépenses
WHERE code_u = :'code_u'
UNION
SELECT 'TOTAL', '', '', sum(montant)
FROM dépenses
WHERE code_u = :'code_u'
order by 1
et que nous voulons apporter les mêmes améliorations que pour le script dépensex, nous devons l'appeler à partir du script bash dépense0x
#!/bin/bash
fichier=$(mktemp)
exec 7>&1
exec 1>$fichier
exec 6>&2
exec 2>/dev/null
./dépensepx "$1"
exec 1>&7 7>&-
awk 'NR==6 {y=$0};/TOTAL/ {print y};1' $fichier
exec 2>&6 6>&-
Comme auparavant nous redirigeons la sortie des erreurs (canal 2) vers le néant.
De plus nous redirigeons la sortie standard (canal 1) vers le fichier temporaire créé, de sorte que toute la sortie du script dépensepx s'effectuera vers ce fichier. Bien sûr, il faut revenir à la situation initiale avant l'exécution de awk pour que l'output de ce dernier soit dirigé vers l'écran. Remarquons que awk sauvegarde la ligne 6 au lieu de la 5 comme auparavant.
Explication:
La commande \o $fichier utilisée dans le script dépensex envoie dans $fichier le résultat des instructions SQL.
En cas de redirection de la sortie standard, c'est la totalité de l'output qui part vers $fichier, donc aussi la ligne générée par \echo.
--() { :; }; exec psql bdtest -v détails="$1" -f "$0"
\echo :détails
\echo
SELECT *
FROM dépenses
WHERE détails = :'détails'
donne le même résultat que le script détailx présenté plus haut.
Cependant dès l'instant où la commande psql est terminée, nous quittons le script. Il est impossible de faire transiter dans le même script l'output du SELECT par awk comme auparavant.
Si nous partons du script dépensepx:
--() { :; }; exec psql bdtest -q -v signification="$1" -f "$0"
\pset numericlocale
\pset border 2
\pset linestyle u
\pset footer
\set titre 'Dépenses ':signification
\pset title :titre'\n'
\set code_u
\echo
SELECT code_u
FROM utilisations
WHERE signification = :'signification'
\gset
SELECT référence, date_exec::text as date, détails, montant
FROM dépenses
WHERE code_u = :'code_u'
UNION
SELECT 'TOTAL', '', '', sum(montant)
FROM dépenses
WHERE code_u = :'code_u'
order by 1
et que nous voulons apporter les mêmes améliorations que pour le script dépensex, nous devons l'appeler à partir du script bash dépense0x
#!/bin/bash
fichier=$(mktemp)
exec 7>&1
exec 1>$fichier
exec 6>&2
exec 2>/dev/null
./dépensepx "$1"
exec 1>&7 7>&-
awk 'NR==6 {y=$0};/TOTAL/ {print y};1' $fichier
exec 2>&6 6>&-
Comme auparavant nous redirigeons la sortie des erreurs (canal 2) vers le néant.
De plus nous redirigeons la sortie standard (canal 1) vers le fichier temporaire créé, de sorte que toute la sortie du script dépensepx s'effectuera vers ce fichier. Bien sûr, il faut revenir à la situation initiale avant l'exécution de awk pour que l'output de ce dernier soit dirigé vers l'écran. Remarquons que awk sauvegarde la ligne 6 au lieu de la 5 comme auparavant.
Explication:
La commande \o $fichier utilisée dans le script dépensex envoie dans $fichier le résultat des instructions SQL.
En cas de redirection de la sortie standard, c'est la totalité de l'output qui part vers $fichier, donc aussi la ligne générée par \echo.
