Re: Bug #820: RULE on INSERT unable to access NEW serial

On Mon, 18 Nov 2002 pgsql-bugs(at)postgresql(dot)org wrote:

> Kristofer Munn (kmunn(at)munn(dot)com) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> RULE on INSERT unable to access NEW serial value anymore
>
> Long Description

> Enclosed is example code that behaves differently on
> versions 7.1.3 and 7.2.3 with a loss of functionality in the latter
> version.

I don't think rules really work for this purpose.

Even from your 7.1.3 example, notice that you don't really have a
functional key linking the two, rowid=(1,3,5) in dbmodlog and (2,4,6) in
id.

> id | name | userid
> ----+-----------------+--------
> 2 | this | 7
> 4 | that | 8
> 6 | the other thing | 9
> (3 rows)
>
> test=# select * from dbmodlog;
> id | tablename | rowid | action | userid | modtime
> ----+-----------+-------+--------+--------+------------------------
> 1 | tblData | 1 | I | 7 | 2002-11-18 22:10:18-05
> 2 | tblData | 3 | I | 8 | 2002-11-18 22:10:18-05
> 3 | tblData | 5 | I | 9 | 2002-11-18 22:10:18-05
> (3 rows)

And even replace NEW.id with a call to currval which might work for single
row cases fails for multiple row inserts. I'd suggest converting to using
a trigger.