Forums PostgreSQL.fr

meles

Membre

curseur dans un curseur

Bonjour,
  suite à cette discussion [url]http://forums.postgresql.fr/viewtopic.php?id=1026[url], je me lances dans l'écriture d'une procédure, avec pour paramêtres des dates (debut et fin) qui devra aller lire une table de requête et les exécuter une a une avec les paramêtres de dates passés (jexpère être clair). A terme, je souhaiterai que les résultats de chaque requête soit écrit dans une table mais dans un premier temps, je vais me contenter d'un affichage écran.

Voila ou j'en suis:

drop table if exists testing;
create table testing(
  idnum integer,
  datsor date,
  codpos char(5)
  );
insert into testing (idnum, datsor, codpos) values
  (1,'2010-01-03','13005'),
  (2,'2009-12-27','13999'),
  (3,'2010-02-12','13999'); 

drop table if exists qualito;
create table qualito(
  idqry integer,
  query varchar,
  libqry varchar
  );
insert into qualito values (1,'select idnum, codpos from testing where codpos like ''%999'' and datsor between $1 and $2;','Code postal imprécis');

CREATE OR REPLACE FUNCTION plop(datdeb date, datfin date)
RETURNS VOID AS
$$
DECLARE
id INTEGER;
req VARCHAR;
lib VARCHAR;
lstqry CURSOR FOR SELECT * FROM qualito;
BEGIN
OPEN lstqry;
FETCH lstqry INTO id, req, lib;
WHILE FOUND LOOP
  RAISE NOTICE '%,%,%', id, req, lib;
END LOOP;
END;   
$$
LANGUAGE PLPGSQL
VOLATILE
COST 100;

select plop(to_date('20100101','YYYYMMDD'),to_date('20101231','YYYYMMDD'));

Je sèche un peu pour ouvrir une deuxième boucle dans la première (avec un nouveau curseur sur la requête lu dans la table qualito).
L'autre probleme est le comportement de ce code! il boucle et m'affiche plusieur fois le message RAISE NOTICE alors qu'il devrait ne le faire qu'une fois puisqu'il n'y a qu'une requete dans la table qualito.

Cordialement

Marc Cousin

Membre

Re : curseur dans un curseur

Pour ce qui est du code qui boucle :

WHILE FOUND LOOP
  RAISE NOTICE '%,%,%', id, req, lib;
END LOOP;

FOUND ne risque pas de changer dans la boucle…

Vous devez faire un FETCH dans une boucle.

Je l'écris de cette façon habituellement :

LOOP                                                                                                                                                                                                                                 
  FETCH v_cursor INTO v_record;                                                                                                                                                                                                       
  EXIT WHEN NOT FOUND; -- on est arrivés à la fin du curseur               
…
END LOOP

---

Marc.

meles

Membre

Re : curseur dans un curseur

Ok, merci déjà pour ça qui m'enlève une épine du pied.
J'ai aussi testé ça qui semble fonctionner :

WHILE FETCH v_cursor INTO v_record LOOP
  -- instructions
END LOOP;

Cordialement

PS : j'avais oublié de dire que je suis débutant en plpgsql (et en création de function) mais je crois que ça s'est vu!

meles

Membre

Re : curseur dans un curseur

Bonjour,
  finalement j'ai réussi à coup de FOR...LOOP

Pour ceux que ça pourraient intéresser, voila le résultat:

DROP TABLE IF EXISTS testing;
CREATE TABLE testing(
  idnum INTEGER,
  datsor DATE,
  codpos CHAR(5)
  );
INSERT INTO testing (idnum, datsor, codpos) VALUES
  (1,'2010-01-03','13005'),
  (2,'2009-12-27','13999'),
  (3,'2010-02-12','13999'),
  (4,'2010-01-25',NULL);

DROP TABLE IF EXISTS qualito;
CREATE TABLE qualito(
  idqry INTEGER,
  query VARCHAR,
  libqry VARCHAR
  );
INSERT INTO qualito VALUES (1,'select idnum, codpos from testing where codpos like ''%999'' and datsor between $1 and $2;','Code postal imprécis');
INSERT INTO qualito VALUES (2,'select idnum, codpos from testing where codpos is null and datsor between $1 and $2;','Code postal vide');

CREATE OR REPLACE FUNCTION plop(datdeb date, datfin date)
RETURNS VOID AS
$BODY$
DECLARE
  lstqry CURSOR FOR SELECT idqry, query, libqry FROM qualito;
  qual qualito%ROWTYPE;
  result RECORD;
BEGIN
  RAISE NOTICE 'Bornes des séjours : % à %', TO_CHAR(datdeb,'DD/MM/YYYY'),TO_CHAR(datfin,'DD/MM/YYYY');
  FOR qual IN lstqry LOOP
  RAISE NOTICE 'Exécution de la requête: % - %', qual.idqry, qual.libqry;
    FOR result in EXECUTE qual.query using datdeb, datfin LOOP
      RAISE NOTICE '%', result;
    END LOOP;
  END LOOP;
END;   
$BODY$
LANGUAGE PLPGSQL VOLATILE;
select plop(to_date('20100101','YYYYMMDD'),to_date('20101231','YYYYMMDD'));

meles

Membre

Re : curseur dans un curseur

Bonjour,
  cherchant à améliorer les choses, il me reste deux choses à élucider.

Sachant que les requêtes stockés dans la table qualito pourront renvoyer 1..n champs, y'a-t il un moyen de compter le nombre de champs d'un type RECORD ?

