db
tri

Il arrive souvent que l’on ait des données dont il faut déterminer un ordre d’apparition, comme les gagnants d’une course, l’ordre des auteurs d’un ouvrage, un top 10, etc. Dans cette première partie, je vais montrer une façon d’automatiser le système de tri pour des données non incluses dans un conteneur. Le tout avec des fonctions stockées en base de données, et juste un champs en table pour déterminer l’ordre de tri.



16 min.

Partager l’article Trier avec PostgreSQL, partie 1 sur les réseaux sociaux

Prérequis

Les codes sources utilisés dans cet article utiliseront exclusivement le SGBD PostgreSQL, version 10.6 minimum.

Les champs de clé primaire seront toujours id.

Le champ servant à stocker l’ordre de tri sera toujours sort.

L’exemple

L’exemple se basera sur une liste de sportif⋅ve⋅s avec leur ordre d’arrivée à une course. Oui j’invente une course mixte car vive l’égalité.

Voici donc la table qui nous servira dans cette première partie.

CREATE TABLE persons (
    id          SERIAL PRIMARY KEY,
    firstname   TEXT NOT NULL  CHECK(firstname ~ '[[:alpha:]]+'),
    lastname    TEXT NOT NULL  CHECK(lastname ~ '[[:alpha:]]+'),
    sort        INTEGER NOT NULL DEFAULT 0
);

Avoir un tri par défaut dès l’insertion

Il est souvent naturel d’entrer dans l’ordre des données. Nous pouvons donc automatiser ça avec un déclencheur pour avoir une incrémentation du champ sort lors de l’ajout d’une donnée en mettant la valeur du champ à MAX(sort) + 1.

Créons la fonction déjà. Faisons-la générique.

CREATE OR REPLACE FUNCTION put_at_last() RETURNS trigger AS $$
DECLARE
    query TEXT;
    found INTEGER;
BEGIN
    IF TG_OP = 'INSERT' THEN
        query := 'SELECT COALESCE(MAX(sort), 0) + 1 FROM ' || quote_ident(TG_TABLE_NAME);
        EXECUTE query INTO found;
        NEW.sort := found;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Son fonctionnement est simple, lors d’une insertion, pour la table concernée, elle regarde la valeur maximale dans le champ sort en veillant, si la table est vide, à fixer cette valeur à zéro via la fonction COALESCE(). À cette valeur max trouvée, elle y ajoute 1, cette nouvelle valeur est ensuite stokée dans la variable found, et cette valeur est ensuite passée au champ sort de la nouvelle entrée.

Nous avons la fonction, nous avons la table, créons le déclencheur !

CREATE TRIGGER put_at_last
    BEFORE INSERT ON persons
    FOR EACH ROW EXECUTE PROCEDURE put_at_last();

Voilà, testons vite fait :

INSERT INTO persons (firstname, lastname)
VALUES      ('Laetitia', 'Launay'),
            ('Suzanne', 'Poissonnier'),
            ('Eugène', 'Payet'),
            ('Juliette', 'Germain'),
            ('Sylvie', 'Lemaire'),
            ('Julien', 'Normand'),
            ('Antoine', 'Pasquier')
            ;

En base nous avons maintenant :

 id | firstname |  lastname   | sort 
----+-----------+-------------+------
  1 | Laetitia  | Launay      |    1
  2 | Suzanne   | Poissonnier |    2
  3 | Eugène    | Payet       |    3
  4 | Juliette  | Germain     |    4
  5 | Sylvie    | Lemaire     |    5
  6 | Julien    | Normand     |    6
  7 | Antoine   | Pasquier    |    7

Ainsi, par défaut, nous avons déjà un classement pré-existant.

La requête permettant de connaitre la position relative de chaque personne

Pour changer le classement d’un élément, nous devons connaître l’élément précédent et l’élément suivant, pour changer leur classement également.

Si un élément monte dans le classement, il prendra la position de l’élément précédent, l’élément précédent prendra la position de l’élément déplacé. L’équivalent se produit dans le sens contraire avec l’élément suivant en descendant.

Il est donc nécessaire de connaître la position relative de chaque élément à déplacer, il faut connaître ce qui le précède et ce qui le devance pour adapter les valeurs aux bons éléments.

Reprenons le contenu de notre table exemple. Pour afficher dans l’ordre du classement les éléments, il suffit de la requête basique suivante :

