Forums PostgreSQL.fr

mougoye

Membre

Curseur : Eviter de boucler 2x une même requête / curseurs imbriqués

Bonjour,

Désolé d'avance si le post n'est pas clair car j'ai encore un peu de mal avec les termes débutant dans ce langage.
J'ai une fonction aaa_test qui permet d'écrire dans une table.
Dans cette fonction se trouve 3 curseurs imbriqués (2 dans 1) : sql_cursor_2 et sql_cursor_3 imbriqués dans sql_cursor.
Première question : est-il possible d'imbriqué plusieurs curseurs sans utilisé FOR .... IN EXECUTE ..... pour les curseurs suivants du style:

OPEN sql_cursor;
 
LOOP
 
	FETCH sql_cursor INTO sPol, sPlante;
 
	EXIT WHEN NOT FOUND;
 
	RAISE NOTICE 'ANALYSE % (%) ', sPol, sPlante;

		OPEN sql_cursor_2;
	 
		LOOP
			FETCH sql_cursor_2 INTO sPintersect, sArea;
             
                        EXIT WHEN NOT FOUND;

			RAISE NOTICE 'polygone : % surface : % ', sPintersect, sArea;
			
		END LOOP;

END LOOP;

Le souci c'est que le EXIT WHEN NOT FOUND nous faire sortir de tous les curseurs

Pour contourner ce problème, j'ai donc insérer deux curseurs sql_cursor_2 et sql_cursor_3
Cependant,
Deuxième question : les curseurs sql_cursor_2 et sql_cursor_3 effectuent sensiblement la même chose mise à part que le select s'effectue sur un champ différent. N'y a-t-il pas moyen d'éviter ce double "balayement" en récupérant directement les valeurs des deux champs et en les stockant dans des variables ?

Fonction entière :

-- Function: aaa_test(numeric, numeric)

-- DROP FUNCTION aaa_test(numeric, numeric);

CREATE OR REPLACE FUNCTION aaa_test(ibord numeric, iprecision numeric)
  RETURNS void AS
$BODY$
 
DECLARE 
        bVal BOOLEAN;
	sPol text;
	sPintersect text;
        sArea text;
	sPlante text;
	sSchema text;
	sTable text;
	sChamp text;
	sSrid text;
        sql_cursor cursor FOR SELECT DISTINCT(bord_carto_id),nom FROM conso_geographique, entite_carto, conso_element WHERE r_bord = iBord AND conso_geographique.rc_code = 'UTM_2-1' AND conso_geographique.ent_id = entite_carto.ent_id AND id_element = id_element_observe ORDER BY nom;
	sql_cursor_2 refcursor;
	sql_cursor_3 refcursor;
        nb_ligne integer;
 
BEGIN
 
EXECUTE 'DELETE FROM aaa_cas_test;';
 
OPEN sql_cursor;
 
