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

On Mon, 18 Nov 2002 pgsql-bugs@postgresql.org wrote:

> Kristofer Munn (kmunn@munn.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.