PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 29/01/2021 10:38:08

Christof25
Membre

Sql avec paramètres et condition IF

Bonjour

Je débute en postgre et je dois lancer un sql avec des paramètres et en fonction de la valeur de ce paramètre, je dois initialiser des variables.
J'ai beau chercher des exemples avec Google mais pas de cas concrets, d'où mon appel à l'aide.
Voici mon sql :

do $$
begin
if :p3 = 'indus' then
     PROFIL := 'ROLEADMIN';
else
     PROFIL := 'ROLEREAD';
end if;
CREATE USER :p1 PASSWORD ':p2' NOCREATEDB in group :PROFIL ;
end $$;

Quand je lance ma commande, voilà ce que j'obtiens :

psql -U $USER -d $BASE -f /sql/createUser.sql -v p1="toto" -v p2="xyxyxyxyxy" -v p3="indus"
psql:sql/createUser.sql:9: ERROR:  syntax error at or near ":"
LINE 3: if :p3 = 'indus' then
           ^

Je pense qu'il faut que je déclare des variables pour récupérer les paramètres, non?
Si oui, comment...

Merci

Hors ligne

#2 29/01/2021 11:04:58

rjuju
Administrateur

Re : Sql avec paramètres et condition IF

Vous ne pouvez pas passer de paramètre au bloc de code.  Cela serait sans doute plus simple de gérer cela en sql et avec \gset  Par exemple:

SELECT CASE
    WHEN :'p3' = 'indus' THEN 'ROLEADMIN'
    ELSE 'ROLEREAD'
END AS profil
\gset

CREATE USER :p1 password :'p2' NOCREATEDB IN GROUP :"profil";

Hors ligne

#3 29/01/2021 12:00:31

Christof25
Membre

Re : Sql avec paramètres et condition IF

merci beaucoup, cela fonctionne.

Par contre, quand je testais mon premier sql et qu'il plantait, le code de retour de ma commande plsql était quand même à 0.


psql -U $USER -d $BASE -f /sql/createUser.sql -v p1="toto" -v p2="xyxyxyxyxy" -v p3="indus"
psql:sql/createUser.sql:9: ERROR:  syntax error at or near ":"
LINE 3: if :p3 = 'indus' then
           ^
echo $?
0

Comment faire pour savoir si cela a aboutit ou pas?

Hors ligne

#4 29/01/2021 16:51:48

ruizsebastien
Membre

Re : Sql avec paramètres et condition IF

Bonjour,

Tu peux ajouter "--set ON_ERROR_STOP=on" à ta commande psql.

extrait de la doc :
"psql renvoie 0 au shell s'il s'est terminé normalement, 1 s'il y a eu une erreur fatale de son fait (par exemple : pas assez de mémoire, fichier introuvable), 2 si la connexion au serveur s'est interrompue et que la session n'était pas interactive, 3 si une erreur est survenue dans un script et si la variable ON_ERROR_STOP était positionnée. "


Cordialement,

Sébastien.

Hors ligne

#5 29/01/2021 17:02:34

Christof25
Membre

Re : Sql avec paramètres et condition IF

Merci beaucoup!! Cela fonctionne très bien!!
Bonne journée

Hors ligne

#6 29/01/2021 17:34:41

Christof25
Membre

Re : Sql avec paramètres et condition IF

Me revoilà...
Dans mon sql, j'aimerai effectuer une recherche de l'utilisateur avant de le créer car il peut déjà exister.
Nous avons déjà un sql de ce type pour Oracle. J'aimerai donc reprendre la procédure mais en 'language' PSQSL

procedure recherche_user ( p_user IN ALL_USERS.USERNAME%TYPE,
                           p_user_exist OUT BOOLEAN ) IS
p_tmp           INTEGER;
BEGIN
        Select 1 into p_tmp from ALL_USERS
        WHERE USERNAME=upper(p_user);

        IF p_tmp=1 THEN
                p_user_exist :=TRUE;
        END IF;