SELECT * FROM persons ORDER BY sort ASC;

L’idéal, serait que pour chaque ligne, il y ait les informations sur l’élément qui précède et sur celui qui suit. Pour cela nous allons dégainer les fonctions de fenêtrage LAG() et LEAD().

Plaçons notre requête SELECT dans un CTE et utilisons nos fonctions de fenêtrage pour connaître sur chaque ligne l’ID précédent, l’ID suivant, la position de l’élément précédent et le position de l’élément suivant.

Nous obtenons ceci :

WITH cte AS (
    SELECT * FROM persons ORDER BY sort ASC
)
SELECT
    *,
    LAG(id, 1)      OVER (ORDER BY sort ASC) AS prev_item,
    LEAD(id, 1)     OVER (ORDER BY sort ASC) AS next_item,
    LAG(sort, 1)    OVER (ORDER BY sort ASC) AS prev_sort,
    LEAD(sort, 1)   OVER (ORDER BY sort ASC) AS next_sort
FROM cte;

Exécutons ce code :

 id | firstname |  lastname   | sort | prev_item | next_item | prev_sort | next_sort 
----+-----------+-------------+------+-----------+-----------+-----------+-----------
  1 | Laetitia  | Launay      |    1 |           |         2 |           |         2
  2 | Suzanne   | Poissonnier |    2 |         1 |         3 |         1 |         3
  3 | Eugène    | Payet       |    3 |         2 |         4 |         2 |         4
  4 | Juliette  | Germain     |    4 |         3 |         5 |         3 |         5
  5 | Sylvie    | Lemaire     |    5 |         4 |         6 |         4 |         6
  6 | Julien    | Normand     |    6 |         5 |         7 |         5 |         7
  7 | Antoine   | Pasquier    |    7 |         6 |           |         6 |

Nous avons ce que nous souhaitons !

Les champs prev_item et next_item correspondent respectivement aux ID précédent et suivant. De même, les champs prev_sort et next_sort correspondent aux positions des éléments précédent et suivant, respectivement.

Ainsi, pour l’ID 1, qui est classé en première position, les champs prev_item et prev_sort sont à NULL, ce qui est normal car il n’y a rien avant. Suivant la même logique, le dernier élément du classement a les champs next_item et next_sort à NULL.

Tout ceci est trivial pour le moment car le classement correspond à la valeur de la clé primaire vu que c’est l’ordre d’insertion. Dès que les élements vont être triés et retriés, ce ne sera plus le cas, et cette requête nous sera bien utile. Tellement utile qu’il va falloir l’utiliser autrement.

Rendons cette requête générique et réutilisable

Ce qui serait bien, c’est de rendre cette requête générique pour l’utiliser avec toutes les tables présentant la possibilité de trier leur données.

Pour cela il faut créer une fonction prenant le nom de la table en paramètre et en considérant le fait que la table respecte les conventions que nous nous sommes fixés (champs id et sort), générer la requête qui permet d’obtenir la sortie vue précédemment.

Un code possible serait le suivant :

CREATE OR REPLACE FUNCTION prev_next_simple_helper (
    IN arg_table_name TEXT
)
RETURNS TABLE (
    id          INTEGER,
    sort        INTEGER,
    prev_item   INTEGER,
    next_item   INTEGER,
    prev_sort   INTEGER,
    next_sort   INTEGER
) 
AS $$
DECLARE
    query TEXT;
BEGIN
    query := 'WITH cte AS (
        SELECT id, sort  
        FROM ' || quote_ident(arg_table_name) || '
        ORDER BY sort ASC
    )
    SELECT 
        *,
        LAG (id, 1) OVER (ORDER BY sort ASC) AS prev_item,
        LEAD (id, 1) OVER (ORDER BY sort ASC) AS next_item,
        LAG (sort, 1) OVER (ORDER BY sort ASC) AS prev_sort,
        LEAD (sort, 1) OVER (ORDER BY sort ASC) AS next_sort
    FROM cte;
    ';

	RETURN QUERY EXECUTE query;
END; $$ 

LANGUAGE 'plpgsql';

Petit détail, la requête générée ne comporte que les champs essentiels au classement, exit donc les champs firstname et lastname, seuls les champs id, sort et les champs créés via les fonctions de fenêtrage sont conservés explicitement. Vu que nous voulons rendre la fonction générique, nous gardons uniquement ce qu’il nous faut pour pouvoir trier.

