Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 075CD476173
	for <pgsql-sql@postgresql.org>; Mon, 20 May 2002 10:24:55 -0400 (EDT)
Received: from temp.joelburton.com (unknown [209.190.238.101])
	by postgresql.org (Postfix) with ESMTP id D2F0F475F97
	for <pgsql-sql@postgresql.org>; Mon, 20 May 2002 10:24:26 -0400 (EDT)
Received: from joeltf0d13cyyp (unknown [209.190.238.100])
	by temp.joelburton.com (Postfix) with SMTP
	id 70D7C2BD0D; Mon, 20 May 2002 12:07:22 -0400 (EDT)
From: "Joel Burton" <joel@joelburton.com>
To: "Eckhard Hoeffner" <e-hoeffner@fifoost.org>,
	"SQL PostgreSQL" <pgsql-sql@postgresql.org>
Subject: Re: Using LASTOID in one query
Date: Mon, 20 May 2002 10:24:30 -0400
Message-ID: <JGEPJNMCKODMDHGOBKDNKEMOCOAA.joel@joelburton.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-15"
Content-Transfer-Encoding: 8bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0)
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Importance: Normal
In-Reply-To: <20020520140835.GA6211@fifoost.org>
X-Archive-Number: 200205/202
X-Sequence-Number: 7837

If you're using SERIAL for the table_a primary key, you don't need to use
the OID, just use the sequence information.

INSERT INTO Table_A (a_text) VALUES ('hello');
INSERT INTO Table_B (number1, b_text) VALUES
(currval('Table_A_number1_seq'), 'there');

Faster, easier, works in a transaction, and not PHP-specific.

See the system docs on currval(), nextval(), and setval().

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Eckhard Hoeffner
> Sent: Monday, May 20, 2002 10:09 AM
> To: SQL PostgreSQL
> Subject: [SQL] Using LASTOID in one query
>
>
> Is there a possibility to use the last OID from PHP within one
> query? I have two tables like the following:
>
> table_a:
> number1 serial PRIMARY KEY
> a_text text
>
> table_b:
> number1 integer REFERENCES table_a,
> b_text text
>
> I am retrieving data like
> a_text = something
> b_text = something else
>
>
> With psql I would do the following:
> BEGIN;
> INSERT INTO table_a (a_text) VALUES ('something');
> INSERT 224490 1
> SELECT number1 FROM table_a WHERE oid = 224490;
>  number1
>  -------
>    9
> (1 row)
> INSERT INTO table_b (number1, b_text) VALUES (9, 'something else');
> INSERT 224491 1;
> COMMIT;
>
> If I am using php, the only solution I know is:
> 1. make the first INSERT, execute it
> 2. get the last oid and make the next query, execute it
> 3. make the second INSERT
>
> This are 3 queries and I can not do it within a transaction.
>
>
> --
> --//--\\--
> Eckhard Hoeffner
> e-hoeffner@fifoost.org
> Tal 44
> D-80331 München
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>