Un UPDATE un peu particulier [Résolu]

[wacky]

Bonjour,

Je fais appel à votre connaissance pour tenter de résoudre une incompréhension:
Pour la clarté du problème énoncé, je vais rester très théorique car rentrer dans le détail n'apporterai rien et risquerait au contraire de compliquer les choses:

Soit une table T contenant les colonnes A et B (invariables)
Soit une fonction F( A, B ) dont la valeur de retour représente une table T'

Je souhaite stocker le meilleur résultat de la table T' retournée par la fonction F( A, B ) pour chaque ligne de T dans une colonne C

Pour se faire j'ai essayé plusieurs méthodes telles que :

- UPDATE T SET C = ( SELECT X FROM F( A, B ) ORDER BY X DESC LIMIT 1 )
=> Error Heading

- Créer un trigger ON INSERT/UPDATE pour que C = ( SELECT X FROM F( A, B ) ORDER BY X DESC LIMIT 1 )
- Faire un UPDATE blanc du type UPDATE T SET A = A pour forcer le calcul de C
=> Error Heading

...

Bref je pense que je dois passer à côté de quelque chose qui m'échappe.
L'erreur de retour ne me permet pas de comprendre l'origine de mon problème, je ne vois pas à quoi cela peut correspondre

Si quelqu'un pense pouvoir me venir en aide, je lui en serais très reconnaissant

Cordialement

[estofilo]

Quelle est la déclaration de la fonction F?

[wacky]

Pour la fonction F, je la déclare de la manière suivante:

Code :

CREATE OR REPLACE FUNCTION F( A numeric, B numeric ) RETURNS SETOF type_x AS ...

Ou type_x est un type que j'ai créé avec:

Code :

1
2
3
CREATE TYPE type_x AS (
  X numeric
);

Lorsque je fais un:

Code :

SELECT X FROM F( a, b )

(où a et b sont des valeurs test) j'ai bien un tableau de valeurs
|_X_|
| 10 |
| 15 |
| 12 |