Voyons si ça fonctionne toujours aussi bien avec notre table exemple :

SELECT * FROM prev_next_simple_helper('persons');

Donne en sortie :

 id | sort | prev_item | next_item | prev_sort | next_sort 
----+------+-----------+-----------+-----------+-----------
  1 |    1 |           |         2 |           |         2
  2 |    2 |         1 |         3 |         1 |         3
  3 |    3 |         2 |         4 |         2 |         4
  4 |    4 |         3 |         5 |         3 |         5
  5 |    5 |         4 |         6 |         4 |         6
  6 |    6 |         5 |         7 |         5 |         7
  7 |    7 |         6 |           |         6 |

Le contenu est identique à ce que nous avions déjà.

Parfait ! Nous avons maintenant une fonction générique permettant de connaître la position relative de chaque élément d’une table triable respectant la convention des champs id et sort.

Nous allons pouvoir passer au vif du sujet : changer le classement !

Déplacement d’un seul élément

C’est le cas typique où l’interface propose une flèche vers le haut et une flèche vers le bas pour déplacer un élément.

Pour ce cas il faut connaître 3 choses :

  • le nom de la table concernée
  • la valeur de l’ID de l’élément concerné
  • le sens du déplacement (vers le haut, ou vers le bas, croissant, décroissant…)

Utilisons-les dans une fonction. Sa valeur de retour correspondra à la nouvelle position :

CREATE OR REPLACE FUNCTION move_simple(
    IN arg_way              TEXT,
    IN arg_table_name       TEXT,
    IN arg_item_id          INTEGER
)
RETURNS INTEGER AS $$
DECLARE
    v_out       INTEGER;
BEGIN
    -- Code à venir…

    RETURN v_out;
END;
$$ LANGUAGE plpgsql;

Commençons par nous entendre sur les valeurs permises pour l’argument arg_way : up et down me semble appropriées pour notre exemple. Mettons donc un garde-fou en soulevant une erreur si la valeur de arg_way ne correspond à aucune des deux valeurs autorisées :

CREATE OR REPLACE FUNCTION move_simple(
    IN arg_way              TEXT,
    IN arg_table_name       TEXT,
    IN arg_item_id          INTEGER
)
RETURNS INTEGER AS $$
DECLARE
    v_out       INTEGER;
BEGIN
    -- Que 'up' et 'down'
    IF  NOT(arg_way = 'down' OR arg_way = 'up')
    THEN
        RAISE EXCEPTION 'You must select way as `up` or `down` only.';
    END IF;

    -- Code à venir…

    RETURN v_out;
END;
$$ LANGUAGE plpgsql;

Bien. Il faut également connaître les valeurs minimale et maximale des positions possibles pour le classement. Pour cela nous déclarons deux nouvelles variables pour les stocker après les avoir récupérées à l’aide de la fonction prev_next_simple_helper() que nous avions défini juste avant.

Appelons-les v_min et v_max :

CREATE OR REPLACE FUNCTION move_simple(
    IN arg_way              TEXT,
    IN arg_table_name       TEXT,
    IN arg_item_id          INTEGER
)
RETURNS INTEGER AS $$
DECLARE
    v_min       INTEGER;
    v_max       INTEGER;
    v_out       INTEGER;
BEGIN

    IF  NOT(arg_way = 'down' OR arg_way = 'up')
    THEN
        RAISE EXCEPTION 'You must select way as `up` or `down` only.';
    END IF;

    -- On détermine les bornes min et max
    SELECT  MIN(sort), MAX(sort)
    INTO    v_min, v_max
    FROM    prev_next_simple_helper(arg_table_name);

    -- Code à venir…

    RETURN v_out;
END;
$$ LANGUAGE plpgsql;

Nous commençons à avoir des éléments intéressants pour nous aider à trier. Maintenant il faut connaître la position actuelle de l’item déplacé. Nous réutilisons encore prev_next_simple_helper() pour cela et nous stockons la valeur dans la variable v_sort_curr.

CREATE OR REPLACE FUNCTION move_simple(
    IN arg_way              TEXT,
    IN arg_table_name       TEXT,
    IN arg_item_id          INTEGER
)
RETURNS INTEGER AS $$
DECLARE
    v_min       INTEGER;
    v_max       INTEGER;
    v_out       INTEGER;

    v_sort_curr INTEGER;
