How insert count of a table in another table attribute by trigger postgresql-CodePudding

I have created two tables "post" and "node" and I want to assign the sum of the entities of the "node" table in the attribute "nb_noeud" of the "post" table by trigger. But, the code below does not work and I think I missed something. My code is as follows:

CREATE TABLE noeud
(
    id_noeud serial NOT NULL,
    code_noeud varchar(10) NULL,
    type_noeud t_noeud NULL,
    phase t_phase NULL
    x_32632 bigint NULL,
    y_32632 bigint NULL,
    geom_noeud geometry(point) NULL,
    obs text NULL
)
;

CREATE TABLE poste
(
    id_pt serial NOT NULL,
    code_pt varchar(8) NULL,
    nom_pt varchar(50) NULL,
    nb_noeud smallint NULL,
    geom_pt geometry(polygon) NULL,
    surf_pt numeric(15,2) NULL,
    obs text NULL
)
;

CREATE OR REPLACE FUNCTION recap_noeud() RETURNS TRIGGER 
language plpgsql AS 
$$
DECLARE
    som_noeud smallint;
BEGIN
    IF (TG_OP = 'INSERT') THEN
        SELECT COUNT(*) INTO som_noeud FROM noeud;
        UPDATE poste set NEW.nb_noeud = som_noeud;
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        SELECT COUNT(*) INTO som_noeud FROM noeud;
        UPDATE poste set NEW.nb_noeud = som_noeud;
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        RETURN NULL;
    ELSE
        RAISE WARNING 'Other action occurred: %, at %', TG_OP, now();
        RETURN NULL;
    END IF;
END;
$$
;

DROP TRIGGER IF EXISTS trig_recap_noeud ON noeud;
CREATE TRIGGER trig_recap_noeud AFTER INSERT OR UPDATE OR DELETE ON noeud FOR EACH ROW EXECUTE PROCEDURE recap_noeud();

CodePudding user response：

Replace DELETE and INSERT clauses with

IF TG_OP = 'INSERT' OR TG_OP = 'DELETE' THEN
        SELECT COUNT(*) INTO som_noeud FROM noeud;
        UPDATE poste set nb_noeud = som_noeud;
        RETURN NULL;

Best regards, Bjarni