Forums PostgreSQL.fr

damalaan

Membre

requete pivot dynamique

Bonjour,

Je cherche une solution pour reproduire un pivot dynamique.
J'ai trouvé une fonction qui utilise `row_to_json`(https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html), mais je ne comprends pas la mise en pratique.

La fonction :

CREATE FUNCTION dynamic_pivot(central_query text, headers_query text)
 RETURNS refcursor AS
$$
DECLARE
  left_column text;
  header_column text;
  value_column text;
  h_value text;
  headers_clause text;
  query text;
  j json;
  r record;
  curs refcursor;
  i int:=1;
BEGIN
  -- find the column names of the source query
  EXECUTE 'select row_to_json(_r.*) from (' ||  central_query || ') AS _r' into j;
  FOR r in SELECT * FROM json_each_text(j)
  LOOP
    IF (i=1) THEN left_column := r.key;
      ELSEIF (i=2) THEN header_column := r.key;
      ELSEIF (i=3) THEN value_column := r.key;
    END IF;
    i := i+1;
  END LOOP;

  --  build the dynamic transposition query (based on the canonical model)
  FOR h_value in EXECUTE headers_query
  LOOP
    headers_clause := concat(headers_clause,
     format(chr(10)||',min(case when %I=%L then %I::text end) as %I',
           header_column,
	   h_value,
	   value_column,
	   h_value ));
  END LOOP;

  query := format('SELECT %I %s FROM (select *,row_number() over() as rn from (%s) AS _c) as _d GROUP BY %I order by min(rn)',
           left_column,
	   headers_clause,
	   central_query,
	   left_column);

  -- open the cursor so the caller can FETCH right away
  OPEN curs FOR execute query;
  RETURN curs;
END 
$$ LANGUAGE plpgsql;

je voudrais qu' à l'utilisation cette requête

select dynamic_pivot(
	'select id_echantillon, 
		 	id_type_analyse , 
		 	max(a.valeur) valeur 
		 	from  t_analyse" a where a.created >''2024-02-01''
		 	and  a.valeur IS NOT null and a."etat" = ''valide''
		 	group by id_echantillon, id_type_analyse',
	'select id_type_analyse from t_type_analyse') as cur;

si j'ai un jeu de données renvoyé par la première partie de la fonction comme celui ci:

id_echantillon	id_type_analyse 	valeur
1471575		52			20.8
1471575		53			46.4
1471575		54			0.02
1471575		55			0
1471576		1			75.8
1471576		2			59.2
1471576		3			532000
1471576		4			606

il me faudrait un tableau final comme cela :

id_echantillon	1	2	3		4		52		53		54		55
141575								20.8	        46.4	        0.02           	0
147576		75.8	59.2	532000         606							

gleu

Administrateur

Re : requete pivot dynamique

Pour les pivots, l'extension tablefunc est intéressante : https://www.postgresql.org/docs/16/tablefunc.html

---

Guillaume.

damalaan

Membre

Re : requete pivot dynamique

je connais cette extension mais qui répond pas aux attentes à savoir que le nombre et le nom des colonnes doivent être connus. Et c'est là tout le problème....c'est que je ne le sais pas à l'avance

dverite

Membre

Re : requete pivot dynamique

Il y a une version en français ici:
https://blog-postgresql.verite.pro/2018 … pivot.html

Cette fonction dynamic_pivot() renvoie un curseur qu'il faut parcourir avec FETCH pour lire les résultats pivotés, comme montré dans l'exemple de l'article.

Dans votre cas en quoi ces résultats diffèrent des résultats attendus ?

---

@DanielVerite
http://blog-postgresql.verite.pro/

damalaan

Membre

Re : requete pivot dynamique

En fait je ne comprends pas comment utliser le

  FETCH ALL FROM :"curseur"; 

j'utilise DBeaver comme outil pour faire mes requêtes et voici le retour que j'ai :

appel à la fonction :

select dynamic_pivot(
	'select id_echantillon, 
		 	id_type_analyse , 
		 	max(a.valeur) valeur 
		 	from t_analyse a where a.created >''2024-02-01''
		 	and  a.valeur IS NOT null and a."etat" = ''valide''
		 	group by id_echantillon, id_type_analyse',
	'select id_type_analyse from t_type_analyse') as curseur

curseur           |
------------------+
<unnamed portal 3>|

Ensuite quand j'exécute

 FETCH ALL FROM  curseur; 

j'ai une erreur

SQL Error [34000]: ERROR: cursor "curseur" does not exist

j'ai essayé aussi

 FETCH ALL FROM  "<unnamed portal 3>" 

mais l'erreur est la même

dverite

Membre

Re : requete pivot dynamique

Dans l'exemple ci-dessus le curseur retourné par la fonction s'appelle "<unnamed portal 3>" et il faudrait faire le FETCH avec cette syntaxe:

 FETCH ALL FROM "<unnamed portal 3>" ;

Par opposition, l'autre syntaxe FETCH ALL FROM :"curseur";  est faite pour le client psql et ne peut pas fonctionner avec DBeaver parce que curseur est une variable psql dans ce contexte.

Il est aussi possible à l'appelant d'imposer son nom de curseur à la fonction pour simplifier ça, dans ce cas utiliser la variante ici:

https://github.com/dverite/postgresql-f … cursor.sql

la fonction dynamic_pivot() prend un 3eme argument qui est le nom de curseur. Avec cette version, la séquence ci-dessous devrait fonctionner quel que soit le client SQL:

BEGIN;

select dynamic_pivot(
	'select id_echantillon, 
		 	id_type_analyse , 
		 	max(a.valeur) valeur 
		 	from t_analyse a where a.created >''2024-02-01''
		 	and  a.valeur IS NOT null and a."etat" = ''valide''
		 	group by id_echantillon, id_type_analyse',
	'select id_type_analyse from t_type_analyse',
   'moncurseur');

FETCH ALL FROM "moncurseur";

COMMIT;

---

@DanielVerite
http://blog-postgresql.verite.pro/

damalaan

Membre

Re : requete pivot dynamique

super merci beaucoup !!