RE: transaction safety - Mailing list pgsql-general

Hi,

The number returned by the sequence for the serial ID is retained within the session, and so it can be returned by calling currval, e.g.:

CREATE TABLE person (
        id serial primary key,
        name varchar not null,
        dob timestamp not null
);
CREATE TABLE address (
        id serial primary key,
        id_person int not null references person(id),
        address varchar not null
);

INSERT INTO person (name, dob) VALUES ('Peter', '03/09/1945');
INSERT INTO address (id_person, address) VALUES (currval('person_id_seq'), '44 Willowdown Road');
INSERT INTO address (id_person, address) VALUES (currval('person_id_seq'), '23 Second Ave. (Second Home)');
INSERT INTO person (name, dob) VALUES ('Jane', '06/12/1958');
INSERT INTO address (id_person, address) VALUES (currval('person_id_seq'), '16 Parsons Crescent');

Typically, the insert for a person, and for all the associated addresses would be done in the same transaction so that if the insert for one of the addresses failed, then the whole lot would role back (perhaps a bit extreme, but I think that's what you asked for ;-)

Cheers...

MikeA

-----Original Message-----
From: DaVinci [mailto:bombadil@wanadoo.es]
Sent: 12 February 2001 11:34
To: Lista PostgreSql
Subject: Re: [GENERAL] transaction safety

On Mon, Feb 12, 2001 at 11:08:55AM +0000, Oliver Elphick wrote:
> DaVinci wrote:
>   > Hi all.
>   >
>   > I want to create a new tuple of main info and detail tuples (in
>   > different tables) that are joined by a key field. Key field is created by
>   > generator automatically and I need that number in order to assign to detail
>   > tuples. How can I to get that number in a safe way?.
>
> A successful INSERT returns the oid of the row just created; so get the
> new value with a query like this:
>
>   SELECT key_field FROM table WHERE oid = new_oid_value;

 I have a new question for this idea (thanks). When Database is big, do i
 need index for oid field to speed select?.

 Greets.

                                                                                                                        David

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************