Re: Custom functions for default values for columns on insert

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Lim Berger" <straightfwd007(at)gmail(dot)com>
Cc: "Postgresql General List" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Custom functions for default values for columns on insert
Date: 2007-08-15 04:28:43
Message-ID: 23595.1187152123@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Lim Berger" <straightfwd007(at)gmail(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2007-08-15 04:32:49 Re: Blobs in Postgresql
Previous Message Jasbinder Singh Bali 2007-08-15 04:16:47 Re: language interface in postgresql