BEGIN

    IF  NOT(arg_way = 'down' OR arg_way = 'up')
    THEN
        RAISE EXCEPTION 'You must select way as `up` or `down` only.';
    END IF;

    SELECT  MIN(sort), MAX(sort)
    INTO    v_min, v_max
    FROM    prev_next_simple_helper(arg_table_name);

    -- position actuelle dans le classement
    SELECT  sort
    INTO    v_sort_curr
    FROM    prev_next_simple_helper(arg_table_name)
    WHERE   id = arg_item_id;

    -- Code à venir…

    RETURN v_out;
END;
$$ LANGUAGE plpgsql;

Nous connaissons la position actuelle, nous connaissons les bornes… Mettons un autre garde-fou levant une erreur s’il y a tentative de franchissement de ces bornes :

CREATE OR REPLACE FUNCTION move_simple(
    IN arg_way              TEXT,
    IN arg_table_name       TEXT,
    IN arg_item_id          INTEGER
)
RETURNS INTEGER AS $$
DECLARE
    v_min       INTEGER;
    v_max       INTEGER;
    v_out       INTEGER;

    v_sort_curr INTEGER;
BEGIN

    IF  NOT(arg_way = 'down' OR arg_way = 'up')
    THEN
        RAISE EXCEPTION 'You must select way as `up` or `down` only.';
    END IF;

    SELECT  MIN(sort), MAX(sort)
    INTO    v_min, v_max
    FROM    prev_next_simple_helper(arg_table_name);

    SELECT  sort
    INTO    v_sort_curr
    FROM    prev_next_simple_helper(arg_table_name)
    WHERE   id = arg_item_id;

    -- Si on est déjà au plus haut, on ne monte plus pour ne pas
    -- se crâmer les ailes comme Icare
    -- Si on est déjà au plus bas, on ne creuse pas :-)
    IF  (v_min = v_sort_curr AND arg_way = 'up')
        OR
        (v_max = v_sort_curr AND arg_way = 'down')
        THEN
        RAISE EXCEPTION 'You cannot up or down more this item.';
    END IF;

    -- Code à venir…

    RETURN v_out;
END;
$$ LANGUAGE plpgsql;

Mettons maintenant une valeur par defaut pour la variable v_out.

CREATE OR REPLACE FUNCTION move_simple(
    IN arg_way              TEXT,
    IN arg_table_name       TEXT,
    IN arg_item_id          INTEGER
)
RETURNS INTEGER AS $$
DECLARE
    v_min       INTEGER;
    v_max       INTEGER;
    v_out       INTEGER DEFAULT 0;

    v_sort_curr INTEGER;
BEGIN

    IF  NOT(arg_way = 'down' OR arg_way = 'up')
    THEN
        RAISE EXCEPTION 'You must select way as `up` or `down` only.';
    END IF;

    SELECT  MIN(sort), MAX(sort)
    INTO    v_min, v_max
    FROM    prev_next_simple_helper(arg_table_name);

    SELECT  sort
    INTO    v_sort_curr
    FROM    prev_next_simple_helper(arg_table_name)
    WHERE   id = arg_item_id;

    IF  (v_min = v_sort_curr AND arg_way = 'up')
        OR
        (v_max = v_sort_curr AND arg_way = 'down')
        THEN
        RAISE EXCEPTION 'You cannot up or down more this item.';
    END IF;

    -- Code à venir…

    RETURN v_out;
END;
$$ LANGUAGE plpgsql;

À ce stade, notre fonction est exécutable bien que non finalisée. Testons nos garde-fou :

-- Monter d’un cran l’élément ayant l’ID 1 de la table `persons` (sont champs sort est à 1)
SELECT move_simple('up', 'persons', 1);

Donne :

ERROR:  You cannot up or down more this item.
CONTEXT:  PL/pgSQL function move_simple(text,text,integer) line 30 at RAISE

Bien, cette limite fonctionne. Voyons son opposé :

SELECT move_simple('down', 'persons', 7);

Donne :

ERROR:  You cannot up or down more this item.
CONTEXT:  PL/pgSQL function move_simple(text,text,integer) line 30 at RAISE

Ce garde-fou fonctionne bien. Voyons s’il ne bloque pas pour les mouvements autorisés :

