Default value for column postgres function with argument

Question

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?

klin · Accepted Answer · 2015-08-24 17:33:35Z

19

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)

edited Aug 24, 2015 at 17:33

answered Aug 24, 2015 at 14:09

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexis.Rolland Over a year ago

Is it still possible to use a custom function as a default expression if this function does not refer to any table and does not require any argument?

klin Over a year ago

@Alexis.Rolland - You can use a custom function with no or constant arguments as a default expression.

Collectives™ on Stack Overflow

Default value for column postgres function with argument

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related