La fonction F( A, B ) est donc valide (les valeurs qu'elle retourne sont correctes) je souhaite dans cet exemple stocker la valeur 15 dans la colonne C de la ligne de T pour laquelle A = a et B = b

Merci pour votre aide précieuse

[estofilo]

Le problème est que quand tu fais

Code :

 SELECT x FROM f(a,b)

x est du type numeric et non pas du type type_x.

Ca doit passer avec:

Code :

UPDATE t SET c=(SELECT row(x)::type_x FROM f(a,b) ORDER BY x DESC LIMIT 1)

[wacky]

Merci pour votre réponse,

L'ajout de ::type_x ne change pas le problème malheureusement
Je souhaite juste apporter une petite correction dans l'énoncé, le message d'erreur n'est pas "Error Header" mais "ERREUR: Invalid heading" précisément (excusez moi pour ce manque de rigueur)

Ayant fait part de ce problème à un ami, il pense que le problème provient du fait que l'UPDATE attend une valeur sans entête après "C =" et que le fait de retourner une valeur provenant d'un tableau via un SELECT, cette dernière est munie d'une entête que l'UPDATE ne comprend pas, d'où le message d'erreur Invalid Heading (d'après ce que j'en ai compris).

Sauriez-vous m'en dire un peu plus à ce propos ? Merci encore pour votre aide

Lorsque je tape "postgresql" + "invalid heading" sur Google, je prend peur par le nombre de résultats

[SQLpro]

Code :

UPDATE t SET c=(SELECT MAX(X) AS XMAX FROM f(a,b))

???

A +

[wacky]

Code :

UPDATE t SET c=(SELECT MAX(X) AS XMAX FROM f(a,b))

=> ERREUR: Invalid heading ( Sans succès, merci pour votre aide )

Je finis pas me demander si cette forme d'écriture est possible, vous-même avez-vous déjà eu l'occasion d'avoir recours à ce type de procédé pour mettre à jour un champ (invariable par la suite) sur un grand nombre de lignes?

J'ai bien essayé une procédure plpgsql du type:

Code :

1
2
3
4
5
FOR r IN SELECT id, A, B FROM T
LOOP
X := F( r.A, r.B );
UPDATE T SET C = X WHERE id = r.id;
END LOOP;

mais sans succès non plus...

[estofilo]

De mémoire je n'ai jamais vu l'erreur "Invalid heading".
Par ailleurs ça fonctionne pour moi, voici un test avec postgresql 8.4:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TYPE type_x AS (x numeric);
 
CREATE FUNCTION f() returns setof type_x AS $$
DECLARE
 v type_x;
BEGIN
 v:=row(1); RETURN next v;
 v:=row(2); RETURN next v;
 RETURN;
END; $$ LANGUAGE plpgsql;
 
CREATE TABLE t(a type_x);
 
INSERT INTO t VALUES (row(10)::type_x);
 
UPDATE t SET a=(SELECT row(x)::type_x FROM f() ORDER BY x DESC LIMIT 1);

Il n'y a pas d'erreur et le résultat est:

Code :

1
2
3
4
5
SELECT * FROM t;
  a  
-----
 (2)
(1 row)

[wacky]

Bonsoir estofilo,

Ma fonction F( A, B ) prend pour paramètres les champs A et B de la table T dans le UPDATE

[estofilo]

A toi de partir de mon exemple qui passe sans erreur pour le transformer pas à pas en ton cas réel qui provoque l'erreur.

[wacky]

Merci pour ta réponse, j'ai réadapté un exemple simple pour vérifier que ce procédé ne pose pas de problème avec une fonction qui calcule les solutions à l'équation x^2 = nombre (elle n'a ici aucun intérêt c'est pour l'exemple)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE t ( nombre numeric, solution1 numeric, solution2 numeric );
INSERT INTO t ( nombre, solution1, solution2 ) VALUES ( 4, NULL, NULL );
INSERT INTO t ( nombre, solution1, solution2 ) VALUES ( 9, NULL, NULL );
INSERT INTO t ( nombre, solution1, solution2 ) VALUES ( 16, NULL, NULL );
 
CREATE TYPE type_x AS ( solution numeric );
 
CREATE FUNCTION f( nombre numeric ) RETURNS SETOF type_x AS $$
  DECLARE
    x type_x;
  BEGIN
    x.solution :=  SQRT( nombre ); RETURN next x;
    x.solution := -SQRT( nombre ); RETURN next x;
    RETURN;
  END;
$$ LANGUAGE plpgsql;
 
UPDATE t SET solution1 = ( SELECT solution FROM f( nombre ) ORDER BY solution DESC LIMIT 1 );
UPDATE t SET solution2 = ( SELECT solution FROM f( nombre ) ORDER BY solution ASC  LIMIT 1 );

Et cela fonctionne parfaitement.
Je peux déjà mettre hors de cause la façon d'écrire (un peu particulière?) mon UPDATE.
Le problème vient de plus haut. Aussi je me pose la question si ma fonction f ne retourne aucune ligne, la colonne C prend elle pour valeur null? (sachant que null est bien autorisé dans la déclaration de ma table T pour la colonne C)

Je vais continuer à fouiller pas à pas, en te remerciant pour ton aide

[wacky]

J'ai trouvé ma STUPIDE (comme trop souvent malheureusement) erreur :

Ma fonction F(A,B) n'était pas si parfaite que ça, il y avait des valeurs A et B pour lesquelles elle retournait un message d'erreur que mon UPDATE ne retournait pas à son tour. Merci infiniment à vous deux de m'avoir mis sur la voix (je m'en veux tellement d'être passé à côté d'un truc comme ça).

En tout cas j'aurais appris beaucoup avec cette erreur à force de chercher les causes possibles