Vous n'êtes pas identifié(e).
Pages : 1
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
Hors ligne
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'.
Marc.
Hors ligne
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;
Hors ligne
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.
Hors ligne
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
Hors ligne
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.
Hors ligne
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)
Hors ligne
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
Hors ligne
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.
Hors ligne
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
Hors ligne
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 LOOPEt vous récupérez la valeur rab_id dans la variable "compteur.rab_id".
Guillaume.
Hors ligne
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
Hors ligne
Pour faire à mi-courses... aller directement sur la dernière, la 9.0.
Évidemment, pensez à bien tester l'applicatif avant.
Guillaume.
Hors ligne
Pages : 1