Forums PostgreSQL.fr

niuxe

Membre

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

Dernière modification par niuxe (15/08/2024 19:36:03)

rjuju

Administrateur

Re : generated column et function

Bonjour,

        SELECT 
            (p.price + (p.price * t.mount / 100)) INTO total
        FROM 
            products AS p

Cette partie est clairement problematique, il faut utiliser votre parametre "price" a la place.

---

Julien.
https://rjuju.github.io/

niuxe

Membre

Re : generated column et function

bonjour rjuju,

Merci pour votre réponse

Cependant, je retombe sur le même problème.

Si je fais :

        SELECT 
            (price + (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; 

Dans le terminal, j'ai ceci d'un insert:

ERREUR:  la référence à la colonne « price » est ambigüe
LIGNE 2 :             (price + (price * t.mount / 100))               ...

Si je fais cela :

        SELECT 
            ($1 + ($1 * 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; 

Je retombe sur le problème initial. La valeur de l'attribut total est vide lors d'un select.

niuxe

Membre

Re : generated column et function

Je crois savoir pourquoi ça passe pas :

The generation expression can only use immutable functions and cannot use subqueries or reference anything other than the current row in any way

Je pense que je vais devoir 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)

Si une personne a une solution plus intéressante, je suis preneur. Le but étant que je voudrais éviter de supprimer l'attribut total et lors d'un select, faire ce calcul. Je pense que je perds en performance puisque ce calcul est fait sur chaque ligne lors d'un select.

Dernière modification par niuxe (16/08/2024 11:54:01)

rjuju

Administrateur

Re : generated column et function

La partie problematique que je pointais concernais le "p,pric" ET le "FROM product".  Vous ne pouvez pas referencer la table source dans le trigger, cela ne fera a peu pres jamais ce que vous voulez.

---

Julien.
https://rjuju.github.io/

niuxe

Membre

Re : generated column et function

La partie problematique que je pointais concernais le "p,pric" ET le "FROM product".  Vous ne pouvez pas referencer la table source dans le trigger, cela ne fera a peu pres jamais ce que vous voulez.

Je pense même que t.mount doit poser un problème (ce que j'ai écrit provenant de la doc).

Merci beaucoup pour votre intervention et je vous souhaite une belle fin de journée

rjuju

Administrateur

Re : generated column et function

A priori le t.mount ne devrait poser soucis que si la ligne correspondant dans taxes n'existe pas.  Peut-etre qu'une CTE creant la ligne dans taxes et ajoutant un produit pourrait causer probleme, mais vu le contexte j'imagine que taxes devrait assez peu changer.  Vous pouvez toujours lever une erreur ou utiliser INTO STRICT pour vous assurer de toujours avoir une et une seule ligne, ce qui est toujours mieux que d'inserer une ligne problematique.

---

Julien.
https://rjuju.github.io/