SELECT move_simple('up', 'persons', 7);
SELECT move_simple('down', 'persons', 1);
SELECT move_simple('down', 'persons', 3);

Nous n’avons aucune erreur en sortie.

Voyons notre garde-fou pour la direction :

SELECT move_simple('foo', 'persons', 3);

Donne :

ERROR:  You must select way as `up` or `down` only.
CONTEXT:  PL/pgSQL function move_simple(text,text,integer) line 12 at RAISE

Parfait, c’est exactement ce que nous voulions.

Reprenons l’écriture de notre fonction pour y inclure enfin le code permettant de changer notre classement.

Nous avons donc un mouvement possible vers le haut (up), et vers le bas (down).

Pour chaque mouvement, nous devons :

  • connaitre les infos de l’élément concerné,
  • mettre à jour la position de l’élément déplacé,
  • mettre à jour la position de l’élément dont il prend la place.

Nous avons donc, pour chaque mouvement, 3 requêtes : un SELECT et deux UPDATE.

Pour les SELECT de chaque mouvement, nous placerons les informations clés dans les nouvelles variables v_sort_other et v_id_other.

Les requêtes UPDATE devant être construites, nous ajoutons encore deux variables : query_curr et query_other.

CREATE OR REPLACE FUNCTION move_simple(
    IN arg_way              TEXT,
    IN arg_table_name       TEXT,
    IN arg_item_id          INTEGER
)
RETURNS INTEGER AS $$
DECLARE
    v_min       INTEGER;
    v_max       INTEGER;

    -- la variable v_out devient inutile maintenant, elle dégage.

    v_sort_curr INTEGER;

    -- pour le UPDATE de l’élément déplacé
    query_curr  TEXT;
    -- pour le UPDATE de l’élément dont il prend la place
    query_other TEXT;

    -- Infos utiles pour le mouvement UP et DOWN
    v_sort_other INTEGER;
    v_id_other   INTEGER;
BEGIN

    IF  NOT(arg_way = 'down' OR arg_way = 'up')
    THEN
        RAISE EXCEPTION 'You must select way as `up` or `down` only.';
    END IF;

    SELECT  MIN(sort), MAX(sort)
    INTO    v_min, v_max
    FROM    prev_next_simple_helper(arg_table_name);

    SELECT  sort
    INTO    v_sort_curr
    FROM    prev_next_simple_helper(arg_table_name)
    WHERE   id = arg_item_id;

    IF  (v_min = v_sort_curr AND arg_way = 'up')
        OR
        (v_max = v_sort_curr AND arg_way = 'down')
        THEN
        RAISE EXCEPTION 'You cannot up or down more this item.';
    END IF;

    -- Selon le mouvement, on sélectionne tel ou tel champ
    SELECT
        CASE WHEN arg_way = 'up' THEN prev_sort ELSE next_sort END,
        CASE WHEN arg_way = 'up' THEN prev_item ELSE next_item END
    INTO
        v_sort_other,
        v_id_other
    FROM
        prev_next_simple_helper(arg_table_name)
    WHERE
        id = arg_item_id;

    -- L’élément déplacé prend la position de l’élément dont il prend la place
    query_curr := '
        UPDATE  '|| quote_ident(arg_table_name) ||'
        SET     sort = $1
        WHERE   id   = $2;
    ';

    -- l’élément délogé prend la position de l’élément déplacé
    query_other := '
        UPDATE  '|| quote_ident(arg_table_name) ||'
        SET     sort = $1
        WHERE   id   = $2;
    ';

    EXECUTE query_curr  USING v_sort_other, arg_item_id;
    EXECUTE query_other USING v_sort_curr, v_id_other;

    -- la nouvelle position correspond forcément à la position de l’élément cible
    RETURN v_sort_other;
END;
$$ LANGUAGE plpgsql;

Il ne nous reste plus qu’à tester !

-- On déplace Suzanne Poissonnier de la 2e à la 1re place
SELECT move_simple('up', 'persons', 2);

En retour nous avons :

 move_simple 
-------------
           1
(1 row)

Donc la position 1, ce que nous cherchons à obtenir. Est-ce bien le cas dans la table ?

SELECT * FROM persons ORDER BY sort ASC;

Donne en sortie :

 id | firstname |  lastname   | sort 
