Journal PostgreSQL: écrire une fonction plpgsql

Posté par  .
Étiquettes :
0
30
déc.
2003
Je me suis bien amusé à écrire une fonction récursive pour récupérer un chemin sous PostgrSQL, je vous en fais profiter.

On a :
skc=# SELECT id,name,parent FROM dir;
.id.|.name.|.parent
---+--------+---------
..0.|./.........|......0...
..1.|.home.|......0...
..2.|.etc.....|......0...
..3.|.skc.....|......1...
(4 rows)

Et ça donne :

skc=# select get_path(1); -> /home/
skc=# select get_path(3); -> /home/skc/
skc=# select get_path(4); -> ERROR: dir.id 4 not found

Installer plpgsql

Sur ma Debian woody, plpgsql n'était pas déclaré dans la base de donnée, il suffit de lancer
/usr/lib/postgresql/bin/createlang [--echo] plpgsql skc
pour créer le handler du langage dans la base skc, --echo permet de voir les commandes qui sont effectivement exécutées.

La fonction

CREATE OR REPLACE FUNCTION get_path(INTEGER) RETURNS VARCHAR AS '
DECLARE
dir_id ALIAS FOR $1;
dir_name VARCHAR;
dir_pid INTEGER;
BEGIN
SELECT INTO dir_name name FROM dir WHERE id=dir_id;
IF NOT FOUND THEN
RAISE EXCEPTION ''dir.id=% not found'', dir_id;
END IF;
IF dir_id = 0
THEN
RETURN dir_name;
ELSE
SELECT INTO dir_pid parent FROM dir WHERE id=dir_id;
RETURN get_path(dir_pid) || dir_name || ''/'';
END IF;
END;
' LANGUAGE 'plpgsql';

Voila, si vous voyez comment améliorer la fonction ou si vous avez une fonction équivalente pour une autre base de données, n'hésitez pas à poster un commentaire.

  • # Re: PostgreSQL: écrire une fonction plpgsql

    Posté par  . Évalué à 5.

    Tu devrais lire ca :
    http://sqlpro.developpez.com/Tree/SQL_tree.html(...)

    Avec cette idee/bidouille/conception (appelle ca comme tu veux :-), tu recuperes ton chemin en une seule requete. La contrainte (expliquee aussi dans la page) c'est qu'il y a un petit peu plus de boulot a faire lors des ecritures (insert, update, delete) ; donc il faut voir si tu lis plus souvent que tu ecris ou l'inverse :-)

  • # Re: PostgreSQL: écrire une fonction plpgsql

    Posté par  (site web personnel) . Évalué à 1.

    Boarf, sous O.....*, il suffit d'écrire :

    
select PATH 
    
from (
    
        select ID, sys_connect_by_path(NAME, '/') PATH
    
        from  DIR
    
        start with PARENT = 0
    
        connect by prior ID = PARENT
    
        )
    
where ID =  monid;


    La sous requête décrit l'arborescence. Le sys_connect_by_path effectue le travail de reconstitution du chemin.

    La requête principale sert à ne sélectionner que la ligne qui nous intérssse.

    * : c'est du proprio, sapusétrémaljeussé. Mais peut-être qu'il y a (ou qu'il y aura un jour) un équivalent sous PostgreSQL (je ne connais pas cette base).

    • [^] # Re: PostgreSQL: écrire une fonction plpgsql

      Posté par  . Évalué à 1.

      SQL 99 prevoit un operateur de fermeture transitive (recursion) : WITH RECURSIVE blabla. Donc je suppose qu'un jour on aura ca sous postgresql et mysql. Par contre je ne sais toujours pas pourquoi je ne peux pas ecrire LEFT INNER JOIN (qui est standard SQL) sous O..... :-/

      • [^] # Re: PostgreSQL: écrire une fonction plpgsql

        Posté par  (site web personnel) . Évalué à 1.

        pourquoi je ne peux pas ecrire LEFT INNER JOIN (qui est standard SQL) sous O.....

        Peut être tout simplement parce que O..... est un éditeur de softs propriétaires, occupant une place très importante sur le marché et donc n'ayant aucun besoin de suivre un standard. Ca vous rappele quelque chose ?

        Il me semble qu'il y a un paquet d'autres exotismes non standards dans O..... .

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.