Forums PostgreSQL.fr

baradji

Membre

Union Multiple

Bonjour,
j'ai plusieurs union a faire dans une fonction pl-pgsql.
je ne sais pas comment simplifier le code.
pour le moment, ca marche car je fais l'union de tous les select.mais par le futur ,on risque de perdre des infos car le nombre peut augmenter.
j'aimerai savoir simplifier ce code en utilisant une boucle pour trouver le nombre d'element.

voici le code

CREATE OR REPLACE FUNCTION "public"."absence_export_fonc1" (integer,integer)
RETURNS SETOF "public"."absence_export_type1" AS
$body$
DECLARE
mon_record absence_export_type1;
annee ALIAS FOR $1;
mois ALIAS FOR $2;
BEGIN
FOR mon_record IN
EXECUTE '
(SELECT s.csa_id, s.csa_matricule,s.csa_nom,s.csa_prenom,
(SELECT rab_libelle_gipssi FROM ref_abs_gipssi_kronos WHERE rab_id = 1),
TO_CHAR(ap.apa_act_date,''DD/MM/YYYYA'') AS date_abs,
TO_CHAR(ap.apa_act_date,''YYYY'') AS date_acquisition
FROM com_salaries s
INNER JOIN act_prevision_activites ap ON ap.apa_csa_id=s.csa_id
WHERE EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
AND EXTRACT(MONTH FROM ap.apa_act_date)=' || quote_literal($2) || '
AND ap.apa_act_am = (SELECT rab_code_kronos FROM ref_abs_gipssi_kronos WHERE rab_id = 1))

UNION ALL

(SELECT s.csa_id, s.csa_matricule,s.csa_nom,s.csa_prenom,
(SELECT rab_libelle_gipssi FROM ref_abs_gipssi_kronos WHERE rab_id = 2),
TO_CHAR(ap.apa_act_date,''DD/MM/YYYYA'') AS date_abs,
TO_CHAR(ap.apa_act_date,''YYYY'') AS date_acquisition
FROM com_salaries s
INNER JOIN act_prevision_activites ap ON ap.apa_csa_id=s.csa_id
WHERE EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
AND EXTRACT(MONTH FROM ap.apa_act_date)=' || quote_literal($2) || '
AND ap.apa_act_am = (SELECT rab_code_kronos FROM ref_abs_gipssi_kronos WHERE rab_id = 2))

UNION ALL

(SELECT s.csa_id, s.csa_matricule,s.csa_nom,s.csa_prenom,
(SELECT rab_libelle_gipssi FROM ref_abs_gipssi_kronos WHERE rab_id = 3),
TO_CHAR(ap.apa_act_date,''DD/MM/YYYYA'') AS date_abs,
TO_CHAR(ap.apa_act_date,''YYYY'') AS date_acquisition
FROM com_salaries s
INNER JOIN act_prevision_activites ap ON ap.apa_csa_id=s.csa_id
WHERE EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
AND EXTRACT(MONTH FROM ap.apa_act_date)=' || quote_literal($2) || '
AND ap.apa_act_am = (SELECT rab_code_kronos FROM ref_abs_gipssi_kronos WHERE rab_id = 3))

UNION ALL

(SELECT s.csa_id, s.csa_matricule,s.csa_nom,s.csa_prenom,
(SELECT rab_libelle_gipssi FROM ref_abs_gipssi_kronos WHERE rab_id = 4),
TO_CHAR(ap.apa_act_date,''DD/MM/YYYYA'') AS date_abs,
TO_CHAR(ap.apa_act_date,''YYYY'') AS date_acquisition
FROM com_salaries s
INNER JOIN act_prevision_activites ap ON ap.apa_csa_id=s.csa_id
WHERE EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
AND EXTRACT(MONTH FROM ap.apa_act_date)=' || quote_literal($2) || '
AND ap.apa_act_am = (SELECT rab_code_kronos FROM ref_abs_gipssi_kronos WHERE rab_id = 4))

UNION ALL

