Re: SQL-question: returning the id of an insert querry - Mailing list pgsql-general

From Scott Chapman
Subject Re: SQL-question: returning the id of an insert querry
Date
Msg-id 200311121117.26774.scott_list@mischko.com
Whole thread Raw
In response to Re: SQL-question: returning the id of an insert querry  (Andrew Sullivan <andrew@libertyrms.info>)
List pgsql-general
On Wednesday 12 November 2003 03:51, Andrew Sullivan wrote:
> On Mon, Nov 10, 2003 at 08:56:03AM -0800, Scott Chapman wrote:
> > I talked with the author or SQLObject about this recently and I
> > thnk he's implementing this correctly, by querying the cursor for
> > the last OID?:
>
> That won't scale unless you index oid.  And your tables will all need
> oids, which is not standard any more.
>
> If you do your work in one transaction and get the currval that way,
> it is impossible to go wrong.  Also, if you don't return the
> connection to the pool before getting the currval, you will not go
> wrong.

Then there's another issue.  If I insert a record and I don't have
OID's, I have to know which sequence to query currval out of, right?

If that's true, then I have to have much more knowlege about the
database structures in my front-end application, which is a Bad Thing.

It would be nice if PostgreSQL could return the primary key it inserted
with but that may not be a fool-proof solution either.  Is there a nice
way to handle this situation?

Scott

pgsql-general by date:

Previous
From: Rajesh Kumar Mallah
Date:
Subject: Re: procpid in pg_stat_activity but no correspoding backend
Next
From: Doug McNaught
Date:
Subject: Re: SQL-question: returning the id of an insert querry