Why is my Postgres trigger selecting the first row's value and copying it into the rest entries?

Question

I am working on PostgreSQL trigger and I am new to it, I want to develop a Postgres trigger that on insert of the date of birth (DOB), calculates the age, and inserts that date with the corresponding DOB field in the same table.

below is my work:

create or replace function Age() 
returns trigger as $body$
declare
dob1 date;
age1 integer;
BEGIN
select dob into dob1 from dateage; 
SELECT date_part('year', AGE(dob)) as "age", * FROM dateage into age1;
update dateage set age=age1;
return new;
END; $body$ language plpgsql;

And here goes my function call

create trigger datea after insert on dateage
for each row execute procedure age();

My table goes like this: name DATEAGE with attributes ID| DOB | AGE

When I insert into my table values, The first row is calculated fine, but the age in subsequent rows is just copied from the first row's age value.

thankyou for any contribution in advance.

score 0 · Accepted Answer · 2021-02-22T07:38:04.893

Don't INSERT or update, just calculate the value and assign it:

create or replace function Age() 
  returns trigger 
as 
$body$
BEGIN
  new.age := date_part('year', AGE(new.dob));
  return new;
END; 
$body$ language plpgsql;

Then use this for a BEFORE trigger:

create trigger datea 
  BEFORE insert on dateage
for each row execute procedure age();

To answer the "why is it copying from the first row":

This line:

select dob into dob1 from dateage;

Always retrieves the DOB from one arbitrary row from the table (there is no such thing as the "first row" to begin with).

When you select a row from the table (which you don't need to do to begin with - see above) you have to make sure that it retrieves the row you just inserted by adding a WHERE condition:

select dob 
  into dob1 
from dateage
where pk_column = new.pk_column;

The calculation of the age is also incorrect. You are again selecting this from an arbitrary row from the table - not from the just inserted row.

But as you have just put the dob value into the variable dob1 there is no need to run a SELECT to begin with.

age1 := date_part('year', AGE(dob1));

And finally you also need to constrain the UPDATE to exactly the row you want - again by adding a WHERE condition:

update dateage 
  set age=age1
where pk_column = new.pk_column;

But this is a really bad idea, because next year this information is already wrong. A rule of thumb in relational databases is to never store information that can (easily) be calculated from existing data.

Creating a view with that information is a much better solution:

create view dates_with_age
as
select ..., dob, date_part('year', AGE(dob)) as dateage
from dateage;

This way you get accurate (and correct) values in the dateage column every time you query it, not just for the first year after you insert the value.

Why is my Postgres trigger selecting the first row's value and copying it into the rest entries?

1 Answers1