Calculated columns with a trigger

Some say that calculated columns is bad design because it does not follow the relational model. It is true but performance can be greatly enhanced.

In this simple example, we have a table with 3 columns. The first and the second are given by the user and the third is calculated by a trigger. We add a condition : x * 0 = 0 * x = 1 (This fantasy comes from the requirements of a customer).  It is done with the case expression.

Some say that calculated columns is bad design because it does not follow the relational model. It is true but performance can be greatly enhanced.

In this simple example, we add a condition : x * 0 = 0 * x = 1. It is done with the case construct.

drop table if exists tup;

create table tup (
     col1   numeric(10,2)
    ,col2   numeric(10,2)
    ,col3   numeric(10,2)
);

insert into tup(col1,col2) values
     (2.87      ,3.77)
    ,(4         ,5.11)
    ,(2.12      ,0)
    ,(0.0       ,3);

update tup
    set col3 =     (case col1 when 0 then 1 else col1 end)
                *  (case col2 when 0 then 1 else col2 end);

select * from tup;

will give

col1     col2   col3
---------------------
2.87     3.77   10.82
4.00     5.11   20.44
2.12     0.00    2.12
0.00     3.00    3.00

Let now add the trigger and the trigger function :

create or replace function calculate_columns() returns trigger as $$
begin

  new.col3 =   (case new.col1 when 0 then 1 else new.col1 end)
             * (case new.col2 when 0 then 1 else new.col2 end);

  return new;

end $$ language plpgsql;

create trigger calculated_columns  
    before insert or update 
    on tup
    for each row
    execute procedure calculate_columns();

Note that we do a before insert or update trigger and that we update the columns in the row new.

insert into tup(col1, col2) values
     (6.23      ,2)
    ,(0         ,55.11);

select * from tup;

will give :

col1     col2   col3
---------------------
2.87     3.77   10.82
4.00     5.11   20.44
2.12     0.00    2.12
0.00     3.00    3.00
6.23     2.00   12.46
0.00    55.11   55.11

The deal is in the bag!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s