PostgreSQL : generated column et function

Bonsoir,

Si je comprends bien, tu aimerais que ta fonction fetch_taxes_mount() se déclenche à l'insertion de nouvelle valeurs dans les tables concernées afin de remplir le champ "total" et ce n'est pas le cas.

Je n'ais pas assez de bouteille en PSQL pour passer derrière le code des autres et en plus j'utilise toujours les même patterns. Ton approche à toi me semblait correcte, alors je ne sais pas...

De mon côté, pour ce genre de chose je passe par des triggers. Quelque chose comme ceci :

CREATE OR REPLACE FUNCTION fetch_taxes_mount() 
RETURNS TRIGGER AS $$
BEGIN
   -- du code...
END;
$$ LANGUAGE plpgsql;

Après il faut encore le déclarer ce trigger pour chaque table visée avec un déclencheur.

Plutôt que je m'essayer à corriger maladroitement ta fonction je te donne une des miennes ici qui fonctionne en l'état. Voici une table d'articles qui comporte un champ "_modified_at" que nous allons incrémenter automatiquement, mais comme tu peux le voir rien n'est fait au niveau de la table :

-- Table simplifiée pour la démonstration :
CREATE TABLE __article (
  _id                   INT               GENERATED BY DEFAULT AS IDENTITY,
  _name                 VARCHAR(255)      NOT NULL,                         -- titre
  _alternative_headline VARCHAR(255)      NULL,                             -- titre secondaire
  _slug                 VARCHAR(255)      UNIQUE NOT NULL,                  -- slug basé sur le titre
  _content              TEXT              NULL,                             -- contenu
  _created_at           TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,        -- date de création
  _modified_at          TIMESTAMP         NULL,                             -- date de révision
  PRIMARY KEY (_id)
);

Tout se passe ici :

-- Mettre à jour _modified_at avec le timestamp actuel
CREATE FUNCTION update_modified_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW._modified_at = CURRENT_TIMESTAMP; -- TRUNC(CURRENT_TIMESTAMP, 'microseconds');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Créer un déclencheur pour chaque table nécessitant la mise à jour de _modified_at
DO $$
DECLARE
  tables TEXT[] := ARRAY['__account', '__article', '__media', '__comment'];
BEGIN
  FOR i IN 1..array_length(tables, 1) LOOP
    EXECUTE format('CREATE TRIGGER update_modified_at_trigger_%I
                    BEFORE UPDATE ON %I
                    FOR EACH ROW
                    EXECUTE FUNCTION update_modified_at()', tables[i], tables[i]);
  END LOOP;
END $$;

Modifié par Olivier C (15 Aug 2024 - 23:09)

Aller je tente : pour toi cela pourrait ressembler à quelque chose comme ceci :

CREATE TABLE IF NOT EXISTS products(
    id SERIAL PRIMARY KEY,
    price NUMERIC(5,2),
    taxe_id INTEGER,
    result NUMERIC(5,2), -- on ne définit rien au niveau de la colonne...
    CONSTRAINT products_taxes_id FOREIGN KEY(taxe_id) REFERENCES taxes(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- ta fonction avec une modif' :
CREATE OR REPLACE FUNCTION update_result()
RETURNS TRIGGER AS $$
BEGIN
    -- ton calcul...
END;
$$ LANGUAGE plpgsql;

-- un déclencheur pour ta fonction :
CREATE TRIGGER update_result_trigger
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_result();

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)

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.