En relation avec la première question, plutôt que d'appeler chaque champ du RECORD ou du ROWTYPE par son nom, y-a t-il un moyen de l'appeler par son ordre (un peu comme quand on fait un ORDER BY 1,3 DESC par exemple)?

Cordialement

Marc Cousin

Membre

Re : curseur dans un curseur

Je ne vois pas bien comment passer un nombre variable de paramètres au using? de type inconnu en plus.

À mon avis, ça n'est pas supporté.

---

Marc.

meles

Membre

Re : curseur dans un curseur

Hello,
  ce n'est pas le nombre de paramètres qui change, c'est le nombre de champ renvoyés.

la partie qui est concernée est la suivante :

    FOR result in EXECUTE qual.query using datdeb, datfin LOOP
      RAISE NOTICE '%', result;
    END LOOP;

le RECORD "result" pourrait avoir 1 à n champs, et c'est le nombre de champ de "result" que je souhaitera compter.

Pour être plus clair, sur mes deux requêtes de la table qualito, l'une cherche les codes postaux imprécis, l'autre les code postaux non renseignés.

La première me renvoie idnum plus la valeur du code postal incriminé, l'autre n'a besoin de me renvoyer que l'idnum. Donc mon result pourrait avoir 1 ou 2 champs dans ce cas, et c'est ce que je cherche à compter.

Cordialement

Dernière modification par meles (18/10/2010 14:26:24)

Marc Cousin

Membre

Re : curseur dans un curseur

Si c'est juste le résultat, je vous conseille d'essayer de passer par un tableau, si c'est possible.
Par exemple:
SELECT array[a,b] from test2 limit 2;
      array     
-----------------
{538785,538785}

(a et b sont les colonnes de ma table).

Comme ça, vous n'aurez qu'une seule colonne dans le résultat (donc pas de surprise), et vous pourrez utiliser les fonctions sur tableau, qui elles vous permettront de connaître la taille du tableau par exemple.

---

Marc.

meles

Membre

Re : curseur dans un curseur

Effectivement, ça pourrait être une solution mais :
- tout les champs renvoyés ne sont pas du même type
- idnum sera présent quoiqu'il arrive.

J'ai bien imaginé une alternative, mais elle me parait lourdingue. Dans ma table qualito, le pourrait avoir une colonne qui indiquerai le nombre de champs renvoyé par la requête "query", ce que je pourrait ensuite traité avec des if - elsif - then puisque je ne pense renvoyer de 1 à 4 champs grand max.

Sinon, il faudrait que je trouve un truc du style d'un array qui accepte le mélange de type.

Cordialement

Marc Cousin

Membre

Re : curseur dans un curseur

Si vous ne souhaitez que les afficher, vous pouvez 'caster' avant d'envoyer dans l'array:

SELECT array[a::text,b::text] from test2 limit 2;

---

Marc.

meles

Membre

Re : curseur dans un curseur

Effectivement,
  mais j'avais d'autres projets pour eux (les stocker dans une table).

Mais je vais quand même les caster dans un premier temps.

Merci du coup du main

meles

Membre

Re : curseur dans un curseur

Bonjour,
  j'avais fini par résoudre mon problème en incluant le nb de colonne renvoyé dans la table contenant les requêtes (la table qualito de mon premier message), et je traitais ma boucle sur les requête avec un CASE...WHEN pour gérer ce nombre de colonne retournées variables !

Mais, je trouvais que ce CASE...WHEN était lourd à gérer, j'ai donc réécris la fonction comme ceci:

CREATE OR REPLACE FUNCTION plop(datdeb date, datfin date)
RETURNS VOID AS
$BODY$
 DECLARE
  lstqry CURSOR FOR SELECT idqry, query, libqry FROM qualito;
  qual qualito%ROWTYPE;
  idnum INTEGER;
  defaut1 VARCHAR DEFAULT NULL;
 BEGIN
  RAISE NOTICE 'Bornes des séjours : % à %', TO_CHAR(datdeb,'DD/MM/YYYY'),TO_CHAR(datfin,'DD/MM/YYYY');
  FOR qual IN lstqry LOOP
   RAISE NOTICE 'Exécution de la requête: % - %', qual.idqry, qual.libqry;
    FOR idnum, defaut1 IN EXECUTE qual.query using datdeb, datfin LOOP
     INSERT INTO result (idnum,iderr,defaut1) values (idnum,qual.idqry,defaut1);
    END LOOP;
  END LOOP;
 END;
$BODY$
LANGUAGE PLPGSQL VOLATILE COST 100;

c'est a dire en defaultant à NULL la colonne qui n'est pas toujours renvoyée (defaut1) par les requêtes de la table qualito.

La question que je me posais était la suivante, quand je passe d'une requête qui a attribué une valeur à defaut1 à une requête qui ne renvoies que idnum et donc ne l'utilises pas, ne devrait-on pas s'attendre à ce que defaut1 garde la dernière valeur qui lui a été attribué ?
Ou comme apparemment ce n'est pas le cas, est-il resetté à NULL à chaque boucle sur ma table qualito ?

Cordialement

gleu

Administrateur

Re : curseur dans un curseur

Vous demandez la valeur des deux colonnes idnum et defaut1. Si defaut1 n'a pas de valeur, elle vaut NULL.

---

Guillaume.

meles

Membre

Re : curseur dans un curseur

Bonsoir,
  cela veut-il dire qu'en fait, je n'ai même pas besoin de defaut1 VARCHAR DEFAULT NULL; mais que defaut1 VARCHAR; suffirait ?

Cordialement

gleu

Administrateur

Re : curseur dans un curseur

Les deux sont équivalents.

---

Guillaume.