Re: [NOVICE] Last ID Problem - Mailing list pgsql-hackers

From Mark Cave-Ayland
Subject Re: [NOVICE] Last ID Problem
Date
Msg-id 9EB50F1A91413F4FA63019487FCD251DADED@WEBBASEDDC.webbasedltd.local
Whole thread Raw
Responses Re: [NOVICE] Last ID Problem
List 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
"UPDATE RETURNING"
> 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,

Mark.

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

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk




pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Our getopt_long() doesn't do abbreviations or NLS
Next
From: Peter Eisentraut
Date:
Subject: libpq API incompatibility between 7.4 and 8.0