Change values before insert into table / PostgreSQL

Ive got the following problem. I want to change some values of INSERT statement, e.g. there’s a query:

INSERT INTO table(a,b) values(x,y);

But table has also another column c. I want to check (before INSERT) value of b from the query above and depending on one’s value then set c=z but only for this specific row I’ve just inserted.

I think that some trigger can help in my problem but I’ve no idea how to do that. Any pseudocode would be appreciated.

Answer

It is the goog practice to restrict direct access to the table(s) for regular users. It can be done by using stored procedures and gives access rights only to them. For example (sorry for long code):

create table foo (
    a integer,
    b integer,
    c integer);

--== Create table. Yes, it is no PK, but for example it is not necesarry

create or replace function p_foo_insert(in aa integer, in ab integer) returns integer as
$$
declare
    result integer;
begin
    insert into foo (a, b, c) values (aa, ab, aa + ab) returning c into result;
    return result;
end; $$ language plpgsql SECURITY DEFINER;

--== It is function for row insertion. Inside this function you can do any data manipulation.

select p_foo_insert(1, 2);

--== Test statement. Result must be a + b = 1 + 2 = 3

So, forget about insert into, use stored procedures :o)

PS: Note about SECURITY DEFINER option. It allows to grant access to the function without granting access to objects used inside it.