Re: INSERT question

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 2001 November 15 03:03 pm, Roland Roberts wrote:
> >>>>> "Jason" == Jason Earl <jason(dot)earl(at)simplot(dot)com> 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(at)xyzzy(dot)dhs(dot)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-----