LOOP
 
	FETCH sql_cursor INTO sPol, sPlante;
 
	EXIT WHEN NOT FOUND;
 
	RAISE NOTICE 'ANALYSE % (%) ', sPol, sPlante;

		FOR sql_cursor_2 IN EXECUTE 

			'SELECT ent_libelle
			FROM (SELECT max(
				area2d(
					intersection(
						ST_SnapToGrid(
							(SELECT bd.geometry FROM bord_carto AS bd WHERE bord_id = ' || sPol || '),' || iPrecision || ') , ST_SnapToGrid(geometry,' || iPrecision || '))
			)
			) AS area , 
			ent_libelle 
			FROM entite_carto AS ent 
			JOIN ref_carto AS ref ON ref.rc_id = ent.rc_id WHERE ref.rc_code =''UTM_2-1'' 
			AND INTERSECTS((SELECT bd.geometry FROM bord_carto AS bd WHERE bord_id = ' || sPol || '), geometry) 
			GROUP BY ent_id, ent_libelle ORDER BY area DESC) as rqt
			WHERE area > 0;'
	 
		LOOP
	 
			sPintersect := sql_cursor_2;

		END LOOP;	

		FOR sql_cursor_3 IN EXECUTE 

			'SELECT area 
			FROM (SELECT max(
				area2d(
					intersection(
						ST_SnapToGrid(
							(SELECT bd.geometry FROM bord_carto AS bd WHERE bord_id = ' || sPol || '),' || iPrecision || ') , ST_SnapToGrid(geometry,' || iPrecision || '))
			)
			) AS area , 
			ent_libelle 
			FROM entite_carto AS ent 
			JOIN ref_carto AS ref ON ref.rc_id = ent.rc_id WHERE ref.rc_code =''UTM_2-1'' 
			AND INTERSECTS((SELECT bd.geometry FROM bord_carto AS bd WHERE bord_id = ' || sPol || '), geometry) 
			GROUP BY ent_id, ent_libelle ORDER BY area DESC) as rqt
			WHERE area > 0;'
	 
		LOOP
			sArea := sql_cursor_3;
		END LOOP;

		RAISE NOTICE 'polygone : % surface : % ', sPintersect, sArea;

		EXECUTE 'INSERT INTO aaa_cas_test VALUES (''' || sPol || ''',''' || sPlante || ''', ''' || sPintersect || ''',''' || sArea || ''');';
 
	 	
END LOOP;
 
CLOSE sql_cursor;
RETURN;
END;

Merci par avance pour vos réponses

Marc Cousin

Membre

Re : Curseur : Eviter de boucler 2x une même requête / curseurs imbriqués

Pour la première question: rien ne vous oblige à faire EXIT WHEN NOT FOUND simple.

Voici un exemple tiré de la doc:

<<ablock>>
BEGIN
    -- some computations
    IF stocks > 100000 THEN
        EXIT ablock;  -- causes exit from the BEGIN block
    END IF;
    -- computations here will be skipped when stocks > 100000
END;

Rien ne vous empêche donc de mettre des labels à chacune de vos boucles, et de faire un
EXIT  mon_label WHEN NOT FOUND;

Pour le second point, bien sûr, vous pouvez récupérer plusieurs valeurs dans un enregistrement en même temps. Tout est documenté ici: http://doc.postgresql.fr/9.0/plpgsql-cursors.html
Pour résumer, lors du fetch, donnez lui la liste des variables à affecter séparées par des virgules, au lieu d'une seule variable.

---

Marc.

mougoye

Membre

Re : Curseur : Eviter de boucler 2x une même requête / curseurs imbriqués

Merci pour votre réponse
Je vais tester cela

mougoye

Membre

Re : Curseur : Eviter de boucler 2x une même requête / curseurs imbriqués

Merci Marc, j'ai trouvé la solution grâce à votre post.
Ci-dessous la solution :

CREATE OR REPLACE FUNCTION bbb_test(ibord numeric, iprecision numeric)
  RETURNS void AS
$BODY$
 
DECLARE 
        bVal BOOLEAN;
	sPol bigint;
	sPintersect text;
        sArea double precision;
	sPlante text;
	sSchema text;
	sTable text;
	sChamp text;
	sSrid text;
        sql_cursor cursor FOR SELECT DISTINCT(bord_carto_id),nom FROM conso_geographique, entite_carto, conso_element WHERE r_bord = iBord AND conso_geographique.rc_code = 'UTM_2-1' AND conso_geographique.ent_id = entite_carto.ent_id AND id_element = id_element_observe ORDER BY nom;
	sql_cursor_2 cursor FOR SELECT ent_libelle, area
			FROM (SELECT max(
				area2d(
					intersection(
						ST_SnapToGrid(
							(SELECT bd.geometry FROM bord_carto AS bd WHERE bord_id = sPol ), iPrecision ) , ST_SnapToGrid(geometry, iPrecision ))
			)
			) AS area , 
			ent_libelle 
			FROM entite_carto AS ent 
			JOIN ref_carto AS ref ON ref.rc_id = ent.rc_id WHERE ref.rc_code ='UTM_2-1' 
			AND INTERSECTS((SELECT bd.geometry FROM bord_carto AS bd WHERE bord_id = sPol ), geometry) 
			GROUP BY ent_id, ent_libelle ORDER BY area DESC) AS rqt
			WHERE area > 0;
	--sql_cursor_3 refcursor;
        nb_ligne integer;
 
BEGIN
 
EXECUTE 'DELETE FROM aaa_cas_test;';
 
OPEN sql_cursor;
 
<<label1>>
 
LOOP
 
	FETCH sql_cursor INTO sPol, sPlante;
 
	EXIT label1 WHEN NOT FOUND;
 
	RAISE NOTICE 'ANALYSE % (%) ', sPol, sPlante;
 
 
		OPEN sql_cursor_2;
 
		<<label2>>
 
		LOOP
			FETCH sql_cursor_2 INTO sPintersect, sArea;
 
			EXIT label2 WHEN NOT FOUND;
 
			RAISE NOTICE 'polygone : % surface : % ', sPintersect, sArea;
 
			EXECUTE 'INSERT INTO aaa_cas_test VALUES (''' || sPol || ''',''' || sPlante || ''', ''' || sPintersect || ''',''' || sArea || ''');';
 
		END LOOP;
 
		CLOSE sql_cursor_2;
 
 
END LOOP;
 
CLOSE sql_cursor;
RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION bbb_test(numeric, numeric) OWNER TO postgres;