Technique | Écrire et utiliser des fonctions retournant une valeur de type composite (ROWTYPE)

Par RockyRoad le 27/08/2008 - 19:42

Bien que nouvelle utilisatrice de PG, j'ai choisi de présenter un mini-article sur l'utilisation des ROWTYPE dans PL/pgSQL, car je crois qu'il pourrait être utile à beaucoup.

Je n'ai hélas pas beaucoup de temps à y consacrer, mais je compte sur votre participation pour m'aider à le clarifier.

En effet je n'ai rien trouvé de tel sur le web et j'ai dû passer du temps, pour "trouver le pot aux roses", c'est-à-dire une syntaxe correcte utilisable pour mon application (GPL) que je vous dévoilerai plus tard.

Merci à Guillaume Lelarge pour son aide. Au-delà de partager son expérience, il s'est joint activement à mes réflexions et suggéré les solutions qui m'ont mise sur la bonne voie.

Lorsqu'on crée une table, PG crée automatiquement un type (structure) décrivant la composition d'une ligne, c'est-à-dire la liste des champs. Si on veut manipuler des lignes indépendamment d'une table, on peut déclarer un type, par exemple:

test=>

DROP TYPE IF EXISTS names CASCADE;

CREATE TYPE names AS (

       first_name varchar,

       last_name varchar,

       age integer

);

L'interpréteur psql nous répond:

CREATE TYPE

La fonction suivante construit une ligne du type 'names' :

test=>

CREATE OR REPLACE FUNCTION BuildName(_first varchar, _last varchar) RETURNS names AS $$

DECLARE

        result names;

BEGIN

        RAISE NOTICE 'BuildName(''%'', ''%'')', _first, _last;

        result.first_name = _first;

        result.last_name = _last;

        return result;

END;

$$ LANGUAGE plpgsql;

CREATE FUNCTION

test=>

SELECT BuildName('Pierre', 'Dupond');

On reçoit le message, puis le résultat en une seule colonne

NOTICE:  BuildName('Pierre', 'Dupond')

    buildname

------------------

 (Pierre,Dupond,)

(1 row)

Si on veut distinguer les colonnes, conformément à la définition du type, il faut utiliser la syntaxe ().*

test=>

SELECT (BuildName('Pierre', 'Dupond')).*;

NOTICE:  BuildName('Pierre', 'Dupond')

NOTICE:  BuildName('Pierre', 'Dupond')

NOTICE:  BuildName('Pierre', 'Dupond')

 first_name | last_name | age

------------+-----------+-----

 Pierre     | Dupond    |

(1 row)

On obtient bien le résultat en 3 colonnes. Mais on reçoit 3 FOIS le message (il y a 3 champs).

Que se passe-t-il ?

Le comportement est identique avec une table:

test=>

DROP TABLE IF EXISTS person CASCADE;

CREATE TABLE person (

    id integer PRIMARY KEY,

    name varchar,

    data integer

);

NOTICE:  table "person" does not exist, skipping

DROP TABLE

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "person_pkey" for table "person"

CREATE TABLE

test=>

CREATE OR REPLACE FUNCTION BuildPerson(_num integer, _name varchar) RETURNS person AS $$

DECLARE

        result person;

BEGIN

        RAISE NOTICE 'BuildPerson(%, ''%'')', _num, _name;

        result.id = _num;

        result.name = _name;

        return result;

END;

$$ LANGUAGE plpgsql;

CREATE FUNCTION

test=>

SELECT BuildPerson(1, 'Dupond');

On reçoit le message, puis le résultat en une seule colonne

NOTICE:  BuildPerson(1, 'Dupond')

 buildperson

-------------

 (1,Dupond,)

(1 row)

Si on veut insérer les résultats dans la table, il faut adapter la syntaxe pour obtenir des champs distincts

test=>

SELECT (BuildPerson(2, 'Dupond')).*;

NOTICE:  BuildPerson(2, 'Dupond')

NOTICE:  BuildPerson(2, 'Dupond')

NOTICE:  BuildPerson(2, 'Dupond')

 id |  name  | data

----+--------+------

  2 | Dupond |

(1 row)

test=>

