CODEWITHSUNDEEP
postgresqltriggers
Adam Jenkins
Adam Jenkins

Reputation: 55613

Default value for column postgres function with argument

I have a postgres function that takes one argument. I want to make this function the default value for a column, but I'm not sure how to pass the argument into the table definition.

This is what I mean, I have two columns in the table that look like this:

trade_id INTEGER NOT NULL
group_id INTEGER DEFAULT trade_id_f(argument_goes_here);

I want to make the DEFAULT value of group_id to be the return value of trade_id_f(trade_id) where trade_id is the trade_id of the record to be inserted.

I'm new to all things postgres functions, is this possible?

Upvotes: 13

Views: 18529

Answers (1)

klin
klin

Reputation: 121534

Unfortunately, you cannot do that, because of (for the documentation):

The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed).

You can use a trigger, e.g.:

create table the_table (
    trade_id int not null,
    group_id int);

create or replace function trade_id_trigger ()
returns trigger language plpgsql as $$
begin
    new.group_id:= new.trade_id+ 1;
    return new;
end $$;

create trigger trade_id_trigger
before insert or update on the_table
for each row execute procedure trade_id_trigger();

insert into the_table values (1,1);

select * from the_table;

 trade_id | group_id
----------+----------
        1 |        2
(1 row)

Upvotes: 18

Related Questions