Sql avec paramètres et condition IF

Christof25 · 29/01/2021 10:38:08

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

rjuju · 29/01/2021 11:04:58

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";

Christof25 · 29/01/2021 12:00:31

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?

ruizsebastien · 29/01/2021 16:51:48

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. "

Christof25 · 29/01/2021 17:02:34

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

Christof25 · 29/01/2021 17:34:41

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

rjuju · 29/01/2021 20:35:19

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.

gleu · 29/01/2021 23:23:25

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.

Christof25 · 01/02/2021 09:05:21

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

rjuju · 01/02/2021 09:33:16

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é.

rjuju · 01/02/2021 09:34:19

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 ?

Christof25 · 01/02/2021 09:48:09

j'obtiens cela :

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

rjuju · 01/02/2021 09:55:18

Il manque le message d'erreur...

Christof25 · 01/02/2021 09:59:39

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!';
        ^

gleu · 01/02/2021 11:17:30

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

Christof25 · 01/02/2021 12:26:26

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)

rjuju · 01/02/2021 12:49:27

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.

Christof25 · 01/02/2021 14:55:04

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;

gleu · 01/02/2021 15:31:05

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;

Christof25 · 01/02/2021 16:11:05

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

gleu · 01/02/2021 16:41:49

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

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

Christof25 · 01/02/2021 18:05:11

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
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.

rjuju · 02/02/2021 04:47:58

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.

Christof25 · 02/02/2021 10:17:03

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....

rjuju · 02/02/2021 10:34:30

END;
$$

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

Forums PostgreSQL.fr

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

Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

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

Re : Sql avec paramètres et condition IF

Pied de page des forums