EXCEPTION
        WHEN NO_DATA_FOUND THEN
        p_user_exist:=FALSE;
        WHEN OTHERS THEN
        dbms_output.put_line(chr(10)||'Error during searching user_id');
        RAISE SORTIES;
END recherche_user;

Appel de la fonction

    recherche_user(w_user,w_user_exist);
        IF w_user_exist=false
        THEN
                 {creation de l'utilisateur avec mon sql de départ}
        ELSE
           dbms_output.put_line(chr(10)||'Warning ! User  '||w_user||' already exist on database.');
           raise sorties;
        END IF;

Pourriez-vous m'aider à faire l'équivalent, svp?

Merci

Hors ligne

#7 29/01/2021 20:35:19

rjuju
Administrateur

Re : Sql avec paramètres et condition IF

Pourquoi ne pas utiliser \if également dans ce cas?

SELECT COUNT(*) = 0 AS user_not_exist FROM pg_roles WHERE rolname = :'p1' \gset

\if :user_not_exist
...
\endif

Comme indiqué dans la documentation (https://www.postgresql.org/docs/current/app-psql.html ) les blocs \if peuvent être imbriqués.

Hors ligne

#8 29/01/2021 23:23:25

gleu
Administrateur

Re : Sql avec paramètres et condition IF

J'aurais parié que le "CREATE ROLE IF NOT EXISTS" existait justement. Julien, une idée de pourquoi ça n'existe pas ? surtout que ça ne doit pas être difficile à ajouter. J'ai cherché un peu mais je ne trouve rien sur les listes.


Guillaume.

Hors ligne

#9 01/02/2021 09:05:21

Christof25
Membre

Re : Sql avec paramètres et condition IF

Bonjour

Merci rjuju. Ça fonctionne.
J'aimerai également ajouter un message d'information dans le cas d'une création et dans le cas il existe déjà.

J'ai testé cela mais cela sort en erreur :

SELECT COUNT(*) = 0 AS user_not_exist FROM pg_roles WHERE rolname = :'p1' \gset

\if :user_not_exist

SELECT CASE
    WHEN :'p3' = 'indus' THEN 'roleadmin'
    ELSE 'roleread'
END AS profil
\gset
CREATE USER :p1 password :'p2' NOCREATEDB IN GROUP :"profil";
dbms_output.put_line('creation user :p1 done!');
\endif

J'ai également essayé avec

raise notice 'creation user :p1 done!';

Je ne sais pas si c'est la syntaxe ou l'appel qui sont en cause.

Merci

Hors ligne

#10 01/02/2021 09:33:16

rjuju
Administrateur

Re : Sql avec paramètres et condition IF

gleu a écrit :

J'aurais parié que le "CREATE ROLE IF NOT EXISTS" existait justement. Julien, une idée de pourquoi ça n'existe pas ? surtout que ça ne doit pas être difficile à ajouter. J'ai cherché un peu mais je ne trouve rien sur les listes.

Je ne sais pas trop.  Je crois que les versions [ IF [ NOT ] EXISTS ] sont généralement ajoutées au compte goutte en fonction des demandes.  J'imagine que la création d'un utilisateur est suffisamment rare pour que personne ne se soit jamais plaint.  Aussi, les sémantiques deviennt toujours un peu compliquées, ce qui explique probablement que cela ne soit pas le cas pour tous les DDL.  Par exemple, un CREATE USER IF NOT EXISTS toto NOSUPER alors qu'un toto existe déjà mais est super utilisateur.  L'ordre SQL ne devrait à priori rien faire vu que l'utilisateur existe, mais ce n'est pas forcément le comportement attendu, et cela pourrait devenir un problème de sécurité.

Hors ligne

#11 01/02/2021 09:34:19

rjuju
Administrateur

Re : Sql avec paramètres et condition IF

Christof25 a écrit :

J'ai testé cela mais cela sort en erreur :

SELECT COUNT(*) = 0 AS user_not_exist FROM pg_roles WHERE rolname = :'p1' \gset

\if :user_not_exist

SELECT CASE
    WHEN :'p3' = 'indus' THEN 'roleadmin'
    ELSE 'roleread'
END AS profil
\gset
CREATE USER :p1 password :'p2' NOCREATEDB IN GROUP :"profil";
dbms_output.put_line('creation user :p1 done!');
\endif

J'ai également essayé avec

raise notice 'creation user :p1 done!';

Je ne sais pas si c'est la syntaxe ou l'appel qui sont en cause.

Merci


dbms_output n'existe pas sur postgres (sauf si vous utilisez orafce peut être).  Quelle est l'erreur en utilisant raise notice ?

Hors ligne

#12 01/02/2021 09:48:09

Christof25
Membre

Re : Sql avec paramètres et condition IF

j'obtiens cela :

LINE 1: raise notice 'creation user :p1 done!';
        ^

Hors ligne

#13 01/02/2021 09:55:18

rjuju
Administrateur

Re : Sql avec paramètres et condition IF

Il manque le message d'erreur...

Hors ligne

#14 01/02/2021 09:59:39

Christof25
Membre

Re : Sql avec paramètres et condition IF

rjuju a écrit :

Il manque le message d'erreur...

oups, pardon

psql:createUser.sql:11: ERROR:  syntax error at or near "raise"
LINE 1: raise notice 'creation user :p1 done!';
        ^

Hors ligne

#15 01/02/2021 11:17:30

gleu
Administrateur

Re : Sql avec paramètres et condition IF

RAISE NOTICE ne fonctionne que dans une routine PL/pgsql.


Guillaume.

Hors ligne

#16 01/02/2021 12:26:26

Christof25
Membre

Re : Sql avec paramètres et condition IF

gleu a écrit :

RAISE NOTICE ne fonctionne que dans une routine PL/pgsql.

Merci.
Du coup, j'ai ajouté cela :

CREATE USER :p1 password :'p2' NOCREATEDB IN GROUP :"profil";
do $$
BEGIN
raise notice 'creation user :p1 done!';
END $$;

Ca marche mieux mais mon paramètre n'est pas valorisé...
Il doit manquer une ligne de code...

CREATE ROLE
psql:createUser.sql:14: NOTICE:  creation user :p1 done!
DO

une idée?

Dernière modification par Christof25 (01/02/2021 12:26:40)

Hors ligne

#17 01/02/2021 12:49:27

rjuju
Administrateur

Re : Sql avec paramètres et condition IF

Le paramètre ne sera pas pris en compte dans le bloc de code, ce qui est votre problème original.  Le seul moyen d'y avoir accès serait de créer une vraie fonction, par exemple log_notice(text) que vous appelleriez en passant votre paramètre.

Hors ligne

#18 01/02/2021 14:55:04

Christof25
Membre

Re : Sql avec paramètres et condition IF

rjuju a écrit :

Le paramètre ne sera pas pris en compte dans le bloc de code, ce qui est votre problème original.  Le seul moyen d'y avoir accès serait de créer une vraie fonction, par exemple log_notice(text) que vous appelleriez en passant votre paramètre.

Du genre? :

CREATE FUNCTION log_notice(text)
DECLARE
    user ALIAS FOR $1;
BEGIN
    raise notice 'creation user :user done!';
END;

Hors ligne

#19 01/02/2021 15:31:05

gleu
Administrateur

Re : Sql avec paramètres et condition IF

Non, les variables PL/psql ne fonctionnent pas de la même façon que les variables psql.

Il faudrait certainement faire un :

RAISE NOTICE 'creation user % done!', user;

Guillaume.

Hors ligne

#20 01/02/2021 16:11:05

Christof25
Membre

Re : Sql avec paramètres et condition IF

ok, mais ça coince encore. Pas évident ce langage....

CREATE FUNCTION log_notice(text)
DECLARE
    user ALIAS FOR $1;
BEGIN
    raise notice 'creation user % done!';
END;


SELECT COUNT(*) = 0 AS user_not_exist FROM pg_roles WHERE rolname = :'p1' \gset

\if :user_not_exist

SELECT CASE
    WHEN :'p3' = 'indus' THEN 'roleadmin'
    ELSE 'roleread'
END AS profil
\gset
CREATE USER :p1 password :'p2' NOCREATEDB IN GROUP :"profil";
do $$
BEGIN
   log_notice :p1
END $$;
\endif

Ca plante...

psql -U $USER-d $BASE -f CreateUser.sql -v p1="tintin" -v p2="xxxxx" -v p3="indus"
psql:createUser.sql:3: ERROR:  syntax error at or near "DECLARE"
LINE 2: DECLARE
        ^
psql:createUser.sql:5: ERROR:  syntax error at or near "raise"
LINE 2:     raise notice 'creation user % done!';
            ^
psql:createUser.sql:6: WARNING:  there is no transaction in progress
COMMIT

Hors ligne

#21 01/02/2021 16:41:49

gleu
Administrateur

Re : Sql avec paramètres et condition IF

Votre CREATE FUNCTION ne respecte pas la syntaxe. La doc sur CREATE FUNCTION se trouve sur https://docs.postgresql.fr/13/sql-createfunction.html

De plus, vous avez mal copié mon RAISE NOTICE smile

Et tant que j'y suis, la doc sur PL/pgsql : https://docs.postgresql.fr/13/plpgsql.html


Guillaume.

Hors ligne

#22 01/02/2021 18:05:11

Christof25
Membre

Re : Sql avec paramètres et condition IF

gleu a écrit :

Votre CREATE FUNCTION ne respecte pas la syntaxe. La doc sur CREATE FUNCTION se trouve sur https://docs.postgresql.fr/13/sql-createfunction.html

De plus, vous avez mal copié mon RAISE NOTICE smile

Et tant que j'y suis, la doc sur PL/pgsql : https://docs.postgresql.fr/13/plpgsql.html

Merci pour la doc.
Après avoir consulté la doc, normalement, ma fonction doit être OK.

CREATE FUNCTION log_notice(text) AS $$;
DECLARE
    user ALIAS FOR $1;
BEGIN
    RAISE NOTICE 'creation user % done!', user;
END;
$$

Par contre, pour l'appel, j'ai suivi https://docs.postgresql.fr/9.3/sql-synt … funcs.html et j'ai donc mis cela :

SELECT log_notice(':p1');

Toujours une erreur :

psql:test.sql:9: ERROR:  syntax error at or near "SELECT"
LINE 8: SELECT log_notice(':p1');
        ^

Bref, pour un simple message d'information.... pas évident.

Hors ligne

#23 02/02/2021 04:47:58

rjuju
Administrateur

Re : Sql avec paramètres et condition IF

Le problème vient à priori des lignes précédentes.  Peut être manqeue-t-il un ; à la fin de l'instruction précédente ?


Sinon, ':p1' est différent de :'p1'.  Regardez le paragraphe "SQL interpolation" sur https://www.postgresql.org/docs/current … ERPOLATION pour plus de détails.

Hors ligne

#24 02/02/2021 10:17:03

Christof25
Membre

Re : Sql avec paramètres et condition IF

rjuju a écrit :

Le problème vient à priori des lignes précédentes.  Peut être manqeue-t-il un ; à la fin de l'instruction précédente ?.

Je ne pense pas car il y en a à chaque ligne. Quand j'en ai ajouté un après le $$ , c'était pire, la fonction n'était pas reconnue. Et même sans paramètre, ça coince....

CREATE FUNCTION log_notice(text) AS $$;
DECLARE
    user ALIAS FOR $1;
BEGIN
    RAISE NOTICE 'creation user % done!', user;
END;
$$

SELECT log_notice('TINTIN');

Toujours l'erreur...

psql:test.sql:9: ERROR:  syntax error at or near "SELECT"
LINE 8: SELECT log_notice('TINTIN');
        ^

Je sèche....

Hors ligne

#25 02/02/2021 10:34:30

rjuju
Administrateur

Re : Sql avec paramètres et condition IF

END;
$$

Comme supposé, il manque effectivement un ; après le $$.

Hors ligne

Pied de page des forums