Forums PostgreSQL.fr

XPBT

Membre

Résultat d'une fonction

Bonjour,
voici mon problème du jour:
j'ai une table ma_table_donnees avec trois colonnes champ (character varying), horodate(timestamp without time zone) et duree(numeric), avec une clé primaire (champ, horodate)). J'ai besoin de faire une somme de ces durees pour certains champs, à toutes les horodates. Je souhaite injecter cette somme dans une autre table ma_table_resultat, qui contient deux colonnes horodate et resultat(smallint).
J'ai donc créé une fonction:

CREATE OR REPLACE FUNCTION "ma_fonction"(timestamp without time zone)
  RETURNS smallint AS
$BODY$
    DECLARE RESULTAT numeric;
        t1 numeric;
        t2 numeric;
        t3 numeric;
        t4 numeric;
        t5 numeric;
BEGIN
SELECT INTO t1 duree FROM ma_table_donnees WHERE champ = x1 AND horodate = $1;       
SELECT INTO t2 duree FROM ma_table_donnees WHERE champ = x2 AND horodate = $1;
SELECT INTO t3 duree FROM ma_table_donnees WHERE champ = x3 AND horodate = $1;
SELECT INTO t4 duree FROM ma_table_donnees WHERE champ = x4 AND horodate = $1;
SELECT INTO t5 duree FROM ma_table_donnees WHERE champ = x5 AND horodate = $1;
RESULTAT = t1*0.46+t2+t3+t4+t5*1.19;
RETURN round(RESULTAT);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION "ma_fonction"(timestamp without time zone)
  OWNER TO postgres;

Ensuite j'exécute la commande:

INSERT INTO ma_table_resultat
        SELECT horodate, ma_fonction(horodate) FROM ma_table_donnees;

Ça fonctionne, j'obtiens bien le résultat attendu pour chaque horodate. Le problème est que j'obtiens ce résultat en une vingtaine d'exemplaires (ma_table_resultat contient une vingtaine de lignes avec un résultat identique pour chaque horodate). Évidemment ce n'est pas le but, j'aimerais bien avoir une seule ligne et un seul résultat par horodate.
Je ne connais pas assez bien le fonctionnement de Postgre et du langage SQL pour comprendre ce qui se passe lors de l'exécution de cette fonction et qui conduit à dupliquer les résultats. Quelqu'un pourrait-il éclairer ma lanterne?
Merci d'avance.

gleu

Administrateur

Re : Résultat d'une fonction

Il faudrait nous montrer un exemple complet. La fonction semble bien écrite, la requête d'insertion aussi. Pour moi, ça doit fonctionner ainsi.

---

Guillaume.

XPBT

Membre

Re : Résultat d'une fonction

Un exemple complet? De quels autres renseignements auriez-vous besoin?

dverite

Membre

Re : Résultat d'une fonction

L'objectif poursuivi semble être une aggrégation sur l'horodate, l'aggrégat étant le résultat de la fonction montrée.

Dans ce cas, ça ne peut pas marcher tel que montré, d'abord parce qu'une fonction aggrégat nécessite un CREATE AGGREGATE et un codage spécifique, ensuite parce que la requête doit avoir un GROUP BY.

Ce qu'il faut comprendre c'est que quand on fait "select fonction(x) from table" et que la table contient 20 lignes, on obtient 20 résultats. Peu importe que la fonction elle-même aille chercher d'autres lignes dans la même table et fasse des additions.

Le résultat voulu et qui est conforme à la logique SQL se rapproche peut-être de ça (ne nécessite pas fonction plpgsql):

select 
 horodate,
 sum(
   case when champ=x1 then t1*0.46
            when champ=x2 then t2
           ... etc...
            else 0 end
    )
 GROUP BY horodate;

Ce qui resterait assez bizarre c'est ce que représentent ces x1, x2,... On s'attendrait à des constantes litérales (nom du champ dans une table Entité-valeur) alors que syntaxiquement ici c'est un nom de colonne.

---

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

XPBT

Membre

Re : Résultat d'une fonction

Merci pour ces réponses.

L'objectif poursuivi semble être une aggrégation sur l'horodate, l'aggrégat étant le résultat de la fonction montrée.

Une agrégation de plusieurs valeurs à la même horodate oui.

Dans ce cas, ça ne peut pas marcher tel que montré, d'abord parce qu'une fonction aggrégat nécessite un CREATE AGGREGATE et un codage spécifique, ensuite parce que la requête doit avoir un GROUP BY.

Le problème est que je n'ai pas besoin de TOUTES les données de la même horodate. Sinon effectivement il aurait été simple de faire SELECT sum(...) FROM TABLE GROUP BY horodate.

Ce qu'il faut comprendre c'est que quand on fait "select fonction(x) from table" et que la table contient 20 lignes, on obtient 20 résultats. Peu importe que la fonction elle-même aille chercher d'autres lignes dans la même table et fasse des additions.

Je ne suis pas sûr de bien comprendre. Ma table de données doit faire 1000 à 2000 lignes, la fonction devrait donc retourner 1000 lignes par horodate et pas 20 non?

Le résultat voulu et qui est conforme à la logique SQL se rapproche peut-être de ça (ne nécessite pas fonction plpgsql):