----+-----------+-------------+------
  2 | Suzanne   | Poissonnier |    1
  1 | Laetitia  | Launay      |    2
  3 | Eugène    | Payet       |    3
  4 | Juliette  | Germain     |    4
  5 | Sylvie    | Lemaire     |    5
  6 | Julien    | Normand     |    6
  7 | Antoine   | Pasquier    |    7
(7 rows)

Remontons Laetitia Launay :

SELECT move_simple('up', 'persons', 1);

Nous avons alors :

 id | firstname |  lastname   | sort 
----+-----------+-------------+------
  1 | Laetitia  | Launay      |    1
  2 | Suzanne   | Poissonnier |    2
  3 | Eugène    | Payet       |    3
  4 | Juliette  | Germain     |    4
  5 | Sylvie    | Lemaire     |    5
  6 | Julien    | Normand     |    6
  7 | Antoine   | Pasquier    |    7
(7 rows)

Descendons Eugène Payet, ayant l’ID 3 :

SELECT move_simple('down', 'persons', 3);

Ce qui donne :

 id | firstname |  lastname   | sort 
----+-----------+-------------+------
  1 | Laetitia  | Launay      |    1
  2 | Suzanne   | Poissonnier |    2
  4 | Juliette  | Germain     |    3
  3 | Eugène    | Payet       |    4
  5 | Sylvie    | Lemaire     |    5
  6 | Julien    | Normand     |    6
  7 | Antoine   | Pasquier    |    7
(7 rows)

Cela semble bien fonctionner n’est-ce pas ? Le classement au coup par coup est fini !

Bien, reste à voir pour le classement en fournissant tous les éléments…

Déplacement de tous les éléments

La réorganisation du classement de tous les éléments se fait plus facilement qu’avec un seul élément à déplacer au coup par coup.

La fonction que nous allons concevoir aura besoin du nom de la table concernée, ainsi que de la liste des ID dans l’ordre voulu. Cette liste sera ici sous la forme d’une chaîne de caractères contenant les ID concaténés avec des virgules. Cette manière de faire est très pratique par exemple avec des scripts Javascript retounant la liste des ID dans un nouvel ordre après des glisser-déposer à la souris. Vous êtes libre d’adapter avec par exemple un JSON ou autre.

Par ailleurs cette fonction ne retournera rien.

Décortiquons un peu cette fonction par étape. Commençons par la signature :

CREATE OR REPLACE FUNCTION sort_simple(
    IN arg_table_name       TEXT,
    IN arg_item_ids         TEXT
)
RETURNS VOID AS $$
DECLARE
    ids INTEGER[];
    i   INTEGER;
    id  INTEGER;
    q   TEXT;
BEGIN
    -- Code à venir…
END;
$$ LANGUAGE plpgsql;

Les deux arguments de la fonction parlent d’eux-même.

Les variables déclarées au sein de la fonction sont les suivantes :

  • ids va contenir les ID provenant de arg_item_ids sous la forme d’un tableau d’entiers
  • i va servir à remplir le champ sort de la table visée de façon incrémentale,
  • id sera la valeur de l’ID d’un item dans la boucle,
  • q sera la chaîne de caractères contenant la requête s’exécutant à chaque tour de la boucle FOREACH pour mettre à jour le classement.

Commençons par récupérer les IDs sous la forme d’un tableau d’entiers, en indiquant que la chaîne de caractères doit être scindée selon le caractère séparateur ,, et en convertissant dans la foulée ses éléments en entiers.

CREATE OR REPLACE FUNCTION sort_simple(
    IN arg_table_name       TEXT,
    IN arg_item_ids         TEXT
)
RETURNS VOID AS $$
DECLARE
    ids INTEGER[];
    i   INTEGER;
    id  INTEGER;
    q   TEXT;
BEGIN
    -- Convertissons en un tableau de nombres entiers
    ids := string_to_array(arg_item_ids, ',')::integer[];

    -- Code à venir…
END;
$$ LANGUAGE plpgsql;

Initialisons l’incrémentation et déclarons la requête de mise à jour.

CREATE OR REPLACE FUNCTION sort_simple(
    IN arg_table_name       TEXT,
    IN arg_item_ids         TEXT
)
RETURNS VOID AS $$
DECLARE
    ids INTEGER[];
    i   INTEGER;
    id  INTEGER;
    q   TEXT;
