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 08/03/2022 18:41:39

cllemendavvid
Membre

syntaxe après le FROM pour une "table variable"

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

#2 08/03/2022 18:54:26

gleu
Administrateur

Re : syntaxe après le FROM pour une "table variable"

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

#3 30/04/2022 17:21:56

cllemendavvid
Membre

Re : syntaxe après le FROM pour une "table variable"

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

#4 01/05/2022 10:29:52

rjuju
Administrateur

Re : syntaxe après le FROM pour une "table variable"

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

Hors ligne

#5 01/05/2022 22:25:03

cllemendavvid
Membre

Re : syntaxe après le FROM pour une "table variable"

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 niveau


Alors 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

#6 02/05/2022 09:29:33

ruizsebastien
Membre

Re : syntaxe après le FROM pour une "table variable"

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

#7 04/05/2022 20:41:25

cllemendavvid
Membre

Re : syntaxe après le FROM pour une "table variable"

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

#8 05/05/2022 12:12:35

rjuju
Administrateur

Re : syntaxe après le FROM pour une "table variable"

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.

Hors ligne

#9 05/05/2022 12:48:37

dverite
Membre

Re : syntaxe après le FROM pour une "table variable"

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.

Hors ligne

#10 05/05/2022 20:32:31

cllemendavvid
Membre

Re : syntaxe après le FROM pour une "table variable"

rjuju a écrit :

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


dverite a écrit :

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

Pied de page des forums