INSERT INTO person (SELECT (BuildPerson(2, 'Dupond')).*);

NOTICE:  BuildPerson(2, 'Dupond')

NOTICE:  BuildPerson(2, 'Dupond')

NOTICE:  BuildPerson(2, 'Dupond')

INSERT 0 1

On reçoit aussi 3 FOIS le message , mais la rangée n'est insérée qu'une fois

test=>

SELECT * FROM person;

 id |  name  | data

----+--------+------

  2 | Dupond |

(1 row)

Tout va bien ? On avance ... mais:

Si la fonction comporte elle-même une instruction INSERT, on peut avoir un problème:

test=>

CREATE OR REPLACE FUNCTION AddPerson(_num integer, _name varchar) RETURNS person AS $$

DECLARE

        result person;

BEGIN

        RAISE NOTICE 'AddPerson(%, ''%'')', _num, _name;

        result.id = _num;

        result.name = _name;

        INSERT INTO person SELECT result.*;

        return result;

END;

$$ LANGUAGE plpgsql;

CREATE FUNCTION

test=>

SELECT (AddPerson(3, 'Durand')).*;

NOTICE:  AddPerson(3, 'Durand')

NOTICE:  AddPerson(3, 'Durand')

ERROR:  duplicate key value violates unique constraint "person_pkey"

Eh oui ! Comme les messages répétés pouvaient nous le laisser prévoir, la fonction est appelée plusieurs fois, avec les mêmes arguments, ce que notre clé primaire interdit.

Remarquez que, la transaction ayant échoué, aucune ligne n'est finalement ajoutée.

test=>

SELECT * FROM person;

 id |  name  | data

----+--------+------

  2 | Dupond |

(1 row)

Pas de souci cependant si on évite la syntaxe ().* lors de l'invocation:

test=>

DELETE FROM person WHERE id=3;

SELECT AddPerson(3, 'Durand');

SELECT * FROM person;

DELETE 0

NOTICE:  AddPerson(3, 'Durand')

  addperson

-------------

 (3,Durand,)

(1 row)

 

 id |  name  | data

----+--------+------

  2 | Dupond |

  3 | Durand |

(2 rows)

Attention si on récupère le résultat dans une variable (par exemple dans une fonction récursive)

test=>

CREATE OR REPLACE FUNCTION DoAddPerson(_num integer, _name varchar) RETURNS person AS $$

DECLARE

        result person;

BEGIN

        RAISE NOTICE 'DoAddPerson(%, ''%'')', _num, _name;

        SELECT * INTO result FROM AddPerson(_num, _name);

        -- result := AddPerson(_num, _name); -- syntaxe équivalente à la ligne précédente

        return result;

END;

$$ LANGUAGE plpgsql;

CREATE FUNCTION

test=>

SELECT DoAddPerson(4, 'Dubois');

SELECT * FROM person;

NOTICE:  DoAddPerson(4, 'Dubois')

NOTICE:  AddPerson(4, 'Dubois')

 doaddperson

-------------

 (4,Dubois,)

(1 row)

 

 id |  name  | data

----+--------+------

  2 | Dupond |

  3 | Durand |

  4 | Dubois |

(3 rows)

Conclusion

L'opérateur .* équivaut à invoquer la source autant de fois que celle-ci a de champs. Si la source est une fonction, cette fonction est donc appelée plusieurs fois (s'il y a plusieurs champs ;)

C'est-à-dire que lorsqu'on écrit:

test=>

INSERT INTO person (SELECT  (BuildPerson(2, 'Dupond')).*);

tout se passe comme si on avait écrit:

test=>

INSERT INTO person (SELECT  (BuildPerson(2, 'Dupond')).id,

                            (BuildPerson(2, 'Dupond')).name,

                            (BuildPerson(2, 'Dupond')).data);

NOTICE:  BuildPerson(2, 'Dupond')

NOTICE:  BuildPerson(2, 'Dupond')

NOTICE:  BuildPerson(2, 'Dupond')

INSERT 0 1

N'hésitez pas à me faire part de vos remarques, suggestions, et bien sûr expérimentations autour de cet article :)

— Michelle Baert —