Forums PostgreSQL.fr

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;