BEGIN
    ids := string_to_array(arg_item_ids, ',')::integer[];

    -- Initialisation de l’incrémentation, zéro par défaut
    i   := 0;

    -- La requête d’UPDATE
    q   := 'UPDATE ' || quote_ident(arg_table_name) || ' SET sort=$1 WHERE id=$2';

    -- Code à venir…
END;
$$ LANGUAGE plpgsql;

Il ne reste plus qu’à créer la boucle sur le tableau d’IDs, incrémenter à chaque tour, et le tour est joué :

CREATE OR REPLACE FUNCTION sort_simple(
    IN arg_table_name       TEXT,
    IN arg_item_ids         TEXT
)
RETURNS VOID AS $$
DECLARE
    ids INTEGER[];
    i   INTEGER;
    id  INTEGER;
    q   TEXT;
BEGIN
    ids := string_to_array(arg_item_ids, ',')::integer[];
    i   := 0;
    q   := 'UPDATE ' || quote_ident(arg_table_name) || ' SET sort=$1 WHERE id=$2';

    -- On boucle sur les IDS…
    FOREACH id IN ARRAY ids
    LOOP
        -- On incrémente la position
        i := i + 1;
        -- On met à jour la position pour l’ID donné
        EXECUTE q USING i, id;
    END LOOP;
    
END;
$$ LANGUAGE plpgsql;

Et si nous testions cela maintenant ? On va reprendre notre table telle qu’elle était au début.

SELECT sort_simple('persons', '7,1,6,2,5,3,4');

Regardons le contenu de la table :

 id | firstname |  lastname   | sort 
----+-----------+-------------+------
  7 | Antoine   | Pasquier    |    1
  1 | Laetitia  | Launay      |    2
  6 | Julien    | Normand     |    3
  2 | Suzanne   | Poissonnier |    4
  5 | Sylvie    | Lemaire     |    5
  3 | Eugène    | Payet       |    6
  4 | Juliette  | Germain     |    7
(7 rows)

Ça fonctionne parfaitement ! Parfaitement ? Et si les IDs fournis ne correspondent pas au nombre d’éléments de la table, il se passe quoi ? De même si le nombre est bon, mais qu’il y a des doublons ?

Il nous faut donc ajouter quelques sécurités supplémentaires :

  • tester si le nombre d’ID correspond au nombre d’éléments dans la table,
  • tester s’il y a des doublons.

Si un des tests échoue, la fonction devra lever une erreur et ne rien faire d’autre.

Commençons par ajouter la sécurité sur le nombre d’éléments. Nous allons récupérer le nombre d’éléments de la table via un SELECT et le comparer avec le nombre d’éléments dans le tableau des IDs.

CREATE OR REPLACE FUNCTION sort_simple(
    IN arg_table_name       TEXT,
    IN arg_item_ids         TEXT
)
RETURNS VOID AS $$
DECLARE
    ids INTEGER[];
    i   INTEGER;
    id  INTEGER;
    nt  INTEGER;
    qn  TEXT;
    q   TEXT;
BEGIN
    ids := string_to_array(arg_item_ids, ',')::integer[];
    i   := 0;
    -- requête pour connaître le nombre d’éléments dans la table
    qn  := 'SELECT COUNT(*) FROM ' || quote_ident(arg_table_name);
    q   := 'UPDATE ' || quote_ident(arg_table_name) || ' SET sort=$1 WHERE id=$2';

    EXECUTE qn INTO nt;

    IF  nt <> cardinality(ids)
    THEN
        RAISE EXCEPTION 'IDS amount must be the same as amount of elements in the table.';
    END IF;

    FOREACH id IN ARRAY ids
    LOOP
        i := i + 1;
        EXECUTE q USING i, id;
    END LOOP;
    
END;
$$ LANGUAGE plpgsql;

Testons avec un nombre d’IDs incorrect :

SELECT sort_simple('persons', '1,7');

La sortie contient alors :

ERROR:  IDS amount must be the same as amount of elements in the table.
CONTEXT:  PL/pgSQL function sort_simple(text,text) line 20 at RAISE

Et maintenant avec un nombre correct d’IDs :

SELECT sort_simple('persons', '7,6,5,4,3,2,1');

La sortie ne présente alors pas d’erreur. Voyons le contenu de la table :

 id | firstname |  lastname   | sort 
