Postgres - trigger function not working with generated columns as input

Question

Before I post the DDL for my table, trigger function, and trigger, I'll just ask a simple question - Can a trigger function work with generated columns assigned to variables in an AFTER INSERT event? I created such a trigger, and although it doesn't throw an error it doesn't insert the computed values. If such a scenario should work, I'll follow up with a post containing the table definition and code. Otherwise, I'll have to write a standalone function.

If you see my comment, the problem is not likely to be the generated columns.
Here is my table definition. Note that the generated columns to convert mgl (milligrams/liter) to meql (milliequivalents per liter) works without problem:

CREATE TABLE IF NOT EXISTS public.wq
(
 id integer NOT NULL DEFAULT nextval('wq_id_seq'::regclass),
 well_no character varying(50) COLLATE pg_catalog."default" NOT NULL,
 sample_no character varying(50) COLLATE pg_catalog."default",
 temp numeric(5,2),
 ph numeric(4,2),
 ec numeric(6,0),
 ca_mgl numeric(6,2),
 ca_meql numeric(9,6) GENERATED ALWAYS AS (((ca_mgl / (40)::numeric) * (2)::numeric)) STORED,
 mg_mgl numeric(6,2),
 mg_meql numeric(9,6) GENERATED ALWAYS AS (((mg_mgl / 24.3) * (2)::numeric)) STORED,
 na_mgl numeric(6,2),
 na_meql numeric(9,6) GENERATED ALWAYS AS (((na_mgl / (23)::numeric) * (1)::numeric)) STORED,
 k_mgl numeric(6,2),
 k_meql numeric(9,6) GENERATED ALWAYS AS (((k_mgl / (39)::numeric) * (1)::numeric)) STORED,
 cl_mgl numeric(6,2),
 cl_meql numeric(9,6) GENERATED ALWAYS AS (((cl_mgl / 35.4) * (1)::numeric)) STORED,
 hco3_alk_mgl numeric(6,2),
 hco3_alk_meql numeric(9,2) GENERATED ALWAYS AS (((hco3_alk_mgl / (61)::numeric) * (1)::numeric)) STORED,
 so4_mgl numeric(6,2),
 so4_meql numeric(9,6) GENERATED ALWAYS AS (((so4_mgl / 96.07) * (2)::numeric)) STORED,
 disso2 numeric(6,2),
 no3_mgl numeric(6,2),
 no3_meql numeric(9,6) GENERATED ALWAYS AS (((no3_mgl / 62.01) * (1)::numeric)) STORED,
 fe_mgl numeric(6,2),
 fe_meql numeric(9,6) GENERATED ALWAYS AS (((fe_mgl / 55.85) * (2)::numeric)) STORED,
 f_mgl numeric(6,2),
 f_meql numeric(9,6) GENERATED ALWAYS AS (((f_mgl / (19)::numeric) * (1)::numeric)) STORED,
 as_mgl numeric(6,2),
 sum_cat_meql numeric(9,6),
 sum_an_meql numeric(9,6),
 tdi numeric(10,6),
 error_perc numeric(5,2) GENERATED ALWAYS AS ((((sum_cat_meql - sum_an_meql) / (sum_cat_meql + sum_an_meql)) * (100)::numeric)) STORED

Here is my trigger function

plpgsql
CREATE OR REPLACE FUNCTION public.fn_1_wq_ionbalance()
 RETURNS trigger
 LANGUAGE 'plpgsql'
 COST 100
 VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE 
 sumcat numeric(9,6) := null;
 suman numeric(9,6) := null;
BEGIN
 sumcat = new.ca_meql + new.mg_meql + new.na_meql + new.k_meql;
 suman = new.cl_meql + new.hco3_alk_meql + new.so4_meql ;
 new.sum_cat_meql =sumcat;
 new.sum_an_meql = suman;
 
 RETURN NEW;
END;
$BODY$;

If I replace the columns in the function to the ordinary numeric fields mgl, it still doesn't fill in the value for the sum_cat_meql and sum_an_meql fields. So the problem isn't related to generated columns

Here is the trigger:

CREATE OR REPLACE TRIGGER a_tr_wq_ionbalance
 AFTER INSERT OR UPDATE 
 ON public.wq
 FOR EACH ROW
 EXECUTE FUNCTION public.fn_1_wq_ionbalance();

Can anyone spot a problem?

• Nicholas Lombardi: Okay, the problem may not be with generated columns, as I used ordinary columns as a surrogate, and it still doesn't work. I will post the table definition, and the trigger function & trigger

Answer 1

For the assignment to NEW to take effect, it has to be a BEFORE trigger, not an AFTER trigger like you have now.

Like:

CREATE OR REPLACE FUNCTION public.fn_1_wq_ionbalance()
 RETURNS trigger
 LANGUAGE plpgsql AS
$func$
BEGIN
 NEW.sum_cat_meql := NEW.ca_meql + NEW.mg_meql + NEW.na_meql + NEW.k_meql;
 NEW.sum_an_meql := NEW.cl_meql + NEW.hco3_alk_meql + NEW.so4_meql ;
 RETURN NEW;
END
$func$;
CREATE OR REPLACE TRIGGER a_tr_wq_ionbalance
BEFORE INSERT OR UPDATE ON public.wq
FOR EACH ROW
EXECUTE FUNCTION public.fn_1_wq_ionbalance();

But the whole idea is dubious. In modern Postgres, you would rather use true generated columns (like you already have for other columns). See:

• Computed / calculated / virtual / derived / generated columns in PostgreSQL

You did not declare, but the use of EXECUTE FUNCTION indicates at least Postgres 11.

In most cases it's better to not store the redundant, functionally dependent values in the table at all. Consider a VIEW or MATERIALIZED VIEW instead.

• Nicholas Lombardi: Thanks Erwin. It doesn't work either way. This trigger can't be used with generated columns. But I took your advice and used a view with a cte. Works fine.