pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing LASTOID in one query
4+ messages / 3 participants
[nested] [flat]
* Using LASTOID in one query
@ 2002-05-20 14:08 Eckhard Hoeffner <e-hoeffner@fifoost.org>
2002-05-20 14:24 ` Re: Using LASTOID in one query Joel Burton <joel@joelburton.com>
2002-05-20 15:31 ` Re: Using LASTOID in one query Masaru Sugawara <rk73@sea.plala.or.jp>
0 siblings, 2 replies; 4+ messages in thread
From: Eckhard Hoeffner @ 2002-05-20 14:08 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Using LASTOID in one query
2002-05-20 14:08 Using LASTOID in one query Eckhard Hoeffner <e-hoeffner@fifoost.org>
@ 2002-05-20 14:24 ` Joel Burton <joel@joelburton.com>
1 sibling, 0 replies; 4+ messages in thread
From: Joel Burton @ 2002-05-20 14:24 UTC (permalink / raw)
To: Eckhard Hoeffner <e-hoeffner@fifoost.org>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Using LASTOID in one query
2002-05-20 14:08 Using LASTOID in one query Eckhard Hoeffner <e-hoeffner@fifoost.org>
@ 2002-05-20 15:31 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2002-05-20 16:10 ` Re: Using LASTOID in one query Eckhard Hoeffner <e-hoeffner@fifoost.org>
1 sibling, 1 reply; 4+ messages in thread
From: Masaru Sugawara @ 2002-05-20 15:31 UTC (permalink / raw)
To: Eckhard Hoeffner <e-hoeffner@fifoost.org>; +Cc: pgsql-sql
On Mon, 20 May 2002 16:08:36 +0200
Eckhard Hoeffner <e-hoeffner@fifoost.org> wrote:
> Is there a possibility to use the last OID from PHP within one
> query? I have two tables like the following:
You might find something interesting here.
http://www.postgresql.org/idocs/index.php?plpgsql-statements.html
23.5.5. Obtaining result status
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Using LASTOID in one query
2002-05-20 14:08 Using LASTOID in one query Eckhard Hoeffner <e-hoeffner@fifoost.org>
2002-05-20 15:31 ` Re: Using LASTOID in one query Masaru Sugawara <rk73@sea.plala.or.jp>
@ 2002-05-20 16:10 ` Eckhard Hoeffner <e-hoeffner@fifoost.org>
0 siblings, 0 replies; 4+ messages in thread
From: Eckhard Hoeffner @ 2002-05-20 16:10 UTC (permalink / raw)
To: pgsql-sql
* Masaru Sugawara <rk73@sea.plala.or.jp> [20 05 02 17:31]:
>On Mon, 20 May 2002 16:08:36 +0200
>Eckhard Hoeffner <e-hoeffner@fifoost.org> wrote:
>
>> Is there a possibility to use the last OID from PHP within one
>> query? I have two tables like the following:
>
>You might find something interesting here.
>
>http://www.postgresql.org/idocs/index.php?plpgsql-statements.html
>23.5.5. Obtaining result status
Thanks, but Joel already gave me a very good hint for my question.
--
--//--\\--
Eckhard Hoeffner
e-hoeffner@fifoost.org
Tal 44
D-80331 München
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-05-20 16:10 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-20 14:08 Using LASTOID in one query Eckhard Hoeffner <e-hoeffner@fifoost.org>
2002-05-20 14:24 ` Joel Burton <joel@joelburton.com>
2002-05-20 15:31 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2002-05-20 16:10 ` Eckhard Hoeffner <e-hoeffner@fifoost.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox