Re: lastrowid and INSERT ... RETURNING

On Mon, Aug 4, 2008 at 5:47 AM, Federico Di Gregorio <fog-NGVKUo/i/[email protected]> wrote:
> Il giorno ven, 06/06/2008 alle 17.58 -0700, Cliff Wells ha scritto:
>> On Wed, 2006-07-26 at 08:45 +0200, Federico Di Gregorio wrote:
>> > Il giorno mar, 25/07/2006 alle 15.22 -0700, craigp ha scritto:
>> > > there's a todo item at
>> > > http://www.postgresql.org/docs/faqs.TODO.html (search
>> > > for returning) which would return columns/expressions on
>> > > insert/update. so
>> > > if/when that's done there'll be a mechanism to support the use-case of
>> > > returning the primary-key of a newly-inserted row (among others).
>> >
>> > That would be neat. Please, poke at me when it is ready in pg. :)
>>
>> Poke.  Added in 8.2.4.
>>
>> test=# create table foo ( id serial primary key not null, name text );
>>
>> test=# insert into foo ( name ) values ( 'cliff' ) returning id;
>
> RETURNING can return anything so does it make sense to hack some magic
> to automatically populate .lastrowid when the query is an INSERT and the
> value returned is a long (like the OID were) or is better to simply
> accept that PostgreSQL does not support row ids and put the .fetchone()
> in the code?

If someone issues an "INSERT ... RETURNING" query, they probably know
what they're doing and will check the result set themselves.  As it
won't necessarily be a primary key value, using it for lastrowid will
just make it lastrowid less reliable.


> I am for the second but I can hack .lastrowid if enough people need it
> to transition from old backends with automatic OIDs to the new ones
> where OIDs are not geneated by default.

If people are using higher level systems (e.g. ORMs like SQLObject,
Storm, etc), then this is a non-issue.  If they are doing lower level
stuff, then couldn't they continue to use tables with OIDs if that is
what they need?

James.