CODEWITHSUNDEEP
postgresqlstored-procedurestriggersinsert
sjwbond
sjwbond

Reputation: 65

Postgres trigger procedure on insert using data in inserted fields to insert calculated data in another field

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.

Upvotes: 4

Views: 1943

Answers (2)

michal.jakubeczy
michal.jakubeczy

Reputation: 9489

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).

Upvotes: 1

user330315
user330315

Reputation:

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.*

Upvotes: 7

Related Questions