# 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. When a function is applied in a query on a indexed column, the query optimizer will not use the index. It is thus sometimes better to have an index on the calculated column because queries from the front end applications will run faster.

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 = x (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.

The condition : x * 0 = 0 * x = x is implemented with a 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!