Skip site navigation (1) Skip section navigation (2)

Re: [NOVICE] Last ID Problem

From: "Mark Cave-Ayland" <m(dot)cave-ayland(at)webbased(dot)co(dot)uk>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,"'Alvaro Herrera'" <alvherre(at)dcc(dot)uchile(dot)cl>,"'Michael Fuhr'" <mike(at)fuhr(dot)org>,"'Mitch Pirtle'" <mitch(dot)pirtle(at)gmail(dot)com>,"'Tatsuo Ishii'" <t-ishii(at)sra(dot)co(dot)jp>, <pgsql-hackers(at)postgresql(dot)org>,<operationsengineer1(at)yahoo(dot)com>
Subject: Re: [NOVICE] Last ID Problem
Date: 2005-02-02 09:13:12
Message-ID: 9EB50F1A91413F4FA63019487FCD251DADED@WEBBASEDDC.webbasedltd.local (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Hi Tom and others,

> I think the correct solution is not to mess with what's admittedly a
legacy aspect of
> our client API.  Instead we should invent the "INSERT RETURNING" and
> commands that have been discussed repeatedly (see the pghackers archives).
That would 
> allow people to get what they want, and do so in only one network round
trip, without 
> any artificial dependencies on OIDs or TIDs or anything else.  It'd be
unportable, but 
> surely no more so than relying on OIDs or TIDs ...

Just off the top of my head, would it not be feasible to add a column to
pg_class called lastinsert that points to the OID of the pg_attribute column
to return after an insert? It could be changed using something similar to
"ALTER TABLE x SET LASTINSERT TO y", but by default it would be set to the
OID of the primary key of the table if the table specified WITHOUT OIDS at
creation time, or the first column of the table otherwise. After the INSERT
command, the value of the resulting is column is passed back to the client.

I see that INSERT...RETURNING is a solution to the problem, but it seems
somewhat strange to have to use an unportable command just to be able to
return an identifier for the last inserted record...

Kind regards,


WebBased Ltd
South West Technology Centre
Tamar Science Park
PL6 8BT 

T: +44 (0)1752 791021
F: +44 (0)1752 791023


pgsql-hackers by date

Next:From: Rafael Martinez GuerreroDate: 2005-02-02 11:17:25
Subject: Problems with initdb 8.0.1
Previous:From: Peter EisentrautDate: 2005-02-02 09:02:49
Subject: Re: Our getopt_long() doesn't do abbreviations or NLS

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group