Comment calculer les lignes qui sont dans une table mais pas dans une autre et vice-versa, sans préjuger de la structure des tables, ni du fait qu’il y ait une clef primaire et des colonnes qui la portent éventuellement?

Le principe de base

Les opérateurs ensemblistes EXCEPT et UNION nous amènent une solution assez simple et élégante, car on peut voir les différences entre deux tables T1 et T2 de même structure comme:

 T1 except T2 (les lignes présentes dans T1 et pas dans T2)
   UNION  
 T2 except T1 (les lignes qui sont dans T2 et pas dans T1)

On peut aussi chercher à exprimer ça de manière assez générique, c’est-à-dire que T1 et T2 puissent être des paramètres plutôt que d’écrire une requête différente pour chaque couple de tables (T1,T2) qu’on peut être amené à comparer.

Faire du SQL générique, c’est souvent faire du SQL dynamique à l’aide de plpgsql. Voyons à quoi pourrait ressembler une fonction plpgsql qui nous renverrait les différences entre deux tables quelconques, un peu comme la sortie d’un diff avec des signes ‘+’ et ‘-‘ en tête de ligne pour marquer les insertions et suppressions.

Transférer toute une ligne dans une valeur

Pour pouvoir sortir toute une ligne sous la forme d’un champ, toujours sans préjuger de sa structure, on utilise le fait que PostgreSQL permet la conversion d’un enregistrement du type de la table vers un champ texte avec un simple CAST, soit une expression du style: SELECT table.*::text FROM table...

Le résultat a un format compatible avec un constructeur de ligne ROW(...), c’est-à-dire qui ressemble un peu à une ligne CSV avec des parenthèses autour. En gros:

  • les champs sont séparés par des virgules.
  • des guillemets délimitent les champs dès que nécessaire.
  • les guillemets internes aux champs sont échappés.

Il reste à ajouter un ‘+’ ou ‘-‘ dans un champ à part et à retourner un type TABLE("+/-" text, text) qui représente le signe suivi de la ligne insérée ou supprimée entre une table et l’autre.

Passer en paramètre les tables à la fonction

Pour passer en paramètre nos tables à une fonction plpgsql, on pourrait utiliser leur nom en type text ou name, mais il y a plus intéressant: le type regclass.

C’est un type “alias d’OID” pour les entrées de pg_class, c’est-à-dire que le moteur SQL évalue 'nom_objet'::regclass au moment de l’exécution en cherchant cet objet dans le catalogue, avec un comportement qui gère un tas de détails pour nous:

  • le fait que le nom puisse être qualifié par un schéma ou non, suivant que le search_path en cours inclut ou non ce schéma.

  • la sensibilité à la casse (majuscule/minuscule) du nom suivant qu’il soit entouré ou non de guillemets.

  • une erreur est déclenchée par la conversion de type vers regclass si l’objet n’est pas trouvé dans le catalogue. Donc notre fonction qui prend du regclass en entrée ne démarrera même pas si on lui donne des arguments incorrects, ce qui est sécurisant car on va quand même injecter ces arguments dans une requête SQL.

En bref regclass permet de fournir un nom de table dynamique via un paramètre en lui appliquant la même gestion que si ce nom avait été présent dans une requête statique. En interne ce type représente un OID mais on n’a pas vraiment besoin de le savoir si on ne veut pas fouiller plus que ça (dans le cas contraire \dC regclass sous psql sera utile pour examiner les différentes conversions).

La fonction

Avec ces éléments, le corps de fonction n’a plus qu’à appliquer format() sur le modèle de requête, en lui faisant remplacer les %s par les noms des tables passées en type regclass et exécuter le résultat (EDIT: ces noms sous forme de texte sont prêts à être injectés en tant qu’identifiants, c’est-à-dire déjà mis entre guillemets si nécessaire, c’est pourquoi il ne faut pas utiliser le format %I dans ce contexte).

Avec RETURN QUERY EXECUTE format(...), une seule instruction plpgsql suffit à faire tout:

CREATE FUNCTION diff_tables(table1 regclass, table2 regclass) 
   RETURNS TABLE("+/-" text, ligne text)
AS $func$
BEGIN
  RETURN QUERY EXECUTE format($$
     SELECT '+', d1.*::text FROM (
        SELECT * FROM %s
           EXCEPT
        SELECT * FROM %s
     ) AS d1

     UNION ALL

     SELECT '-', d2.*::text FROM (
        SELECT * FROM %s
           EXCEPT
        SELECT * FROM %s
     ) AS d2
   $$, table2, table1, table1, table2);
END
$func$ language plpgsql;

Exemple d’utilisation

Le format, un peu similaire à la commande diff avec une ligne par différence, est pratique pour comparer des résultats obtenus avec des résultats supposés, par exemple dans des tests de non-régression, quand on s’attend à des déviations nulles ou faibles.

On peut aussi mesurer des différences entre avant et après un traitement, comme une installation. Par exemple si on fait un CREATE EXTENSION, le diff avant/après de pg_extension va donner la chose suivante:

=# create temporary table backup_pg_extension as select * from pg_extension;
SELECT 1

=# create extension ltree;
CREATE EXTENSION

=# select * from diff_tables('backup_pg_extension', 'pg_extension');
 +/- |          ligne          
-----+-------------------------
 +   | (ltree,10,2200,t,1.1,,)
(1 ligne)