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