Écrire et utiliser des fonctions retournant une valeur de type composite (ROWTYPE)
Par RockyRoad le mercredi 27 août 2008, 19:42 - Articles - Lien permanent
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=>L'interpréteur psql nous répond:
DROP TYPE IF EXISTS names CASCADE;
CREATE TYPE names AS (
first_name varchar,
last_name varchar,
age integer
);
CREATE TYPELa 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=>On reçoit le message, puis le résultat en une seule colonne
SELECT BuildName('Pierre', 'Dupond');
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=>On reçoit le message, puis le résultat en une seule colonne
SELECT BuildPerson(1, 'Dupond');
NOTICE: BuildPerson(1, 'Dupond')Si on veut insérer les résultats dans la table, il faut adapter la syntaxe pour obtenir des champs distincts
buildperson
-------------
(1,Dupond,)
(1 row)
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')On reçoit aussi 3 FOIS le message , mais la rangée n'est insérée qu'une fois
NOTICE: BuildPerson(2, 'Dupond')
NOTICE: BuildPerson(2, 'Dupond')
INSERT 0 1
test=>
SELECT * FROM person;
id | name | dataTout va bien ? On avance ... mais:
----+--------+------
2 | Dupond |
(1 row)
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')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.
NOTICE: AddPerson(3, 'Durand')
ERROR: duplicate key value violates unique constraint "person_pkey"
Remarquez que, la transaction ayant échoué, aucune ligne n'est finalement ajoutée.
test=>
SELECT * FROM person;
id | name | dataPas de souci cependant si on évite la syntaxe ().* lors de l'invocation:
----+--------+------
2 | Dupond |
(1 row)
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=>tout se passe comme si on avait écrit:
INSERT INTO person (SELECT (BuildPerson(2, 'Dupond')).*);
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 —

