Forums PostgreSQL.fr

steely35

Membre

utilisation psql pour exécuter un script pl/pgsql

bonjour,
J'ai créé mon 1er un script pl/pgsql.
Il fonctionne parfaitement bien sous pgadmin: il me génère un affichage qui ressemble à ça:

INFO:  ------------------------------------------------------------
INFO:  - Nb filtres créés sur l'année 2021 par mois -
INFO:  -----------------------------------------------------------
INFO:  mois: 01 -> 10
INFO:  mois: 02 -> 2
INFO:  mois: 03 -> 0
INFO:  mois: 04 -> 0
INFO:  mois: 05 -> 0
INFO:  mois: 06 -> 0
INFO:  mois: 07 -> 0
INFO:  mois: 08 -> 0
INFO:  mois: 09 -> 0
INFO:  mois: 10 -> 0
INFO:  mois: 11 -> 0
INFO:  mois: 12 -> 0
INFO:  --------------------------------------------------------------
INFO:  - Nb filtres créés sur le mois 02/2021 par jour -
INFO:  --------------------------------------------------------------
INFO:  le  01/02/2021 -> 0
INFO:  le  02/02/2021 -> 0
INFO:  le  03/02/2021 -> 0
INFO:  le  04/02/2021 -> 1
INFO:  le  05/02/2021 -> 1
INFO:  le  06/02/2021 -> 0
INFO:  le  07/02/2021 -> 0
INFO:  le  08/02/2021 -> 0
INFO:  le  09/02/2021 -> 0
INFO:  le  10/02/2021 -> 0
INFO:  le  11/02/2021 -> 0
INFO:  le  12/02/2021 -> 0
INFO:  le  13/02/2021 -> 0
INFO:  le  14/02/2021 -> 0
INFO:  le  15/02/2021 -> 0
INFO:  le  16/02/2021 -> 0
INFO:  le  17/02/2021 -> 0
INFO:  le  18/02/2021 -> 0
INFO:  le  19/02/2021 -> 0
INFO:  le  20/02/2021 -> 0
INFO:  le  21/02/2021 -> 0
INFO:  le  22/02/2021 -> 0
INFO:  le  23/02/2021 -> 0
INFO:  le  24/02/2021 -> 0
INFO:  le  25/02/2021 -> 0
INFO:  le  26/02/2021 -> 0
INFO:  le  27/02/2021 -> 0
INFO:  le  28/02/2021 -> 0

Query returned successfully in 43 msec.

Je souhaite maintenant pouvoir obtenir cet affichage en l'exécutant via psql ( les utilisateurs finaux n'ont pas accès à pgadmin).

Je lance donc psql -d... -U... et appelle ensuite mon fichier sql par la méta commande \i monscript.sql

Et là j'ai 2 soucis:
1- je ne parviens pas à rediriger la sortie vers un fichier .txt: elle ne se fait que vers la console!
2- et cette sortie console ne me donne que la toute fin du résultat et je n'ai pas d'ascenceur pour remonter au début...

Bref, je galère à essayer de jouer sur les options de psql, sans succès pour l'instant :-/

Merci à vous!
Steely

monscript.sql:

do $$

DECLARE
    vCurYear TimeStamp;
    vCurYearChar varchar(4);
    vCurYearNum integer;
     vYearAfterChar varchar(5);
    vDateTime1 TimeStamp;
    vDateTime2 TimeStamp;
    vDateTime1Char varchar(19);
    vDateTime2Char varchar(19);
   
    vMois INTEGER;
    vMoisChar varchar(3);
    vMoisAfterChar varchar(3);
   
    vNbfiltres INTEGER;
   
    vJour INTEGER;
    vJourChar varchar(3);
    vJourAfterChar varchar(3);
    vCurMonth TimeStamp;
    vCurMonthChar varchar(2);
    vCurMonthNum integer;
    vNbJourCurMonth integer;
   
