Vous n'êtes pas identifié(e).
Pages : 1
Bonsoir,
Je cherche simplement à écrire ce type de code :
Parcourir une série
et créer pour chaque x une table temporaire x
serie (niveau integer)
1
2
3
...
FOR x IN (SELECT niveau FROM serie) LOOP
CREATE TEMPORARY TABLE chaine_de_caractere||x as SELECT ..
C'est la syntaxe " chaine_de_caractere||x " qui ne fonctionne pas dans mon cas.
Autrement, existe-t-il une syntaxe pour une table "variable" après un FROM :
SELECT * FROM chaine_de_caractere||x
Merci de votre aide,
Hors ligne
Il faut construire une requête sous la forme d'une chaîne de caractères et faire une exécution dynamique. Voir https://docs.postgresql.fr/14/plpgsql-s … CUTING-DYN pour les détails.
Guillaume.
Hors ligne
Re Bonjour,
j'ai tenté de comprendre mais je n'arrive pas à appliquer ce type d'exécutions dynamique
pour le moment je sais travailler seulement avec des create, update, select, boucle for/while
si vous pouviez me transmettre qlq explications/exemples sur les exécutions dynamique ce serait top ?
je veux construire une fonction qui réutilise ses résultats
Exemple : Partir de la base des branches d'un arbre
puis construire des tables de "niveaux"
chaque table de niveaux correspond à une bifurcation/noeud
où la branche se dissocie en deux
tant que la branche se dissocie (il existe un noeud) on créer une nouvelle table et on remplit ses attributs
à la fin on arrive aux extrémités de l'arbre/réseau
par avance merci
Clément
Hors ligne
Il faut utiliser EXECUTE et format(). Par exemple:
EXECUTE format('CREATE TEMPORARY TABLE %I AS SELECT ...', 'chaine_de_caractère' || x);Vous pouvez consulter la documentation qui va avec pour plus de détails, notamment les différents formats acceptés par format().
Julien.
https://rjuju.github.io/
Hors ligne
Bonsoir,
merci pour vos réponses
Voilà le code de référence qui fonctionne sans ce EXECUTE
mais qui passe par l'incrémentation de chaque table à utiliser
(pas très pratique lorsqu'il y a beaucoup de tables à créer !) :
references :
tb_noeuds (code_nd, type_nd) --> recense tous les nœuds d'un arbre A
tb_branches (code_branche, nd_origine, nd_extremite) --> recense toutes les branches (=section entre deux nœuds)
noeud1 (code_nd, branche_entrant, type_nd, niveau)--> nœuds de départ (à la base du tronc)
DROP TABLE IF EXISTS noeud2;
CREATE TEMPORARY TABLE noeud2
( code_nd character varying, branche_entrant character varying, type_nd character varying, niveau integer );
DROP FUNCTION test();
CREATE OR REPLACE FUNCTION test ()
RETURNS SETOF void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE
nd TEXT;
BEGIN
FOR nd IN (SELECT code_nd FROM noeud1 ORDER BY code_nd) LOOP
INSERT INTO noeud2 (code_nd, branche_entrant) (SELECT nd_extremite, code_branche FROM arbre_A.tb_branches WHERE nd_origine = nd);
UPDATE noeud2 SET type_nd= tb_noeuds. type_nd, niveau = 2 FROM arbre_A.tb_noeuds WHERE tb_noeuds.code_nd=noeud2.code_nd;
END LOOP;
END;
$BODY$;
SELECT test();
SELECT * FROM noeud2 ORDER BY code_nd;
--> Ensuite pour le niveau 3, on remplace
noeud1 par noeud2
noeud2 par noeud3
--> La fonction que je souhaiterai permet de "dérouler" le processus jusqu'au dernier nœud aval
--> Création de toutes les tables des nœuds de chaque niveauAlors j'ai testé la combinaison EXECUTE format() sans succès
DROP FUNCTION test();
CREATE OR REPLACE FUNCTION test ()
RETURNS SETOF void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE
BEGIN
EXECUTE format('SELECT code_nd FROM %I'
'ORDER BY code_nd', 'noeud'||'1')
INTO c;
END;
$BODY$;
SELECT test();L'erreur remontée :
ERROR: ERREUR: « c » n'est pas une variable connue
LINE 16: INTO c;
Merci d'avance pour les pistes
Clément
Hors ligne
bonjour,
dans la fonction "test" il me semble qu'il manque la déclaration de la variable "c" :
par exemple :
....
AS $BODY$
DECLARE
c text;
BEGIN
....
non ?
Cordialement,
Sébastien.
Hors ligne
Oui merci en effet j'ai bien ajouté
enfaite
Je travail sur un réseau fibres optique composé de
cb = cable = 'branche' si on reprend l'exemple de mon message précédent avec l'arbre
bp = boites de jonctions = noeud
pm919= arbre
ça avance petit à petit 
je tombe sur un blocage avec mon code
je pense qu'une des boucles est défectueuse
DROP TABLE IF EXISTS bp1;
CREATE TEMPORARY TABLE bp1 AS SELECT code_bpe, code_cb as cb_entrant, bpe.type_fonc, bpe.reference, 1 as niveau
FROM pm919.bpe, pm919.cb
WHERE extremite = code_bpe AND origine LIKE 'SF-TEC-%';
--> table des bp de niveau 1
DROP FUNCTION test();
CREATE OR REPLACE FUNCTION test ()
RETURNS SETOF void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE
c INTEGER;
a pm919.bpe.code_bpe%TYPE;
x INTEGER;
bp TEXT;
BEGIN
FOR x IN (SELECT row_number() OVER (ORDER BY nd ) FROM pm919.nd_cb LIMIT 10) LOOP
EXECUTE format('SELECT count(*) FROM %I WHERE code_bpe IN (SELECT origine FROM pm919.cb)', 'bp'||x)
INTO c;
--EXECUTE format('SELECT DISTINCT code_bpe FROM %I ' , 'bp'||x)
--INTO a; à la place de la table 'temp' dans la partie suivante mais ne fonctionne pas
WHILE c > 0 LOOP
EXECUTE format(
'DROP TABLE IF EXISTS %I ;'
'CREATE TEMPORARY TABLE %I (code_bpe character varying,
cb_entrant character varying,
type_fonc character varying,
reference character varying,
niveau integer);'
,'bp'||sum(x+1), 'bp'||sum(x+1));
EXECUTE format(
'DROP TABLE IF EXISTS temp ;'
'CREATE TEMPORARY TABLE temp (code_bpe character varying,
cb_entrant character varying,
type_fonc character varying,
reference character varying,
niveau integer);'
'INSERT INTO temp (code_bpe) (SELECT code_bpe FROM bp'
||x||');'
);
FOR bp IN (SELECT code_bpe FROM temp) LOOP
EXECUTE format('INSERT INTO %I (code_bpe, cb_entrant) (SELECT extremite, code_cb FROM pm919.cb WHERE origine = $1);'
'UPDATE %I SET type_fonc= bpe.type_fonc, reference= bpe.reference, niveau = $2 FROM pm919.bpe WHERE bpe.code_bpe= %I.code_bpe;',
'bp'||sum(x+1), 'bp'||sum(x+1),'bp'||sum(x+1))
USING bp, sum(x+1);
END LOOP;
END LOOP;
END LOOP;
END;
$BODY$;
SELECT test();
SELECT * FROM bp10;
ERROR: ERREUR: mémoire partagée épuisée
HINT: Vous pourriez avoir besoin d'augmenter max_locks_per_transaction.
CONTEXT: instruction SQL « DROP TABLE IF EXISTS bp2 ;CREATE TEMPORARY TABLE bp2 (code_bpe character varying,
cb_entrant character varying,
type_fonc character varying,
reference character varying,
niveau integer); »
fonction PL/pgSQL test(), ligne 21 à EXECUTE
SQL state: 53200
Hors ligne
L'erreur est due au fait que vous essayez d'acquérir trop de verrous en même temps. En effet les verrous ne sont relachés qu'à la fin de la transaction, et apparemment vous avez une boucle infinie, vu que la variable c n'est jamais décrémentée. Par conséquent, vous allez supprimer et recréer la même table (mais avec un oid différent) en boucle jusqu'à saturation des verrous.
Julien.
https://rjuju.github.io/
Hors ligne
Au-delà de l'erreur de la boucle infinie, conceptuellement le fait de créer N tables qui ont une structure identique est anti-relationnel.
Même si ça vous parait plus simple de raisonner sur des tables séparées, en BDD relationnelle on fera une seule table avec une colonne supplémentaire contenant le numéro derrière le "bp" des noms de tables actuels.
Accessoirement, en faisant comme ça, il n'y a plus besoin de SQL dynamique.
Indépendamment de ça, la boucle dans le code en #7 supprime et créé une table temporaire "temp", toujours la même. Il faudrait à la place créer la table une seule fois en dehors de la boucle, et faire un TRUNCATE dans la boucle si on veut la vider de son contenu à chaque itération.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
L'erreur est due au fait que vous essayez d'acquérir trop de verrous en même temps. En effet les verrous ne sont relachés qu'à la fin de la transaction, et apparemment vous avez une boucle infinie, vu que la variable c n'est jamais décrémentée. Par conséquent, vous allez supprimer et recréer la même table (mais avec un oid différent) en boucle jusqu'à saturation des verrous.
oui c'était bien une boucle infinie
dès hier soir en enlevant le "while" c'était bon
merci
Au-delà de l'erreur de la boucle infinie, conceptuellement le fait de créer N tables qui ont une structure identique est anti-relationnel.
Même si ça vous parait plus simple de raisonner sur des tables séparées, en BDD relationnelle on fera une seule table avec une colonne supplémentaire contenant le numéro derrière le "bp" des noms de tables actuels.
Accessoirement, en faisant comme ça, il n'y a plus besoin de SQL dynamique.
Indépendamment de ça, la boucle dans le code en #7 supprime et créé une table temporaire "temp", toujours la même. Il faudrait à la place créer la table une seule fois en dehors de la boucle, et faire un TRUNCATE dans la boucle si on veut la vider de son contenu à chaque itération.
et oui ! optimisations, optimisations..
Effectivement, sans le sql dynamique
j'ai repris le code et ça me donne ceci :
il fonctionne correctement
merci pour votre aide !
DECLARE
x INTEGER;
C INTEGER;
bp TEXT;
BEGIN
x:=1;
C := (SELECT count(*) FROM ebp WHERE code_bpe IN (SELECT origine FROM pm919.cb)AND niveau= 1);
WHILE C > 0 LOOP
FOR bp IN (SELECT DISTINCT code_bpe FROM ebp WHERE niveau=x) LOOP
INSERT INTO ebp (code_bpe, cb_entrant) (SELECT extremite, code_cb FROM pm919.cb WHERE bp=origine) ;
END LOOP;
UPDATE ebp SET type_fonc= bpe.type_fonc, reference= bpe.reference, niveau = x+1 FROM pm919.bpe
WHERE bpe.code_bpe=ebp.code_bpe AND ebp.niveau IS NULL ;
C := (SELECT count(*) FROM ebp WHERE code_bpe IN (SELECT origine FROM pm919.cb) AND niveau= x+1);
x:=x+1;
END LOOP;
END;Hors ligne
Pages : 1