Trigger PostgreSQL to update one value based on the sum of three others

Question

I am trying to make a boolean become true when the sum of three integers is equal to 100 and false when the sum is not equal to 100.

I am making a trigger function in Postgres to do that, but getting the following error:

ERROR: «db_fondos.checksize» is not a known variable
LINE 6: DB_Fondos.CheckSize=true;

I hope you could help me. Code:

CREATE FUNCTION check_sum()
    RETURNS TRIGGER
AS $$
BEGIN
    IF DB_Fondos.SizeLarge+DB_Fondos.SizeMid+DB_Fondos.SizeSmall=100 then
    DB_Fondos.CheckSize=true;
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

I guess `DB_Fondos` is table name? If it true, in trigger function you should new `NEW.` instead — Slava Rozhnev, Mar 21 '20 at 21:56

GMB · Answer 1 · 2020-03-21T22:01:02.723

3

Why not simply use a computed column for this instead of a trigger? The database manages the computation for you under the hood and you get an always up-to-date value when you query the table:

create table DB_Fondos (
    ...
    SizeLarge int,
    SizeMid int,
    SizeSmall int,
    check_sum boolean generated always as (
        coalesce(SizeLarge, 0) 
        + coalesce(SizeMid, 0) 
        + coalesce(SizeSmall, 0) = 100
    )
);

edited Mar 21 '20 at 22:01

answered Mar 21 '20 at 21:57

GMB

216,147
25
84
135

Nitpicking: COALESCE(col, 0) - columns could be nullable – Lukasz Szozda Mar 21 '20 at 21:59
1

The generated fields introduced since PostgreSQL 12. https://pgdash.io/blog/postgres-12-generated-columns.html Before it trigger solution can be used – Slava Rozhnev Mar 21 '20 at 22:06
I have got a error when submiting the following code: "PeriodoInvAños" integer NOT NULL DEFAULT 5, "PlazoFondoAños" integer NOT NULL DEFAULT 10, "MaxExtensionesGPAños" integer NOT NULL DEFAULT 2, "ExtensionesAprobMes" numeric NOT NULL, "SizeLarge" integer, "SizeMid" integer, "SizeSmall" integer, "CheckSize" boolean generated always as (coalesce(SizeLarge,0)+coalesce(SizeMid,0)+coalesce(SizeSmall,0)=100), CONSTRAINT "DB_Fondos_pkey" PRIMARY KEY ("Id_Fondo"), CONSTRAINT "Fondo" UNIQUE ("Fondo"), CONSTRAINT "Fondo2" UNIQUE ("Emisor") ); – Jonathan Prieto Mar 21 '20 at 22:30

score 1 · Answer 2 · edited Mar 21 '20 at 22:22

1

In a trigger, you relate to the newly inserted line with NEW, not with the table name:

CREATE FUNCTION check_sum()
    RETURNS TRIGGER
AS $$
BEGIN
    IF NEW.SizeLarge + NEW.SizeMid + NEW.SizeSmall = 100 THEN
        NEW.CheckSize = true;
    END IF;
    RETURN NEW;
END;

$$
LANGUAGE plpgsql;

edited Mar 21 '20 at 22:22

answered Mar 21 '20 at 21:59

Mureinik

297,002
52
306
350

Trigger PostgreSQL to update one value based on the sum of three others

2 Answers2