(SELECT s.csa_id, s.csa_matricule,s.csa_nom,s.csa_prenom,
(SELECT rab_libelle_gipssi FROM ref_abs_gipssi_kronos WHERE rab_id = 5),
TO_CHAR(ap.apa_act_date,''DD/MM/YYYYA'') AS date_abs,
TO_CHAR(ap.apa_act_date,''YYYY'') AS date_acquisition
FROM com_salaries s
INNER JOIN act_prevision_activites ap ON ap.apa_csa_id=s.csa_id
WHERE EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
AND EXTRACT(MONTH FROM ap.apa_act_date)=' || quote_literal($2) || '
AND ap.apa_act_am = (SELECT rab_code_kronos FROM ref_abs_gipssi_kronos WHERE rab_id = 5))

UNION ALL

(SELECT s.csa_id, s.csa_matricule,s.csa_nom,s.csa_prenom,
(SELECT rab_libelle_gipssi FROM ref_abs_gipssi_kronos WHERE rab_id = 6),
TO_CHAR(ap.apa_act_date,''DD/MM/YYYYA'') AS date_abs,
TO_CHAR(ap.apa_act_date,''YYYY'') AS date_acquisition
FROM com_salaries s
INNER JOIN act_prevision_activites ap ON ap.apa_csa_id=s.csa_id
WHERE EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
AND EXTRACT(MONTH FROM ap.apa_act_date)=' || quote_literal($2) || '
AND ap.apa_act_am = (SELECT rab_code_kronos FROM ref_abs_gipssi_kronos WHERE rab_id = 6))

UNION ALL

(SELECT s.csa_id, s.csa_matricule,s.csa_nom,s.csa_prenom,
(SELECT rab_libelle_gipssi FROM ref_abs_gipssi_kronos WHERE rab_id = 7),
TO_CHAR(ap.apa_act_date,''DD/MM/YYYYA'') AS date_abs,
TO_CHAR(ap.apa_act_date,''YYYY'') AS date_acquisition
FROM com_salaries s
INNER JOIN act_prevision_activites ap ON ap.apa_csa_id=s.csa_id
WHERE EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
AND EXTRACT(MONTH FROM ap.apa_act_date)=' || quote_literal($2) || '
AND ap.apa_act_am = (SELECT rab_code_kronos FROM ref_abs_gipssi_kronos WHERE rab_id = 7))

UNION ALL

(SELECT s.csa_id, s.csa_matricule,s.csa_nom,s.csa_prenom,
(SELECT rab_libelle_gipssi FROM ref_abs_gipssi_kronos WHERE rab_id = 8),
TO_CHAR(ap.apa_act_date,''DD/MM/YYYYA'') AS date_abs,
TO_CHAR(ap.apa_act_date,''YYYY'') AS date_acquisition
FROM com_salaries s
INNER JOIN act_prevision_activites ap ON ap.apa_csa_id=s.csa_id
WHERE EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
AND EXTRACT(MONTH FROM ap.apa_act_date)=' || quote_literal($2) || '
AND ap.apa_act_am = (SELECT rab_code_kronos FROM ref_abs_gipssi_kronos WHERE rab_id = 8))'
LOOP
RETURN NEXT mon_record ;
END LOOP;
RETURN;
END
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;
-------------------------------
rab_id se trouve dans la table ref_abs_gipssi_kronos. on doit parcourir ce champs de 1 à 8.
je ne sais pas comment simplifier ce code avec les UNION.

Merci à celui ou celle qui pourra me donner un coup de pouce

---

www.marchegouro.com

Marc Cousin

Membre

Re : Union Multiple

Je pense qu'on pourrait réécrire l'ensemble en une seule requête, avec jointure, au lieu de faire des union :

xxxx
UNION ALL ?
(SELECT s.csa_id, s.csa_matricule,s.csa_nom,s.csa_prenom,
(SELECT rab_libelle_gipssi FROM ref_abs_gipssi_kronos WHERE rab_id = 8),
TO_CHAR(ap.apa_act_date,''DD/MM/YYYYA'') AS date_abs,
TO_CHAR(ap.apa_act_date,''YYYY'') AS date_acquisition
FROM com_salaries s
INNER JOIN act_prevision_activites ap ON ap.apa_csa_id=s.csa_id
WHERE EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
AND EXTRACT(MONTH FROM ap.apa_act_date)=' || quote_literal($2) || '
AND ap.apa_act_am = (SELECT rab_code_kronos FROM ref_abs_gipssi_kronos WHERE rab_id = 8))'

