Received: from femail15.sdc1.sfba.home.com (femail15.sdc1.sfba.home.com [24.0.95.142]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAFLNOm45592 for ; Thu, 15 Nov 2001 16:23:24 -0500 (EST) (envelope-from drew@xyzzy.dhs.org) Received: from xyzzy.lan.internal ([24.226.81.186]) by femail15.sdc1.sfba.home.com (InterMail vM.4.01.03.20 201-229-121-120-20010223) with ESMTP id <20011115212321.XIJU880.femail15.sdc1.sfba.home.com@xyzzy.lan.internal>; Thu, 15 Nov 2001 13:23:21 -0800 Received: from localhost ([127.0.0.1] helo=there ident=drew) by xyzzy.lan.internal with smtp (Exim 3.32 #1 (Debian)) id 164Tyh-0003Da-00; Thu, 15 Nov 2001 16:23:19 -0500 Content-Type: text/plain; charset="iso-8859-1" From: "Andrew G. Hammond" To: Roland Roberts , pgsql-sql@postgresql.org Subject: Re: INSERT question Date: Thu, 15 Nov 2001 16:23:16 -0500 X-Mailer: KMail [version 1.3.2] References: <1f5f2b44.0111130130.17e8e57a@posting.google.com> <878zd7vpzn.fsf@npa01zz001.simplot.com> In-Reply-To: MIME-Version: 1.0 Message-Id: Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id fAFLUKn46526 X-Archive-Number: 200111/227 X-Sequence-Number: 5610 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 2001 November 15 03:03 pm, Roland Roberts wrote: > >>>>> "Jason" == Jason Earl writes: > > Jason> Even better, however, is to use the functions nextval() and > Jason> currval() like so: > > Jason> INSERT INTO parent_table (data) values ('some data'); > Jason> INSERT INTO child_table (parent, more_data) > Jason> (currval('parent_table_p_key_seq'), > Jason> 'more data'); > > The above is probably the best for a pure-SQL way with the caveat > that it should be wrapped in a transaction or currval() may not be > what you expect; i.e., another client may add a row and you get a > value different from what you inserted. Actually, I don't think you need the transaction. currval() "may not be what you expect" in a very different way from what you're impling above! Session1: SELECT nextval('test') # 1 Session2: SELECT nextval('test') # 2 Session1: SELECT currval('test') # 1 !!!!!!! Session1: SELECT nextval('test') # 3 Kinda counter-intuitive, but fits the definition. - -- Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iEYEARECAAYFAjv0MkUACgkQCT73CrRXhLEVkwCdGmGvMzc4y3emRPKxWGoGpPiL WO8AnRekzrv9XVicymCtsplg0QbKFF1M =iTsp -----END PGP SIGNATURE-----