Re: [GENERAL] SQL-question: returning the id of an insert querry
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. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SQL-question: returning the id of an insert querry
scott.marlowe [EMAIL PROTECTED] writes:
select tablename.fieldname.currval;
That syntax would be problematic, it would mean to select all rows from
tablename and evaluate fieldname.currval for each one. Actually it's worse, it
would be confused with schemas I think.
The postgres-ish way to do this would be to create a function like currval
that took a table and column and told you the currval of the sequence
associated with it.
Well you can already do something like that:
db= create or replace function currval(text,text) returns bigint as 'select
currval($1 || ''_'' || $2 || ''_seq'')' language sql strict;
CREATE FUNCTION
db= create table test (a serial);
NOTICE: CREATE TABLE will create implicit sequence test_a_seq for serial column
test.a
CREATE TABLE
db= insert into test(a) values (default);
INSERT 14080230 1
db= select currval('test','a');
currval
-
1
(1 row)
The only problem arises if you use table names or column names that cause
postgres to truncate the resulting sequence name. This could be worked-around
by using the dependency information instead of depending on the naming scheme.
But as long as you do that the above works fine. And means you could always
change your naming scheme or method for looking up the associated sequence
later without changing all your sql.
--
greg
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] SQL-question: returning the id of an insert querry
Scott Chapman [EMAIL PROTECTED] writes:
On Wednesday 12 November 2003 11:29, Doug McNaught wrote:
Scott Chapman [EMAIL PROTECTED] writes:
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?
Write a database function that inserts the record and returns the
primary key value? That's probably the best way to insulate your app
from the database structure...
The function still has to know which sequence to pull from doesn't it?
Yes. It's theoretically possible to derive that information if you
have enough system-tables-fu, but since the function knows which
table it's inserting into, it's not hard to put the proper sequence
name in as well.
I don't know much about triggers/functions in PG. Is it possible to
have a function that intercepts the information AFTER the sequence
value is added as the new primary key and then return it? This would
enable the use of a more generic function.
Sure, in the function you would basically do (I forget the exact
pl/pgsql syntax):
INSERT INTO foo VALUES (...);
SELECT currval('the_pk_sequence') INTO pk;
RETURN pk;
Doesn't remove the need to know or derive the proper sequence name.
There is no what primary key did I just insert built into PG. And
you will need a separate function for each table.
But this way the DB knowledge resides in the DB and you just have a
nice clean API for inserting data from the clients. The schema can
change and the API will (homefully) remain the same...
-Doug
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] SQL-question: returning the id of an insert querry
Scott Chapman [EMAIL PROTECTED] writes: 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? Write a database function that inserts the record and returns the primary key value? That's probably the best way to insulate your app from the database structure... -Doug ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] SQL-question: returning the id of an insert querry
On Mon, Nov 10, 2003 at 08:09:29AM -0800, Scott Chapman wrote: Chronological events here: X inserts a new record into A. Y inserts a new record into A. X fetches currval of the SA. What value does X get in this case, the one from X's insert or Y's? X's. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) ¿Qué importan los años? Lo que realmente importa es comprobar que a fin de cuentas la mejor edad de la vida es estar vivo (Mafalda) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SQL-question: returning the id of an insert querry
On Monday 10 November 2003 08:23, David Green wrote:
Are X Y two different connections?
If you execute 2 statements on the same connection and then get
currval() it will give the last generated id.
Ex.
On 1 connection:
INSERT INTO A (fld) VALUES (val); -- id generated = 1
INSERT INTO A (fld) VALUES (val2); -- id generated = 2
SELECT currval('SA');
2
Thanks for the clarification. With web applications and connection
pooling, it would appear that it's quite easy to get incorrect values
back. This is what I thought.
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?:
def _queryInsertID(self, conn, table, idName, names, values):
c = conn.cursor()
q = self._insertSQL(table, names, values)
if self.debug:
print 'QueryIns: %s' % q
c.execute(q)
c.execute('SELECT %s FROM %s WHERE oid = %s'
% (idName, table, c.lastoid()))
return c.fetchone()[0]
The other way to do it would be to manually fetch nextval and insert
into the table over-riding the default for the ID field (assuming it
defaulted to the nextval in the sequence). I don't know which way is
best (for performance, for instance).
It's be nice if INSERT could be made to return the OID or (better yet)
the primary key field value when it completes. That would solve this
problem in one action and completely remove the need for the second
query. I expect it would have to be user-togglable so it didn't break
with existing code?
Scott
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] SQL-question: returning the id of an insert querry
I saw this method of Statement class in jdbc.
Will the return int contain the autogenerated key value ??
public int executeUpdate(String sql,
int autoGeneratedKeys)
throws SQLException
thanks,
kathy
Scott Chapman wrote:
On Monday 10 November 2003 08:23, David Green wrote:
Are X Y two different connections?
If you execute 2 statements on the same connection and then get
currval() it will give the last generated id.
Ex.
On 1 connection:
INSERT INTO A (fld) VALUES (val); -- id generated = 1
INSERT INTO A (fld) VALUES (val2); -- id generated = 2
SELECT currval('SA');
2
Thanks for the clarification. With web applications and connection
pooling, it would appear that it's quite easy to get incorrect values
back. This is what I thought.
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?:
def _queryInsertID(self, conn, table, idName, names, values):
c = conn.cursor()
q = self._insertSQL(table, names, values)
if self.debug:
print 'QueryIns: %s' % q
c.execute(q)
c.execute('SELECT %s FROM %s WHERE oid = %s'
% (idName, table, c.lastoid()))
return c.fetchone()[0]
The other way to do it would be to manually fetch nextval and insert
into the table over-riding the default for the ID field (assuming it
defaulted to the nextval in the sequence). I don't know which way is
best (for performance, for instance).
It's be nice if INSERT could be made to return the OID or (better yet)
the primary key field value when it completes. That would solve this
problem in one action and completely remove the need for the second
query. I expect it would have to be user-togglable so it didn't break
with existing code?
Scott
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL-question: returning the id of an insert querry
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Martijn van Oosterhout wrote:
After you've done the insert on the address table, you can use
currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you have
to have used nextval() for the original insert.
Hope this helps,
..going to try it. Thanks
Andreas Fromm
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE/rkMwPkvkZVZzNY0RAnajAJ0ePCTi/UODhGAxOs5NuptZAT0tUgCgpNAz
Oqh8rM934O3SRRzv4Mh9S4I=
=E71z
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] SQL-question: returning the id of an insert querry
On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:
After you've done the insert on the address table, you can use
currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you
have to have used nextval() for the original insert.
What if someone else inserts another address before I get the currval?
I'm out of luck then, right?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] SQL-question: returning the id of an insert querry
Scott Chapman [EMAIL PROTECTED] writes:
On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:
After you've done the insert on the address table, you can use
currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you
have to have used nextval() for the original insert.
What if someone else inserts another address before I get the currval?
I'm out of luck then, right?
No, currval() handles that--see the docs.
-Doug
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] SQL-question: returning the id of an insert querry
On Sun, Nov 09, 2003 at 10:26:51AM -0800, Scott Chapman wrote:
On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:
After you've done the insert on the address table, you can use
currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you
have to have used nextval() for the original insert.
What if someone else inserts another address before I get the currval?
I'm out of luck then, right?
No, currval is concurrency-safe. That's exactly what sequences are for.
--
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
I call it GNU/Linux. Except the GNU/ is silent. (Ben Reiter)
---(end of broadcast)---
TIP 8: explain analyze is your friend
