PostgreSQL : generated column et function

Bonjour tout le monde,


J'ai un petit souci de conception. Voici le code :

CREATE OR REPLACE FUNCTION fetch_taxes_mount(price NUMERIC(5, 2), taxe_id INTEGER)
    RETURNS NUMERIC(5, 2) LANGUAGE PLPGSQL AS $$
    DECLARE total NUMERIC(5,2); 
    BEGIN 
        SELECT 
            (p.price + (p.price * t.mount / 100)) INTO total
        FROM 
            products AS p
        INNER JOIN 
            taxes AS t
        ON 
            p.taxe_id = t.id
        WHERE 
            p.taxe_id = $2;
        RETURN total; 
    END;
    $$ IMMUTABLE;


CREATE TABLE IF NOT EXISTS taxes(
    id SERIAL PRIMARY KEY,
    name VARCHAR(16),
    mount NUMERIC(4,2)
);



CREATE TABLE IF NOT EXISTS products(
    id SERIAL PRIMARY KEY,
    price NUMERIC(5,2),
    taxe_id INTEGER,
    total NUMERIC(5,2) GENERATED ALWAYS AS ( fetch_taxes_mount(products.price, products.taxe_id) ) STORED,
    CONSTRAINT products_taxes_id FOREIGN KEY(taxe_id) REFERENCES taxes(id) ON DELETE CASCADE ON UPDATE CASCADE 
);

insertion en base :

INSERT INTO taxes(name, mount) VALUES('TVA_5', 5.50);
INSERT INTO taxes(name, mount) VALUES('TVA_20', 20.00);
INSERT INTO products(price, taxe_id) VALUES(
    50,
    2
);

Lorsque je fais un select * from products, la colonne total est vide.
J'ai testé séparément la requete dans la fonction et j'obtiens un résultat probant:

        SELECT 
            (p.price + (p.price * t.mount / 100)) AS total
        FROM 
            products AS p
        INNER JOIN 
            taxes AS t
        ON 
            p.taxe_id = t.id
        WHERE 
            p.taxe_id = 2;

Aussi, je n'ai pas de message erreur.

Savez vous d'où ça pourrait provenir ? Merci de vos lumières.

Bonne soirée à vous
Modifié par niuxe (15 Aug 2024 - 19:36)

Merci Olivier pour ta réponse.

Là où je veux en venir, c'est utiliser un attribut d'une table en tant que Generated Columns

Pourquoi ? Ça permet de bénéficier des performances optimales lors d'un select.
Reprenons l'exemple de la table products mais d'une manière plus simple afin que tu comprennes mieux :

CREATE TABLE IF NOT EXISTS products(
    id SERIAL PRIMARY KEY,
    price NUMERIC(5,2),
    taxe NUMERIC(4,2),
    total NUMERIC(5,2) GENERATED ALWAYS AS (price + (price * taxe / 100)) STORED
);

INSERT INTO products(price, taxe) VALUES(
    50,
    5.5
);

En sortie en faisant un simple select :


Comme tu peux le constater, le calcul s'est fait lors de l'insertion en base. Si on fait un UPDATE, ce même calcul se fera automatiquement.

Maintenant, tu vas me dire : Pourquoi tu ne fais pas comme ça ?
L'attribut taxe n'est pas une relation intrinsèque de la table products. Si demain, j'ai une table services par exemple, paye tes doublons de données et donc l'intégrité des données sera mauvaise.

Je pourrais faire ça :

CREATE TABLE IF NOT EXISTS products(
    id SERIAL PRIMARY KEY,
    price NUMERIC(5,2),
    taxe NUMERIC(4,2)
);

INSERT INTO products(price, taxe) VALUES(
    50,
    5.5
);

SELECT
    price,
    (price + (price * taxe / 100)) AS total
FROM 
    products;

Le souci étant qu'à chaque ligne, il calcule.

J'ai même pensé à faire ceci :

CREATE TABLE IF NOT EXISTS taxes(
    id SERIAL PRIMARY KEY,
    name VARCHAR(16),
    mount NUMERIC(4,2)
);

CREATE TABLE IF NOT EXISTS products(
    id SERIAL PRIMARY KEY,
    price NUMERIC(5,2),
    taxe NUMERIC(4,2),
    total NUMERIC(5,2) GENERATED ALWAYS AS (price + (price * taxe / 100)) STORED
);

INSERT INTO taxes(name, mount) VALUES('TVA_5', 5.50);
INSERT INTO taxes(name, mount) VALUES('TVA_20', 20.00);
INSERT INTO products(price, taxe) VALUES(
    50,
    (SELECT mount FROM taxes WHERE name = 'TVA_5')
);

SELECT * FROM products;

Le problème étant que la valeur taxe dans products sera dupliquée (encore un souci d'intégrité de données)

ps: si tu fais des tests en local, pense à faire un drop à chaque fois

DROP FUNCTION fetch_taxes_mount;
DROP TABLE taxes CASCADE;
DROP TABLE products;

Modifié par niuxe (16 Aug 2024 - 11:07)

[HS]
Je l'ai (spam) vu Olivier et je l'ai shooté Merci pour ton intervention
[/HS]
Modifié par niuxe (16 Aug 2024 - 13:42)

Olivier C a écrit :
A oui effectivement je n'avais pas compris, je n'avais pas saisis l'enjeu des colonnes générées. Tu cherches à faire un peu ce qu'une "vue" est aux tables en Postgres.

Pas vraiment. Le but est de faire un calcul lors de l'insertion ou de l'update. Ça évite de faire un calcul de chaque ligne lors d'un select. Pourquoi ? optimisation des perfs ! Je pense que la dernière solution reste la meilleur. Elle n'est pas adéquate puisque j'ai une redondance. Je vais troquer la perf pour une violation d'intégrité (mais contrôlée).

Salut,

il n'y a pas trop de contexte à ton problème mais je suis un peu d'accord avec Olivier, pour être un poil plus précis je trouve que cela ressemble à une "vue matérialisée".
Il y a un coup "élevé" à la création de la vue matérialisée mais ensuite à l'utilisation cela sera aussi optimisé qu'un select normal sur une simple table.
On peut par exemple estimer que le prix des produits et des taxes ne dois pas être modifié à la volée le jour même, ce qui rend acceptable le delta de différences entre la vue matérialisée et les vrais données modifiées, et il suffit de reconstruire la vue matérialisée pendant la nuit par exemple.

Bon j'ai pas testé mais je suppose que ça doit etre un truc du genre :

CREATE TABLE IF NOT EXISTS taxes(
    id SERIAL PRIMARY KEY,
    name VARCHAR(16),
    mount NUMERIC(4,2)
);

CREATE TABLE IF NOT EXISTS products(
    id SERIAL PRIMARY KEY,
    price NUMERIC(5,2),
    taxe_id int FOREIGN KEY REFERENCES taxes (id),
);

CREATE MATERIALIZED VIEW products_taxes AS
  SELECT
      p.id,
      p.price,
      t.mount,
      p.price + (p.price * t.mount / 100) as total
    FROM products p , taxes t
    WHERE p.taxe_id = t.id

Sinon pour ta solution, je dirais qu'il doit être plus propre de stocker aussi l'id de la taxe pour pouvoir faire un triggrer qui va mettre à jour la valeur de la taxe dans la table produit si elle change dans la table taxe.
Modifié par Mathieuu (20 Aug 2024 - 15:03)