Received: from mail.perdue.net (c696732-a.west1.ia.home.com [24.22.227.157]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2EHpIx52674 for ; Wed, 14 Mar 2001 12:51:18 -0500 (EST) (envelope-from tperdue@mail.perdue.net) Received: (from tperdue@localhost) by mail.perdue.net (8.11.2/8.11.2/Debian 8.11.2-1) id f2EHoF412294 for pgsql-sql@postgresql.org; Wed, 14 Mar 2001 11:50:15 -0600 Date: Wed, 14 Mar 2001 11:50:15 -0600 From: Tim Perdue To: pgsql-sql@postgresql.org Subject: Rule/currval() issue Message-ID: <20010314115015.R670@mail.perdue.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.3.12i X-Archive-Number: 200103/282 X-Sequence-Number: 1944 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