begin
    -------------------------------------------------------------
    - Stats de l'année par mois
    -------------------------------------------------------------
   
    --vCurYear:= (select (DATE_TRUNC('year', CURRENT_TIMESTAMP - interval '1 year')));
    vCurYear:= (select (DATE_TRUNC('year', CURRENT_TIMESTAMP)));
    vCurYearChar := TO_CHAR(vCurYear,'YYYY');
    vCurYearNum := TO_NUMBER(vCurYearChar,'9999');
    vYearAfterChar := TO_CHAR(vCurYearNum+1,'0000');

    raise info '----------------------------------------------------';
    raise info '- Nb filtres créés sur l''année % par mois -',vCurYearChar;
    raise info '----------------------------------------------------';

    vMois := 1;
    while vMois<=12 loop
        vMoisChar := to_char(vMois,'00');
        vMoisAfterChar := to_char(vMois+1,'00');
        vDateTime1 := (select to_timestamp(concat(vCurYearChar,'-',vMoisChar, '-01 00:00:00'),'YYYY-MM-DD HH24:MI:SS'));
        if (vMois=12) then
            vDateTime2 := (select to_timestamp(concat(vYearAfterChar,'-01-01 00:00:00'),'YYYY-MM-DD HH24:MI:SS'));
        else   
            vDateTime2 := (select to_timestamp(concat(vCurYearChar,'-', vMoisAfterChar, '-01 00:00:00'),'YYYY-MM-DD HH24:MI:SS'));
        end if;
        vDateTime1Char := TO_CHAR(vDateTime1, 'YYYY-MM-DD HH24:MI:SS');
        vDateTime2Char := TO_CHAR(vDateTime2, 'YYYY-MM-DD HH24:MI:SS');
        EXECUTE 'select sum(1)
        from filtre FI
        inner join declarant DEC on DEC.id=FI.declarant_id
        where DEC.profile=0 and FI.date_creation>= $1 and FI.date_creation< $2'
        INTO vNbfiltres
        USING vDateTime1, vDateTime2;
        raise info 'mois: % -> %', substr(vDateTime1Char,6,2), coalesce(vNbfiltres,0);
        vMois := vMois+1;
    end loop;
   
   

    -------------------------------------------------------------
    -- Stats du mois par jour
    -------------------------------------------------------------

    --vCurMonth:= (select (DATE_TRUNC('month', CURRENT_TIMESTAMP - interval '1 year')));
    vCurMonth:= (select (DATE_TRUNC('month', CURRENT_TIMESTAMP)));
    vCurMonthChar := TO_CHAR(vCurMonth,'MM');
    vCurMonthNum := TO_NUMBER(vCurMonthChar,'00');
    --vYearAfterChar := TO_CHAR(vCurYearNum+1,'0000');

    if (vCurMonthNum = 1 or vCurMonthNum=3 or vCurMonthNum=5 or vCurMonthNum=7 or vCurMonthNum=8 or vCurMonthNum=10 or vCurMonthNum=12)
    then
        vNbJourCurMonth=31;
    elsif (vCurMonthNum = 4 or vCurMonthNum=6 or vCurMonthNum=9 or vCurMonthNum=11)
    then
        vNbJourCurMonth=30;
    else
        if (mod(vCurYearNum,4) =0) then
            vNbJourCurMonth=29;
        else
            vNbJourCurMonth=28;
        end if;
    end if;
   
    raise info '------------------------------------------------------';
    raise info '- Nb filtres créées sur le mois %/% par jour -',vCurMonthChar, vCurYearChar;
    raise info '------------------------------------------------------';

    vJour := 1;
    while vJour<=vNbJourCurMonth loop
        vJourChar := TO_CHAR(vJour,'00');
       
        vDateTime1 := (select to_timestamp(concat(vCurYearChar,'-',vCurMonthChar, '-', vJourChar, ' 00:00:00'),'YYYY-MM-DD HH24:MI:SS'));
        vDateTime2 := (select to_timestamp(concat(vCurYearChar,'-',vCurMonthChar, '-', vJourChar, ' 23:59:59'),'YYYY-MM-DD HH24:MI:SS'));
        --vDateTime2 := (select to_timestamp(concat(vCurYearChar,'-', vMoisAfterChar, '-01 00:00:00'),'YYYY-MM-DD HH24:MI:SS'));
       
       
        vDateTime1Char := TO_CHAR(vDateTime1, 'YYYY-MM-DD HH24:MI:SS');
        vDateTime2Char := TO_CHAR(vDateTime2, 'YYYY-MM-DD HH24:MI:SS');
       
        EXECUTE 'select sum(1)
         from declaration FI
        inner join declarant DEC on DEC.id=FI.declarant_id
        where DEC.profile=0 and FI.date_creation>= $1 and FI.date_creation< $2'
        INTO vNbfiltres
        USING vDateTime1, vDateTime2;
        raise info 'le %/%/% -> %', vJourChar, vCurMonthChar, vCurYearChar, coalesce(vNbfiltres,0);
        vJour := vJour+1;
    end loop;
   
end $$;

gleu

Administrateur

Re : utilisation psql pour exécuter un script pl/pgsql

Les RAISE sont envoyés sur la sortie des erreurs (stderr) et non pas sur la sortie standard (stdout).

---

Guillaume.

rjuju

Administrateur

Re : utilisation psql pour exécuter un script pl/pgsql

En l'état, le seul moyen de récupérer la sortie serait de rediriger la sortie d'erreur.  Exemple:

$ psql -Xc "do \$\$ begin raise notice 'test'; end; \$\$ language plpgsql;" 2>output.txt
DO

$ cat output.txt
NOTICE:  test

Une autre alternative serait de renvoyer les lignes en tant qu'enregistrement.

Au passage, il est probable que vous puissiez supprimer les boucles générant différentes dates, en utilisant à la place la fonction generate_series(timestamptz debut, timestamptz fin, interval pas).

---

Julien.
https://rjuju.github.io/

ruizsebastien

Membre

Re : utilisation psql pour exécuter un script pl/pgsql

bonjour,

ça doit répondre à votre besoin (il me semble) :
https://docs.postgresql.fr/11/app-psql.html

