Re: SQL-question: returning the id of an insert querry

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