----+-----------+-------------+------
  7 | Antoine   | Pasquier    |    1
  6 | Julien    | Normand     |    2
  5 | Sylvie    | Lemaire     |    3
  4 | Juliette  | Germain     |    4
  3 | Eugène    | Payet       |    5
  2 | Suzanne   | Poissonnier |    6
  1 | Laetitia  | Launay      |    7
(7 rows)

Parfait. Occupons-nous maintenant de tester la présence de doublons.

Pour cela, nous allons utiliser la fonction UNNEST() sur le tableau des IDs et regrouper les IDs identiques et les compter. Si le compte est différent de zéro, c’est que nous avons des doublons. 

CREATE OR REPLACE FUNCTION sort_simple(
    IN arg_table_name       TEXT,
    IN arg_item_ids         TEXT
)
RETURNS VOID AS $$
DECLARE
    ids INTEGER[];
    i   INTEGER;
    id  INTEGER;
    nt  INTEGER;
    d   BOOLEAN;
    qn  TEXT;
    q   TEXT;
BEGIN
    ids := string_to_array(arg_item_ids, ',')::integer[];
    i   := 0;
    qn  := 'SELECT COUNT(*) FROM ' || quote_ident(arg_table_name);
    q   := 'UPDATE ' || quote_ident(arg_table_name) || ' SET sort=$1 WHERE id=$2';

    EXECUTE qn INTO nt;

    IF  nt <> cardinality(ids)
    THEN
        RAISE EXCEPTION 'IDS amount must be the same as amount of elements in the table.';
    END IF;

    -- Vérifions s’il y a des doublons avec un petit CTE des familles
    WITH dq AS (
        SELECT      item
        FROM        unnest(ids) AS item
        GROUP BY    item
        HAVING      count(*) > 1
    )
    SELECT  COUNT(*) > 0
    INTO    d
    FROM    dq;

    -- Alors docteur, doublon ou pas doublon ?
    IF  d
    THEN
        RAISE EXCEPTION 'IDS must be unique!';
    END IF;

    FOREACH id IN ARRAY ids
    LOOP
        i := i + 1;
        EXECUTE q USING i, id;
    END LOOP;
    
END;
$$ LANGUAGE plpgsql;

Testons notre nouveau garde-fou :

SELECT sort_simple('persons', '1,2,2,4,5,6,7');

Et la bonne erreur est levée :

ERROR:  IDS must be unique!
CONTEXT:  PL/pgSQL function sort_simple(text,text) line 37 at RAISE

Testons avec une liste sans doublons :

SELECT sort_simple('persons', '1,2,3,4,5,6,7');

Pas d’erreur… Voyons si ça fonctionne toujours en regardant le contenu de la table :

 id | firstname |  lastname   | sort 
----+-----------+-------------+------
  1 | Laetitia  | Launay      |    1
  2 | Suzanne   | Poissonnier |    2
  3 | Eugène    | Payet       |    3
  4 | Juliette  | Germain     |    4
  5 | Sylvie    | Lemaire     |    5
  6 | Julien    | Normand     |    6
  7 | Antoine   | Pasquier    |    7
(7 rows)

N’est-ce pas génial non ?

Conclusion

Vous avez maintenant ce qu’il faut pour trier le contenu d’une table. Dans la deuxième partie de cette série d’articles, je vous montrerai comment faire la même chose pour des contenus de table dépendant d’un conteneur. Par exemple, des élements appartenant à un groupe, comment trier ces élements de façon générique au sein du groupe.

Pour résumer, quelques exemples commentés : 

-- Remonte d’un cran dans le classement l’élément de la table top10 ayant l’ID 3
SELECT move_simple('up', 'top10', 3);

-- Abaisse de deux crans dans le classement l’élément de la table top10 ayant l’ID 1
SELECT move_simple('down', 'top10', 1);
SELECT move_simple('down', 'top10', 1);

-- Réordonne l’ensemble des éléments de la table `top10` en fournissant les ID
-- dans l’ordre désiré.
-- On a donc :
-- ID 1 en première position,
-- ID 5 en deuxième position,
-- ID 10 en troisième position, etc
SELECT sort_simple('top10', '1,5,10,3,4,7,8,9,6,2');

Voilà, amusez-vous avec ces codes, adaptez-les, améliorez-les !

Mise à jour : la deuxième partie est maintenant disponible : Trier avec PostgreSQL, partie 2.

Photo de Karim Ghantous sur Unsplash