select 
 horodate,
 sum(
   case when champ=x1 then t1*0.46
            when champ=x2 then t2
           ... etc...
            else 0 end
    )
 GROUP BY horodate;

Est-ce que cette formulation "case when ... then" renvoie bien la valeur t1 qui correspond à la ligne pour laquelle champ = x1? Ce n'a pas l'air évident.

Ce qui resterait assez bizarre c'est ce que représentent ces x1, x2,... On s'attendrait à des constantes litérales (nom du champ dans une table Entité-valeur) alors que syntaxiquement ici c'est un nom de colonne.

x1, etc sont bien des valeurs de la colonne "champ"(character varying).

XPBT

Membre

Re : Résultat d'une fonction

A priori en modifiant ma requête d'insertion comme ceci:

INSERT INTO ma_table_resultat
        SELECT DISTINCT(horodate), ma_fonction(horodate) FROM ma_table_donnees;

ça fonctionne. Ce n'est pas très élégant ni optimal à ce qu'il me semble, mais cela permet d'éluder le problème. Qu'en pensez-vous?

dverite

Membre

Re : Résultat d'une fonction

Ma table de données doit faire 1000 à 2000 lignes, la fonction devrait donc retourner 1000 lignes par horodate et pas 20 non?

En soi la fonction ne renvoie aucune ligne, elle renvoie une valeur scalaire vu qu'elle est déclarée en RETURNS smallint.
Des fonctions qui renvoient des lignes, ça existe par ailleurs avec une déclaration du type RETURNS SETOF type mais ce n'est pas le sujet ici.

La requête SELECT en revanche, retourne des lignes, et en l'occurrence SELECT mafonction(x) FROM matable renvoie donc autant de lignes qu'il y en a dans matable, et la fonction est appelée autant de fois qu'il y a de lignes dans la table.

Pour une solution efficace, d'après la réponse précédente, le but est bien ce que je pensais et donc la fonction n'est pas utile, tout peut être aggrégé en une seule requête comme proposé plus haut, le SUM(CASE...) donnant bien le résultat attendu. Ce n'est pas juste une optimisation: dans la question initiale, la manière donc la fonction est combinée avec la requête pour calculer un aggrégat est réellement absurde en logique SQL.

---

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

XPBT

Membre

Re : Résultat d'une fonction

Merci beaucoup pour votre réponse.

Ce n'est pas juste une optimisation: dans la question initiale, la manière donc la fonction est combinée avec la requête pour calculer un aggrégat est réellement absurde en logique SQL.

C'est bien mon problème: je ne suis pas familier de "la logique "SQL"!

tout peut être aggrégé en une seule requête comme proposé plus haut, le SUM(CASE...) donnant bien le résultat attendu.

Pas tout à fait cependant: la solution passant par une fonction permet de n'afficher aucun résultat lorsque l'un des éléments de la somme est absent, ce qui n'est pas le cas avec sum(case...), qui renvoie systématiquement un résultat, que tous les termes de la somme soient présents ou non.
J'avais au départ choisi de passer par une fonction car ce que j'ai décrit plus haut ne représente qu'une étape intermédiaire: mon but est de créer une requête qui, à l'horodate h, renvoie la somme suivante:
               la durée t1 à l'horodate h
            + la durée t2 à l'horodate h+t1
            + la durée t3 à l'horodate h+t1+t2
            etc.
Je ne vois pas bien comment écrire cette itération sans passer par une fonction comme celle-ci, qui a priori fait bien le boulot :

CREATE OR REPLACE FUNCTION "ma_fonction2"(timestamp without time zone)
  RETURNS smallint AS
$BODY$
	DECLARE RESULTAT numeric;
		t1 numeric;
		t2 numeric;
		t3 numeric;
		t4 numeric;
		t5 numeric;
BEGIN
SELECT INTO t1 duree FROM ma_table_donnees WHERE champ = 'x1' AND horodate = $1;		
SELECT INTO t2 duree FROM ma_table_donnees WHERE champ = 'x2' AND horodate = $1 + (round (0.46*t1/60)) * interval '1 minute';
SELECT INTO t3 duree FROM ma_table_donnees WHERE champ = 'x3' AND horodate = $1 + (round ((0.46*t1 + t2)/60)) * interval '1 minute';
SELECT INTO t4 duree FROM ma_table_donnees WHERE champ = 'x4' AND horodate = $1 + (round ((0.46*t1 + t2 + t3)/60)) * interval '1 minute';
SELECT INTO t5 duree FROM ma_table_donnees WHERE champ = 'x5' AND horodate = $1 + (round ((0.46*t1 + t2 + t3 + t4)/60)) * interval '1 minute';
RESULTAT = t1*0.46+t2+t3+t4+t5*1.19;
RETURN round(RESULTAT);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION "ma_fonction2"(timestamp without time zone)
  OWNER TO postgres;

Cette fonction vous paraît-elle également absurde? Comment aurais-je dû procéder?

dverite

Membre

Re : Résultat d'une fonction

Cette fonction vous paraît-elle également absurde? Comment aurais-je dû procéder?

Je ne sais pas, c'est totalement différent de la version précédente puisque là chaque valeur t2/t3/etc.. dépend de la précédente avec une sorte de chaînage.
Dans la version d'avant, on a l'impression que horodate est une clef mais dans celle-là non et ça change tout.

---

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