Calculate a column value during INSERT

Question

I am using Postgresql 8.3

I have a Database table contaning buy_value and sell_value. I wish to add a DEFAULT function so that on Every Insert, database will calculate the profit according to the buy and sell values and insert that to the related column...

How should I define my alter?

Answer 1

it's not as simple as changing the DEFAULT value... you'll need a trigger before insert. (but its not recomended to store calculated fields on the table, because you risk to break the data integrity)

check this out

http://www.postgresql.org/docs/9.1/static/triggers.html

Good Luck

Answer 2

I can't really think of a good reason to store such computed column in postgres.

In terms of speed of writing and reading - additional I/O will produce performance hit which can very hardly be justified (maybe in most CPU bound systems, but even then for such trivial operation it would not make sense).

Usually storing computed columns is necessary to create an index, however postgres has functional indexes, so you can create an index without having a materialized column and for the rest of the purposes use a view.

Answer 3

drop table foo2 cascade;
create table foo2 (val int, valplus int);
create or replace function fooplusonetrig()
returns trigger as $$
declare
  rec record;
begin
  raise notice 'here with % %',new.val,new.valplus;
  new.valplus := new.val + 1;
  return new;
end; $$ language 'plpgsql';
create trigger fooplusonetrig before insert ON foo2 for each row execute procedure fooplusonetrig();
insert into foo2 values (2,1);
select * from foo2;
 val | valplus 
-----+---------
   2 |       3