Re: Fwd: Strange behaviour of RULE (selecting last inserted ID of 'sequenced' column)

yes, I've found some words about similar sutuation on Varlena:

"... The SERIAL column NEW.job_id is effectively replaced by
nextval(...) and the nextval() function is called twice--once for the
original row and once for that traced row."
(http://www.varlena.com/GeneralBits/101.php)

What am I doing? I've just wanted to introduce the rule that allows to
retrieve "last inserted ID" on every INSERT executed.

I'm afraid that this is not expected behaviour. OK, let it be so for
some time...

Simple workaround is: to use currval() instead of NEW.id.

On 8/8/06, Jim Nasby <jnasby(at)pervasive(dot)com> wrote:
> More of a gotcha than a bug... basically, your select rule is hitting
> the sequence again. I think there's a section in the rules chapter
> that talks about this. GeneralBits might also have info.
>
> Probably a better question is, what are you trying to do?
>
> On Aug 4, 2006, at 4:50 AM, Nikolay Samokhvalov wrote:
>
> > I still think that this is quite strange behaviour. When I write
> > '...SELECT NEW.id...' I don't expect that another calling of column's
> > default expr will take place. I just want to have access to "id"
> > column of just-created row.
> >
> > Any thoughts?
> >
> > ---------- Forwarded message ----------
> > From: Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com>
> > Date: Jul 13, 2006 6:15 PM
> > Subject: Strange behaviour of RULE (selecting last inserted ID of
> > 'sequenced' column)
> > To: PostgreSQL-general <pgsql-general(at)postgresql(dot)org>
> >
> >
> > Is this a bug?
> >
> > test=> create sequence strange_seq;
> > CREATE SEQUENCE
> > test=> create table strange(id integer not null default
> > nextval('strange_seq') primary key, data text);
> > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> > "strange_pkey" for table "strange"
> > CREATE TABLE
> > test=> create rule strange_rule as on insert to strange do select
> > new.id as id;
> > CREATE RULE
> > test=> insert into strange(data) values('adas');
> > id
> > ----
> > 2
> > (1 row)
> >
> > test=> select * from strange;
> > id | data
> > ----+------
> > 1 | adas
> > (1 row)
> >
> > test=> insert into strange(data) values('adas');
> > id
> > ----
> > 4
> > (1 row)
> >
> > test=> insert into strange(data) values('adas');
> > id
> > ----
> > 6
> > (1 row)
> >
> > test=> select * from strange;
> > id | data
> > ----+------
> > 1 | adas
> > 3 | adas
> > 5 | adas
> > (3 rows)
> >
> >
> > --
> > Best regards,
> > Nikolay
> >
> >
> > --
> > Best regards,
> > Nikolay
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
>
>

--
Best regards,
Nikolay