Re: Rule/currval() issue

Entirely untested, but how about replacing currval() in your first try
with nextval()? My theory is that the compilation of the function is
happening before the INSERT happens; therefore the sequence hasn't been
incremented yet; therefore there's no currval() for this backend
instance. If you use nextval(), you'll get the next increment, which
should be appropriate.

As I think about it, this could have record-locking implications in a
heavy use environment, since the possibility exists of another INSERT
between the nextval() and the INSERT in this situation - I don't know if
that's actually an issue, or if there would be a way around it.

Andy Perrin

Tim Perdue wrote:
>
> This is related to the plpgsql project I was working on this morning. I'm
> trying to create a rule, so that when a row is inserted into a certain table,
> we also create a row over in a "counter table". The problem lies in getting
> the primary key value (from the sequence) so it can be inserted in that
> related table.
>
> I tried a couple different approaches. Neither works.
>
> artifact_group_list is a table where the primary key, group_artifact_id is
> SERIAL type. When I insert a row, I want to get that new value in my rule.
>
> --
> -- Define a rule so when you create a new ArtifactType
> -- You automatically create a related row over in the counters table
> --
> CREATE RULE artifactgroup_insert_agg AS
> ON INSERT TO artifact_group_list
> DO INSERT INTO
> artifact_counts_agg (group_artifact_id,count,open_count)
> VALUES (currval('artifact_grou_group_artifac_seq'),0,0);
>
> I get this:
>
> ERROR: artifact_grou_group_artifac_seq.currval is not yet defined in this session
>
> If I write the rule this way:
>
> CREATE RULE artifactgroup_insert_agg AS
> ON INSERT TO artifact_group_list
> DO INSERT INTO
> artifact_counts_agg (group_artifact_id,count,open_count)
> VALUES (new.group_artifact_id,0,0);
>
> ...it doesn't fail with an error, but the sequence increments twice.
>
> Tim
>
> --
> Founder - PHPBuilder.com / Geocrawler.com
> Lead Developer - SourceForge
> VA Linux Systems
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
--------------------------------------------------------------
Andrew J. Perrin - Programmer/Analyst, Desktop Support
Children's Primary Care Research Group, UNC - Chapel Hill
(919)966-9394 * andrew_perrin(at)unc(dot)edu