[DB-SIG] http://www.python.org/topics/database/DatabaseAPI-2.0.html

Billy G. Allie Billy G. Allie" <Bill.Allie@mug.org
Mon, 02 Sep 2002 03:28:03 -0400


--==_Exmh_-827310966P
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

* Jekabs Andrushaitis <jekabs.andrusaitis@tietoenator.com> [2002-08-30 17=
:13 =

+0200]:
> If I understood the question here correctly - I am looking for answer t=
o it
> too.
> I am using PostgreSQL however, but I have tables with fields whose
> default values are taken from sequence. Since I need to know the
> primary key value for record directly after insertion, I am fetching
> next value from sequence seperately, and only then inserting the row
> into table with primary key field value supplied.
> =

> Is there a way to easily fetch the field values for inserted row after
> insert statement execution?

With PostgreSQL, you can get the last inserted sequence number using =

currval().  This is transaction specific ...  The value returned is the l=
ast =

sequence number in the current transaction.  For example:

create table testit (a serial primary key, b text);
begin;
insert into testit (b) values ('hello world'); =

select currval('testit_a_seq');
commit;

This will insert the new record and the 'select currval(...)' will return=
 the =

assigned sequence number.

Doing this outside a transaction will return the last used sequence numbe=
r, no =

matter which transaction caused it to be generated.

I hope this helps. =

-- =

____       | Billy G. Allie    | Domain....: Bill.Allie@mug.org
|  /|      | 7436 Hartwell     | MSN.......: B_G_Allie@email.msn.com
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |



--==_Exmh_-827310966P
Content-Type: application/pgp-signature

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

Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

* Jekabs Andrushaitis <jekabs.andrusaitis@tietoenator.com> [2002-08-30 17=
:13 =

+0200]:
> If I understood the question here correctly - I am looking for answer t=
o it
> too.
> I am using PostgreSQL however, but I have tables with fields whose
> default values are taken from sequence. Since I need to know the
> primary key value for record directly after insertion, I am fetching
> next value from sequence seperately, and only then inserting the row
> into table with primary key field value supplied.
> =

> Is there a way to easily fetch the field values for inserted row after
> insert statement execution?

With PostgreSQL, you can get the last inserted sequence number using =

currval().  This is transaction specific ...  The value returned is the l=
ast =

sequence number in the current transaction.  For example:

create table testit (a serial primary key, b text);
begin;
insert into testit (b) values ('hello world'); =

select currval('testit_a_seq');
commit;

This will insert the new record and the 'select currval(...)' will return=
 the =

assigned sequence number.

Doing this outside a transaction will return the last used sequence numbe=
r, no =

matter which transaction caused it to be generated.

I hope this helps. =

- -- =

____       | Billy G. Allie    | Domain....: Bill.Allie@mug.org
|  /|      | 7436 Hartwell     | MSN.......: B_G_Allie@email.msn.com
|-/-|----- | Dearborn, MI 48126|
|/  |LLIE  | (313) 582-1540    |


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.2 (UnixWare)
Comment: Exmh version 2.2 06/23/2000

iD8DBQE9cxMDnmIkMXoVVdURApfjAKClsdgMMxXk4vGh5KRFompuD9hjjgCff5qM
keABBozEScTLbbUwgYaXKcw=
=W9xY
-----END PGP SIGNATURE-----

--==_Exmh_-827310966P--