Discussion :

[ymoreau]

Bonjour,
J'ai une table stockant des données avec une relation parent-enfant(s), les noms des éléments de cette table sont stockés dans une autre table. Les données représentes des balises XML.

tags (bigint id_tag PK, int id_tag_name, int parent_tag)
tag_names (int id_tag_name PK, varchar(256) tag_name)

Je voudrais récupérer tous les ancêtres d'un élément donné et en faire une chaine de type XPath, j'ai trouvé un article donnant des exemples de requêtes faisant exactement cela, j'ai tenté de reprendre le code en l'adaptant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE FUNCTION get_path(id INTEGER) RETURNS VARCHAR AS $$
  SELECT '/' || path FROM
    (WITH RECURSIVE pathto(path, id) AS (
      SELECT tag_name, parent_tag FROM tags NATURAL JOIN tag_names WHERE id_tag = $1
      UNION
      SELECT names.tag_name || '/' || pathto.path, parent_tag
        FROM tags NATURAL JOIN tag_names AS names, pathto WHERE tags.id_tag = pathto.id)
      SELECT * FROM pathto) AS pathto(path, id)
    WHERE parent_tag IS NULL;
$$ LANGUAGE 'sql';

Et j'obtiens cette erreur

ERREUR: dans la requête récursive « pathto », la colonne 1 a le type character varying(256) dans le terme non récursif mais le type global character varying

J'ai donc tenté (pour voir) de changer le type renvoyé par la fonction en varchar(256), ou de faire un cast sur tag_name ligne 4, celui ligne 6, et les deux en même temps. L'erreur ne change pas.

Je ne vois pas où le problème se situe exactement, la phrase du message d'erreur ne me parait pas très claire. Si quelqu'un peut m'éclairer, merci d'avance !

[vmolines]

Bonjour,

Quand vous dites :

J'ai donc tenté (pour voir) de changer le type renvoyé par la fonction en varchar(256), ou de faire un cast sur tag_name ligne 4, celui ligne 6, et les deux en même temps. L'erreur ne change pas.

Vous avez fait un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT CAST(names.tag_name AS VARCHAR(256)) || '/' || pathto.path, parent_tag

ou un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT CAST(names.tag_name || '/' || pathto.path AS VARCHAR(256)), parent_tag

D'après votre énoncé, je dirais que vous avez testé ma première proposition. Cependant j'aurais opté pour ma 2ème proposition pour résoudre votre problème.

[ymoreau]

En effet je n'avais essayé que la première proposition, par contre en faisant un cast en VARCHAR pour que cela corresponde au retour de la fonction. Je viens d'essayer de convertir l'ensemble de l'expression comme suggéré, l'erreur est toujours la même.

Si j'essaye de tout convertir en varchar(256) ça fonctionne par contre, mais ça ne m'arrange pas trop, car c'est le nom de chaque élément qui est limité à 255 et donc leur concaténation pourrait dépasser cette limite.
C'est peut être ma syntaxe CAST(tag_name AS VARCHAR) qui n'est pas bonne ? pour obtenir le type global character varying.

[vmolines]

Si vous pouviez donner les requêtes testées et les messages obtenus au lieu de retranscrire tout ça en phrases, ce serait pas mal pour la compréhension.

Concernant la fonction, c'est anecdotique, commencez par régler les problèmes de la requête en ne travaillant que sur la requête jusqu'à ce qu'elle fonctionne et donne le résultat escompté. Vous verrez son encapsulation dans une fonction dans un deuxième temps si vous voulez bien.

Concernant la requête récursive, vous avez bien compris, le but est de faire coïncider les types de la partie non récursive (requête avant l'union aussi appelé "anchor" dans le jargon de MS SQL) avec ceux de la partie récursive (requête après l'union).

Pour le problème de restriction de longueur imposé par le type VARCHAR de tag_name de la partie non récursive, vous pouvez caster celui-ci vers une longueur plus grande. Cependant, en restant sur un type VARCHAR, vous êtes forcément limité par la définition de sa taille initiale. Comme votre arborescence n'a aucune limite en nombre de lignes, il y a une contradiction.

Solutions :
- Castez vers un VARCHAR de longueur plus importante que vous estimez suffisante pour votre résultat
- Castez vers du TEXT qui n'impose pas de limite de taille

[ymoreau]

Désolé si je n'étais pas clair, mon idée de départ était de faire un cast sur les varchar(256) vers du varchar tout court qui selon la doc est illimité en taille. Mais effectivement il ne m'était pas venu à l'esprit qu'on pouvait aussi passer ça en text, merci.

C'est donc ce que j'ai fait, et tout fonctionne bien, voilà la procédure modifiée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE FUNCTION get_path(id INTEGER) RETURNS TEXT AS $$
  SELECT '/' || path FROM
    (WITH RECURSIVE pathto(path, id) AS (
      SELECT CAST(tag_name AS TEXT), parent_tag FROM tags NATURAL JOIN tag_names WHERE id_tag = $1
      UNION
      SELECT CAST(names.tag_name || '/' || pathto.path AS TEXT), parent_tag
        FROM tags NATURAL JOIN tag_names AS names, pathto WHERE tags.id_tag = pathto.id)
      SELECT * FROM pathto) AS pathto(path, id)
    WHERE id IS NULL;
$$ LANGUAGE 'sql';

J'ai aussi changé le dernier where, car je m'étais trompé, l'erreur n'est apparue qu'après avoir corrigé les conflits de type.

Merci beaucoup pour votre aide !