Say I have a table X, and X has fields X1, X2 and X3.
I want a solution that whenever I insert values for X1 and X2, X3 is set automatically to the sum of X1 and X2 with a trigger.
This is something very simple to do in mysql, but after a while on google I cant find a good example of this for postgres, just some info about For each row triggers, but again no examples.
Create the trigger function:
create or replace function update_calc_column()
returns trigger
as
$$
begin
new.x3 := new.x1 + new.x2;
return new;
end;
$$
language plpgsql;
Create the trigger:
create trigger calc_trigger
before insert or update on X
for each row
execute procedure update_calc_column();
SQLFiddle: http://sqlfiddle.com/#!15/7ed21/1
The above is essentially a stripped down version of the example in the manual
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE
However, storing derived data like that is usually not a good idea. You should simply create a view that returns a column X3 which is defined as X1 + X2 - a lot less code to maintain and just as efficient (actually it's more efficient because you get rid of the trigger overhead).
Another (more exotic) option is to use Postgres' object oriented extension and create a virtual column:
create or replace function x3(data X) --<< yes, the type of the parameter is the name of the table
returns integer
as
$$
select data.x1 + data.x2;
$$
language sql;
You can then use:
select x.*, x.x3
from x;
SQLFiddle: http://sqlfiddle.com/#!15/53acf/1
This however has the disadvantage that you need to explicitely select the x3 column. It will not show up when using x.*
If all you need is just to calculate the X3 value as a sum of X1 and X2 you can use
ALTER TABLE yourTable
ADD COLUMN x3 BIGINT GENERATED ALWAYS AS (x1 + x2) STORED;
A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column. So from the performance point of view it is also performant (no calculations are done on SELECT).
