Thread: Custom functions for default values for columns on insert

The "default" values of a column during table definition do not accept
values generated by passing another column's value through my own
function. So I try to do this with a rule as follows. The name of my
function in this example is MYFUNCTION.


drop table test cascade;

create table test (id serial primary key, nowd timestamp without time
zone, processed_id varchar(10));

create or replace rule test_ins as on insert to test
  DO UPDATE test
  SET processed_id = MYFUNCTION(NEW.id)
  WHERE id = NEW.id
 ;

insert into test (nowd) values (current_timestamp);
insert into test (nowd) values (now());
select * from test;



This results in the "processed_id" column coming up blank. What am I
doing wrong? How can I make sure that upon insert of a row, the value
of one column ("id in my example) is used to immediately generate the
value of another column ("process_id" in my example).

"Lim Berger" <straightfwd007@gmail.com> writes:
> create table test (id serial primary key, nowd timestamp without time
> zone, processed_id varchar(10));

> create or replace rule test_ins as on insert to test
>   DO UPDATE test
>   SET processed_id = MYFUNCTION(NEW.id)
>   WHERE id = NEW.id
>  ;

I think you are getting burnt by the fact that a rule is a macro
and therefore subject to multiple-evaluation-of-arguments hazards.
In particular, the reference to NEW.id probably results in an extra
evaluation (or two?) of nextval() on the serial sequence.

Even if this worked, it'd be horrendously inefficient, because of having
to apply the full machinery of UPDATE to fix up the row.  Instead you
should use a BEFORE INSERT trigger to apply the change to the NEW
record before it ever gets stored.

            regards, tom lane