Si on regarde bien, ap.apa_act_am = (SELECT rab_code_kronos FROM ref_abs_gipssi_kronos WHERE rab_id = 8) n'est rien d'autre qu'une jointure déguisée entre ap et ref_abs_gipssi_kronos:

apa_act_am=rab_code_kronos

pourrait devenir (je n'ai rien pour le tester) quelque chose comme :

SELECT s.csa_id, s.csa_matricule,s.csa_nom,s.csa_prenom, r.rab_libelle_gipssi,
            TO_CHAR(ap.apa_act_date,''DD/MM/YYYYA'') AS date_abs,
            TO_CHAR(ap.apa_act_date,''YYYY'') AS date_acquisition
FROM com_salaries s
INNER JOIN act_prevision_activites ap ON ap.apa_csa_id=s.csa_id
INNER JOIN ref_abs_gipssi_kronos r ON r.rab_code_kronos=ap.apa_act_am
WHERE EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
AND EXTRACT(MONTH FROM ap.apa_act_date)=' || quote_literal($2) || '
AND r.rab_id IN (1,2,3,4,5,6,7,8)

Évidemment, si rab_id ne peut valoir que 1 à 8, et que vous voulez un comportement dynamique dès que vous allez rajouter un rab_id, ne rajoutez pas la dernière clause WHERE.

Un dernier point :
EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
AND EXTRACT(MONTH FROM ap.apa_act_date)=' || quote_literal($2) || '
est très mauvais en termes de performances: vous demandez au moteur de découper la date pour la comparer le mois et l'année passés en paramètre.

Il serait bien plus efficace de générer une plage de dates à partir de ces paramètres, une fois pour toutes, puis de vérifier que les dates de ap sont dans cette plage (on compare alors un champ à une constante). Cela permet entre autres d'utiliser un index sur la colonne apa_act_date.

Ça tombe bien, PostgreSQL dispose de fonctions très pratiques pour travailler avec les dates:

Vous pouvez refaire la comparaison comme ceci :

apa_act_date >= (to_date('0000-01-01','YYYY-MM-DD') + $1 * '1 year'::interval + $2 * '1 month'::interval)
AND apa_act_date < (to_date('0000-01-01','YYYY-MM-DD') + $1 * '1 year'::interval + ($2 +1) * '1 month'::interval)

Cela a l'air plus compliqué, mais le moteur identifiera que les deux expressions sont des constantes, et les évaluera avant d'exécuter la requête.

Pour calculer les dates 'bornes', on prent tout simplement le 1 janvier de l'an 0, et on rajoute les annéeset les mois passés en paramètre, grâce au type de données 'interval'.

http://docs.postgresql.fr/9.0/datatype-datetime.html

---

Marc.

baradji

Membre

Re : Union Multiple

Merci pour message,
j'ai modifié le code, je souhaite utiliser une boucle for, mais je me prend mal, quelqu'un pourrait m'eclaircir.je souligne que j'utilise postgresql 8.1.
merci

-------------------
CREATE OR REPLACE FUNCTION "public"."absence_export_fonc2" (integer,integer)
RETURNS SETOF "public"."absence_export_type1" AS
$body$
DECLARE
mon_record absence_export_type1;
annee ALIAS FOR $1;
mois ALIAS FOR $2;
BEGIN 
      FOR mon_record IN
      FOR compteur IN 1..20 Loop
        EXECUTE '
          (Select s.csa_id, s.csa_matricule, s.csa_nom, s.csa_prenom, k.rab_libelle_gipssi,
          To_char (ap.apa_act_date, ''DD/MM/YYYYA'') as date_abs,
          To_char (ap.apa_act_date, ''YYYY'') as date_acquisition
          From com_salaries s, act_prevision_activites ap, ref_abs_gipssi_kronos k
          where ap.apa_csa_id = s.csa_id
          and EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
          And EXTRACT(MONTH FROM ap.apa_act_date) =' || quote_literal($2) || '
          And ap.apa_act_am = k.rab_code_kronos
          And k.rab_id = compteur
          And s.csa_mode_facture = 0

          UNION

          Select s.csa_id, s.csa_matricule, s.csa_nom, s.csa_prenom, k.rab_libelle_gipssi,
          To_char (ap.apa_act_date, ''DD/MM/YYYYP'') as date_abs,
          To_char (ap.apa_act_date, ''YYYY'') as date_acquisition
          From com_salaries s, act_prevision_activites ap, ref_abs_gipssi_kronos k
          where ap.apa_csa_id = s.csa_id
          and EXTRACT(YEAR FROM ap.apa_act_date)=' || quote_literal($1) || '
          And EXTRACT(MONTH FROM ap.apa_act_date) =' || quote_literal($2) || '
          And ap.apa_act_pm = k.rab_code_kronos
          And k.rab_id = compteur
          And s.csa_mode_facture = 0)'
          END Loop;
          LOOP
            RETURN NEXT mon_record ;
          END LOOP;
      RETURN;    
END
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

---

www.marchegouro.com

Marc Cousin

Membre

Re : Union Multiple

Déjà, pour commencer, le support de la version 8.1 s'arrête ce mois-ci. Donc si c'est pour un nouveau développement, c'est une mauvaise idée. Ceci dit, essayons de répondre quand même à la question

Que voulez vous faire ? À la lecture du code, ce n'est pas évident. Pouvez vous l'expliquer ?

---

Marc.

baradji

Membre

Re : Union Multiple

Salut Marc,
Comme expliqué dans mon message precedent,j'ai plusieurs union a faire dans une fonction pl-pgsql.
pour le moment, ca marche car je fais l'union de tous les select.mais par le futur ,on risque de perdre des infos car le nombre peut augmenter.
j'aimerai savoir simplifier ce code en utilisant une boucle qui varie par exemple de 1 à 20.

Merci

---

www.marchegouro.com

Marc Cousin

Membre

Re : Union Multiple

N'ayant pas la même structure de table, voici une version 'simple' montrant la méthode  à appliquer :

CREATE or replace function test_union () returns setof test language plpgsql as
$$
DECLARE
  i int;
  vrec record;
BEGIN
  FOR i IN 1..10 LOOP
    FOR vrec IN SELECT * FROM test LOOP
      RETURN NEXT vrec;
    END LOOP;
  END LOOP;
END;
$$
;

Ici, on a une table test, qu'on renvoie 10 fois intégralement. La requête peut être à peu près n'importe quoi…
Si on veut faire une requête dynamique, il faut faire un FOR … IN EXECUTE 'ma requête dynamique'.

Il suffit donc, dans la boucle, d'exécuter 20 fois la requête, en modifiant l'ordre SQL pour s'adapter à la variable de boucle i.

---

Marc.

baradji

Membre

Re : Union Multiple

Merci Marc, ton exemple m'a bien inspiré, voici que je j'ai fais,ca marche bien.

CREATE OR REPLACE FUNCTION "public"."absence_export_fonc7" (
  integer,
  integer
)
RETURNS SETOF "public"."absence_export_type1" AS
$body$
DECLARE
mon_record absence_export_type1;
annee ALIAS FOR $1;
mois ALIAS FOR $2;
compteur SMALLINT;
BEGIN 
      FOR compteur IN 1..(SELECT count(rab_id) FROM ref_abs_gipssi_kronos) LOOP
       FOR mon_record IN   
          (SELECT s.csa_id, s.csa_matricule, s.csa_nom, s.csa_prenom, k.rab_libelle_gipssi,
          To_char (ap.apa_act_date, 'DD/MM/YYYYA') as date_abs,
          To_char (ap.apa_act_date, 'YYYY') as date_acquisition
          From com_salaries s, act_prevision_activites ap, ref_abs_gipssi_kronos k
          where ap.apa_csa_id = s.csa_id
          and EXTRACT(YEAR FROM ap.apa_act_date)=annee
          And EXTRACT(MONTH FROM ap.apa_act_date) =mois
          And ap.apa_act_am = k.rab_code_kronos
          And k.rab_id = compteur and k.rab_util =TRUE
          And s.csa_mode_facture = 0) LOOP
            RETURN NEXT mon_record ;
          END LOOP;

          FOR mon_record IN
          (Select s.csa_id, s.csa_matricule, s.csa_nom, s.csa_prenom, k.rab_libelle_gipssi,
          To_char (ap.apa_act_date, 'DD/MM/YYYYP') as date_abs,
          To_char (ap.apa_act_date, 'YYYY') as date_acquisition
          From com_salaries s, act_prevision_activites ap, ref_abs_gipssi_kronos k
          where ap.apa_csa_id = s.csa_id
          and EXTRACT(YEAR FROM ap.apa_act_date)=annee
          And EXTRACT(MONTH FROM ap.apa_act_date) =mois
          And ap.apa_act_pm = k.rab_code_kronos
          And k.rab_id = compteur and k.rab_util =TRUE
          And s.csa_mode_facture = 0) LOOP
            RETURN NEXT mon_record ;
          END LOOP;    
   END LOOP;
END
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

Dernière modification par baradji (11/11/2010 14:58:22)

---

www.marchegouro.com

baradji

Membre

Re : Union Multiple

salut,
je voulais utiliser record comme ceci

CREATE OR REPLACE FUNCTION "public"."absence_export_fonc8" (
  integer,
  integer
)
RETURNS SETOF "public"."absence_export_type1" AS
$body$
DECLARE
mon_record absence_export_type1;
annee ALIAS FOR $1;
mois ALIAS FOR $2;
compteur record;
BEGIN 
      FOR compteur IN (SELECT (rab_id) FROM ref_abs_gipssi_kronos ORDER BY rab_id) LOOP

mais quand j'execute la requete,ca me ressort l'erreur suivant

ERROR:  operator does not exist: smallint = record
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.

J'utilise postgresql 8.1.quand je fais des castes, ca ne fonctionne pas

---

www.marchegouro.com

gleu

Administrateur

Re : Union Multiple

Pourquoi ne peut pas mettre compteur de type integer et faire plutôt un :

FOR compteur IN SELECT rab_id FROM ref_abs_gipssi_kronos ORDER BY rab_id LOOP

(remarquez l'absence de parenthèses)

---

Guillaume.

baradji

Membre

Re : Union Multiple

Salut,
Merci pour ta reponse, j'ai essayé ça aussi, mais lorsque on met cela, le programme genere des erreurs à la compilation.

ERROR:  loop variable of loop over rows must be record or row variable at or near "LOOP"
LINE 8: ...ELECT rab_id FROM ref_abs_gipssi_kronos ORDER BY rab_id LOOP

---

www.marchegouro.com

gleu

Administrateur

Re : Union Multiple

Encore une bonne raison pour ne pas utiliser une version 8.1

Bref, pour que ça fonctionne en 8.1, vous devez faire la boucle ainsi

FOR compteur IN SELECT rab_id FROM ref_abs_gipssi_kronos ORDER BY rab_id LOOP

Et vous récupérez la valeur rab_id dans la variable "compteur.rab_id".

---

Guillaume.

baradji

Membre

Re : Union Multiple

Merci beaucoup Gleu et aussi Marc.
votre contribution m'a beaucoup aidé à avancer dans mon projet.
j'ai demandé à mes chefs de passer à une version supérieure de postgresql.
que me conseiller vous?
on est 8.1, faut il passer à 8.2 ou 8.3 ou supérieurs?

Merci

---

www.marchegouro.com

gleu

Administrateur

Re : Union Multiple

Pour faire à mi-courses... aller directement sur la dernière, la 9.0.

Évidemment, pensez à bien tester l'applicatif avant.

---

Guillaume.