-L nomfichier
--log-file=nomfichier

    Écrit tous les résultats des requêtes dans le fichier nomfichier en plus de la destination habituelle.

---

Cordialement,

Sébastien.

gleu

Administrateur

Re : utilisation psql pour exécuter un script pl/pgsql

C'est pire que ça en fait. Il n'est pas nécessaire d'utiliser generate_series.

Le calcul de stat se fait en une seule requpête pour chaque partie :

* stat par année

select extract('month' from FI.date_creation), count(*)
from filtre FI
inner join declarant DEC on DEC.id=FI.declarant_id
where DEC.profile=0
  and FI.date_creation between DATE_TRUNC('year', CURRENT_TIMESTAMP) 
                       and     DATE_TRUNC('year', CURRENT_TIMESTAMP)+'1 year'::interval
group by 1
order by 1;

* stats par mois

select extract('day' from FI.date_creation), count(*)
from declaration FI
inner join declarant DEC on DEC.id=FI.declarant_id
where DEC.profile=0
  and FI.date_creation between DATE_TRUNC('month', CURRENT_TIMESTAMP) 
                       and     DATE_TRUNC('month', CURRENT_TIMESTAMP)+'1 month'::interval
group by 1
order by 1;

Et ça remplace un paquet de code inutile avec des meilleures performances que les boucles de PL/pgsql.

---

Guillaume.

steely35

Membre

Re : utilisation psql pour exécuter un script pl/pgsql

Merci pour ta réponse, ça fonctionne très bien!

Et merci pour ta (vos) réponses sur la revue de la requête. Avec le group by c'est effectivement nettement plus concis :-))

Une dernière question, comment supprimer la partie gauche de l'édition générée (psql:./monScript.sql:242)  ? J'ai regardé les variables d'env. PROMPT1 PROMP2 PROMPT3 mais ce n'est visiblement pas géré à ce niveau...

psql:./monScript.sql:242: INFO:  ----------------------------------------------------
psql:./monScript.sql:242: INFO:  - Nb filtres créés sur l'année 2021 par mois -
psql:./monScript.sql:242: INFO:  ----------------------------------------------------
psql:./monScript.sql:242: INFO:  mois: 01 -> 1
psql:./monScript.sql:242: INFO:  mois: 02 -> 0
psql:./monScript.sql:242: INFO:  mois: 03 -> 0
psql:./monScript.sql:242: INFO:  mois: 04 -> 0
psql:./monScript.sql:242: INFO:  mois: 05 -> 0
psql:./monScript.sql:242: INFO:  mois: 06 -> 0
psql:./monScript.sql:242: INFO:  mois: 07 -> 0
psql:./monScript.sql:242: INFO:  mois: 08 -> 0

Merci encore à vous pour vos réponses et pour la réactivité, c'est chouette !

Bonne journée,
Steely

En l'état, le seul moyen de récupérer la sortie serait de rediriger la sortie d'erreur.  Exemple:

$ psql -Xc "do \$\$ begin raise notice 'test'; end; \$\$ language plpgsql;" 2>output.txt
DO

$ cat output.txt
NOTICE:  test

Une autre alternative serait de renvoyer les lignes en tant qu'enregistrement.

Au passage, il est probable que vous puissiez supprimer les boucles générant différentes dates, en utilisant à la place la fonction generate_series(timestamptz debut, timestamptz fin, interval pas).

gleu

Administrateur

Re : utilisation psql pour exécuter un script pl/pgsql

Non, rien à voir avec les variables PROMPTx, qui servent uniquement en mode interactif. Je ne pense pas que psql ait une option pour désactiver ça. Il faut plutôt filtrer la sortie du coup.

---

Guillaume.

gleu

Administrateur

Re : utilisation psql pour exécuter un script pl/pgsql

Alors, en étudiant le code, il y a quand même un moyen. Pas idéal, mais là, c'est à vous de voir. Vous avez la sortie "psql:./monScript.sql:242: " parce que vous lui avez donné un fichier à traiter. Sans fichier, vous n'avez pas cette sortie. Du coup, il suffit de faire un "cat fichier | psql". C'est pas beau mais ça fonctionne :

$ psql -Xf toto.sql
psql:toto.sql:5: NOTICE:  toto
DO

$ cat toto.sql | psql -X
NOTICE:  toto
DO

---

Guillaume.

steely35

Membre

Re : utilisation psql pour exécuter un script pl/pgsql

Alors, en étudiant le code, il y a quand même un moyen. Pas idéal, mais là, c'est à vous de voir. Vous avez la sortie "psql:./monScript.sql:242: " parce que vous lui avez donné un fichier à traiter. Sans fichier, vous n'avez pas cette sortie. Du coup, il suffit de faire un "cat fichier | psql". C'est pas beau mais ça fonctionne :

$ psql -Xf toto.sql
psql:toto.sql:5: NOTICE:  toto
DO

$ cat toto.sql | psql -X
NOTICE:  toto
DO

Merciiiii!