Récupérer l'ID d'un enregistrement inséré sous Postgres

En programmation, c'est une bonne stratégie de connaître des alternatives pour écrire du code qui fait le même travail. On se rend compte avec le temps qu'il n'y a pas de mauvaise façon de faire, juste des meilleures.

Quand j'ai écrit mes premières lignes de code pour des fonctions Postgres et que j'avais à insérer un enregistrement pour récupérer et retourner la valeur de l'identificateur unique (par sa séquence / clé primaire), j'ai pris l'habitude d'y aller en deux étapes : l'insertion d'abord, la récupération ensuite. Le tout étant encapsulé dans une fonction suivant les règles de transaction, ça fonctionnait bien.

CREATE OR REPLACE FUNCTION insert_something(fname character varying, lname character varying)

RETURNS integer AS

$BODY$

BEGIN

   INSERT INTO artist (firstname, lastname)

   VALUES (fname, lname);



   RETURN currval('public.artist_id_artist_seq');

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

Avec ce code, il est impératif de connaître le nom de la séquence pour obtenir la valeur courante de l'insertion qui la précède. Et comme on utilise généralement le type SERIAL pour faciliter la création d'une clé primaire qui génère du même coup la séquence avec un nom générique, il faudra d'abord trouver ce nom.

L'énoncé pour obtenir le nom d'une séquence appliquée à une colonne d'une table :

SELECT pg_get_serial_sequence('artist', 'id_artist')

-- "public.t_artist_id_artist_seq"

Et on demande sa valeur par currval :

SELECT currval('public.artist_id_artist_seq') as id;



-- ou en l'assignant à une variable pl/pgsql



id := currval('public.artist_id_artist_seq');

La requête retournera l'ID généré par l'appel de la fonction :

SELECT insert_something('Jimi', 'Hendrix') // 4

Pour faire exactement la même chose, on pourrait réécrire le code en utilisant une particularité de Postgres pour demander à la requête SQL de retourner la valeur d'un champ dans le même énoncé en utilisant le mot clé RETURNING qui l'assignera à une variable déclarée.

CREATE OR REPLACE FUNCTION insert_something(fname character varying, lname character varying)

   RETURNS integer AS

$BODY$

DECLARE

   id integer; -- déclaration de la valeur de retour

BEGIN

   INSERT INTO t_test (firstname, lastname)

   VALUES (fname, lname)

   RETURNING "id_record" INTO STRICT id;



   RETURN id;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

En fait, vous pouvez retourner n'importe quelle valeur de l'enregistrement venant d'être créé, que ce soit la valeur de la séquence ou la valeur par défaut définie dans un champ de la table (par exemple une date de création avec comme valeur par défaut "now()"). Cependant, si la valeur à retourner est insérée par un trigger de type AFTER INSERT, sa valeur sera nulle car Postgres fait appel au trigger uniquement après l'insertion (BEFORE INSERT ne présente pas de problème).

L'énoncé qui utilise RETURNING a l'avantage qu'on n'a pas à connaître le nom de la séquence et que ça fonctionnera quand même si son nom change dans le futur (tant que le nom du champ demandé reste inchangé!).