pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: lynch@lscorp.com
To: pgsql-sql@postgresql.org
Subject: insert/select returned table
Date: Wed, 10 Jun 1998 18:01:27 -0500
Message-ID: <v02140b00b1a4744fa7e0@[207.152.64.133]> (raw)

Okay, so this is actually in Access and ASP, but that's not my fault!
[The company I work for is really messed up in some ways.]

Anyway, it's an SQL question, and I don't want to actually join an
Access/ASP list, if such exist...

So, I've got a table of records with an "autonumber" unique ID.

When a web user inserts a new record, I need the ID back from *that* record
in an atomic operation.  Since it is on the web, the potential for a race
condition is high.

insert into story (story_id, title) select Max(story_id) + 1, 'Title' from story

worked fine in Access.  Well, it always gave one higher than the actual
value inserted for the ID, but I can subtract 1.  Alas, when I send it over
the web, it inserts, but when attempting to get the resulting ID, I get a
message about a closed object being inaccessible.  Presumably, Access is
trashing the temp table from this query. :-(

So, how do I go about doing this the right way?...

NOTE:  There *ARE* records whose only distinct field is ID, so I can't just
follow up with a select for the data input.

If your answer is Access/ASP specific, please reply direct only.  No sense
cluttering this list more than I have.

PS  I guess I'd better join an Access/ASP list... Anybody know of one?

--
--
-- "TANSTAAFL" Rich lynch@lscorp.com





Message-ID: <v02140b00b1a4744fa7e0@[207.152.64.133]>
Permalink:  ../v02140b00b1a4744fa7e0@%5B207.152.64.133%5D/
Also on:    postgresql.org/message-id/v02140b00b1a4744fa7e0@[207.152.64.133]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: lynch@lscorp.com
  Subject: Re: insert/select returned table
  In-Reply-To: <v02140b00b1a4744fa7e0@[207.152.64.133]>
  References: <v02140b00b1a4744fa7e0@[207.152.64.133]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox