Amélioration de la clause RETURNING avec PostgreSQL 18

Publié le 22/01/2025 par Guillaume Lelarge

Reviers, le 22 janvier 2025

Ces derniers jours, pas mal de fonctionnalités intéressantes ont été ajoutées dans la future version 18. Certaines ont déjà fait l’objet d’un article, comme par exemple la possibilité d’augmenter le nombre d’autovacuum workers sans avoir à redémarrer PostgreSQL (lire PostgreSQL 18: Change the maximum number of autovacuum workers on the fly).

Guillaume Lelarge

Donc, je vais parler d’une fonctionnalité qui n’a pas encore été bloguée (à ma connaissance) et que je trouve très intéressante.

Il n’est pas assez connu que PostgreSQL propose, depuis longtemps, une clause RETURNING qui permet de récupérer des informations sur les lignes insérées, modifiées ou supprimées. Cela permet par exemple de récupérer immédiatement l’identifiant d’une ligne insérée. En voici un exemple simple sur un INSERT :

DROP TABLE IF EXISTS factures;

CREATE TABLE factures (
  id          integer GENERATED ALWAYS AS IDENTITY,
  titre       text    NOT NULL,
  montant_ht  numeric(10,2) NOT NULL,
  tva         numeric(5,2) NOT NULL DEFAULT 20.6 CHECK (tva BETWEEN 0 AND 100),
  montant_ttc numeric(10,2) GENERATED ALWAYS AS (montant_ht*(1+tva/100)) STORED,
  PRIMARY KEY(id)
  );

INSERT INTO factures (titre, montant_ht) VALUES ('facture 1', 100);

INSERT INTO factures (titre, montant_ht) VALUES ('facture 2', 200)
  RETURNING id, montant_ttc;

 id | montant_ttc
----+-------------
  2 |      241.20
(1 row)

Dans le cas d’une mise à jour, nous pouvons récupérer les valeurs mises à jour :

UPDATE factures SET montant_ht=300 WHERE id=2
  RETURNING montant_ttc;

 montant_ttc
-------------
      361.80
(1 row)

Sachant qu’on peut aussi utiliser ce résultat dans une CTE, cette fonctionnalité est vraiment incroyable. Mais elle s’améliore encore en version 18. Cette version permet de récupérer non seulement la nouvelle ligne, mais aussi l’ancienne ligne dans des pseudo-lignes NEW et OLD. Par exemple :

UPDATE factures SET montant_ht=400 WHERE id=2
  RETURNING id,
            old.montant_ttc AS ancien_montant_ttc,
            new.montant_ttc AS nouveau_montant_ttc,
            new.montant_ttc-old.montant_ttc AS diff_ttc;

 id | ancien_montant_ttc | nouveau_montant_ttc | diff_ttc
----+--------------------+---------------------+----------
  2 |             361.80 |              482.40 |   120.60
(1 row)

Autre exemple effectuant une CTE pour tracer les modifications avec un autre calcul au passage :

CREATE TABLE  factures_trace (
  id              integer,
  old_montant_ttc numeric(10,2),
  new_montant_ttc numeric(10,2),
  variation_pc    numeric(5,1),
  upd_ts          timestamptz DEFAULT now());

WITH
  maj AS (
    UPDATE factures SET montant_ht=440 WHERE id=2
    RETURNING id,
              old.montant_ttc AS ancien_montant_ttc,
              new.montant_ttc AS nouveau_montant_ttc,
              new.montant_ttc-old.montant_ttc AS diff_ttc,
              100.0*(new.montant_ttc-old.montant_ttc)/old.montant_ttc AS variation_pc
  )
INSERT INTO factures_trace
SELECT id, ancien_montant_ttc, nouveau_montant_ttc, variation_pc
FROM maj
RETURNING *;

 id | old_montant_ttc | new_montant_ttc | variation_pc |            upd_ts
----+-----------------+-----------------+--------------+-------------------------------
  2 |          482.40 |          530.64 |         10.0 | 2025-01-20 14:27:54.302613+01
(1 row)

Je ne serais pas étonné de voir une utilisation forte de cette nouvelle possibilité de la clause RETURNING. Peut-être permettra-t-elle d’alléger du code destiné à tracer et suivre les modifications, et d’éliminer quelques triggers.

DALIBO

DALIBO est le spécialiste français de PostgreSQL®. Nous proposons du support, de la formation et du conseil depuis 2005.