X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 47E1AD1B209
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Jul 2004 08:47:02 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 79108-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Jul 2004 11:46:59 +0000 (GMT)
Received: from mail.greatgulfhomes.com (mail.greatgulfhomes.com
	[216.191.52.66])
	by svr1.postgresql.org (Postfix) with ESMTP id 72EDBD1B17B
	for <pgsql-sql@postgresql.org>; Thu, 22 Jul 2004 08:46:51 -0300 (ADT)
Received: from terrynew2 (unknown [216.191.52.74])
	by mail.greatgulfhomes.com (Postfix) with SMTP
	id 9CCF03668F5; Thu, 22 Jul 2004 07:54:55 -0400 (EDT)
From: <terry@ashtonwoodshomes.com>
To: "'Kenneth Gonsalves'" <lawgon@thenilgiris.com>,
	"Postgresql Sql Group (E-mail)" <pgsql-sql@postgresql.org>
Subject: Re: next integer in serial key
Date: Thu, 22 Jul 2004 07:48:22 -0400
Message-ID: <019001c46fe1$cb182b20$2766f30a@development.greatgulfhomes.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
In-Reply-To: <0407220943130Q.01150@thenilgiris.com>
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Importance: Normal
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME
X-Spam-Level: 
X-Archive-Number: 200407/172
X-Sequence-Number: 18424

Actually it does work, call nextval to get your next value, then call your INSERT statement,
explicitly giving said value for the serial column.  Then you can proceed with using said value in
the INSERT statement of the related inserts with foreign keys to it.

Alternatively, you can do:
INSERT  (accepting the default)
then SELECT currval(the_sequence_object);
then <extra inserts of related foreign key records>

NOTE: 2nd method assumes that nobody else called nextval() on the sequence between when you did the
insert and when you did the select currval().  Note that being inside a transaction is NOT
sufficient, you need an explicit lock on the sequence.  I do not recommend the 2nd method, too much
can go wrong.

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085


> -----Original Message-----
> From: Kenneth Gonsalves [mailto:lawgon@thenilgiris.com]
> Sent: Thursday, July 22, 2004 12:13 AM
> To: terry@greatgulfhomes.com
> Subject: Re: [SQL] next integer in serial key
>
>
> On Thursday 22 July 2004 10:25 am, you wrote:
> > The same way the default value is defined, which you can
> find by doing:
> > \d tablename
> >
> > Which usually gives something like:
> >                                           Table
> "public.gbs_floorplans"
> >         Column        |     Type      |
>
> > Modifiers
> >
> >
> ----------------------+---------------+-----------------------
> -------------
> >----- ---------------------------------
> >  floorplan_id         | integer       | not null default
> > nextval('public.gbs_floorplans_floorplan_id_seq'::text)
> >  division_id          | character(3)  | not null
> >  floorplan_display_id | character(10) | not null
> >
> > Hence
> > SELECT nextval('public.gbs_floorplans_floorplan_id_seq'::text)
>
> nope. what happens is that i enter data into a table with a
> serial type id,
> and then use that id as a foreign key to enter data into
> another table. so i
> need to know the id for the second entry. i commit after both entries
> succeed. If i use nextval to find the id, this increments the
> id, which will
> defeat the purpose.
> --
> regards
> kg
>
> http://www.onlineindianhotels.net - hotel bookings
> reservations in over 4600
> hotels in India
> http://www.ootygolfclub.org
>