0

I need compute age and update column of age with trigger after Insert Or Update.

Here is my code

CREATE OR REPLACE function to_age()
RETURNS TRIGGER AS $$
    declare ages int;
    BEGIN
        select date_part('year', age(old.dob)) into ages from employees;
--         date_part('year', age(old.dob));
        new.age_of_person := ages;
        raise notice 'success(%)', new.age_of_person;
        RETURN new;
    end;
    $$
LANGUAGE plpgsql;


CREATE TRIGGER ages
    AFTER INSERT OR UPDATE ON employees
    FOR EACH STATEMENT
    EXECUTE FUNCTION to_age();
DROP TRIGGER ages on employees;

And here is my table that I need update.

CREATE TABLE employees(
   id INT GENERATED ALWAYS AS IDENTITY,
   first_name VARCHAR(40) NOT NULL,
   last_name VARCHAR(40) NOT NULL,
   dob timestamp,
   age_of_person int,
   PRIMARY KEY(id)
);

I have mistake and I did not find where. I can not compute age, it raised null in the function.

Alikhan
  • 1
  • 2
    You should not be storing age in your database in the first place, even if it is updated with a trigger. Why are you storing it? (And if you really need that value, you should use a Computed Column so you'll _always_ have a correct value instead of ad-hoc updates via a trigger). Triggers are unreliable and cannot prove the data at-rest is always correct: that's what constraints are for. – Dai Nov 30 '21 at 09:13
  • Why do you store the age? It can change any day. And what's wrong with the function AGE() ? – Frank Heikens Nov 30 '21 at 09:40
  • While the remark from @Dai is correct, the recommendation is currently not possible to implement. At the moment, [PostgreSQL only supports `stored`](https://www.postgresql.org/docs/14/ddl-generated-columns.html) generated columns which will not be dynamic. Until `virtual` generated columns are introduced, you have to calculate the age in your `select`, hide the table behind a view that calculates the age, or use a [function with attribute notation](https://stackoverflow.com/questions/11165450/store-common-query-as-column/11166268#11166268). – Zegarek Nov 30 '21 at 09:45
  • @Zegarek Oh dang - that's surprising to hear, I always thought PostgreSQL was ahead-of-the-curve on these kinds of things... – Dai Nov 30 '21 at 09:47
  • 1
    No need for a SELECT in the trigger. `new.age_of_person := date_part('year', age(new.dob));` But the trigger does not make sense. If the row is never updated, the age will never change. –  Nov 30 '21 at 09:54
  • In addition of the above valuable comments, why do you `DROP` your trigger ages after having created it ??? – Edouard Nov 30 '21 at 09:55
  • No trigger nor a calculated column are needed actually but just a simple view, `create view v_employees as select *, date_part('year', age(dob)) as age_of_person from employees;`. `age_of_person` column is volatile, remove it from table `employees`. – Stefanov.sm Nov 30 '21 at 10:03

0 Answers0