Thread

SQL-question: returning the id of an insert querry

Andreas Fromm <andreas.fromm@physik.uni-erlangen.de> — 2003-11-09T10:29:49Z

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Im building an user database with many tables keeping the data for the
Address, Phone numbers, etc which are referenced by a table where I keep
the single users. My question is, how do I get the "Id"-value of a newly
inserted address to store it in the referencing user table:

(a)  INSERT INTO address VALUES (....);

(b)  INSERT INTO users VALUES ( name, ... , address , ... );

where address should hold the value of the Id from the Adress table.


Do have to do an
SELECT id FROM address WHERE oid = oid_returned_by_insert(a)
or something like that after doing the insert(a) to get the correct id
value, or is there a better way to do this.

Im writing my app in Perl with DBD/DBI


Thanks in advance,

Andreas Fromm

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/rhcdPkvkZVZzNY0RApmDAJ4k4MY/zKvH2862MuHSIjDtsmIs3QCfRzaR
0zDc1bIQAOMpLurvRZ2V8JY=
=kgaA
-----END PGP SIGNATURE-----

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

Martijn van Oosterhout <kleptog@svana.org> — 2003-11-09T11:13:31Z

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,

On Sun, Nov 09, 2003 at 11:29:49AM +0100, Andreas Fromm wrote:
> Hi,
> 
> Im building an user database with many tables keeping the data for the
> Address, Phone numbers, etc which are referenced by a table where I keep
> the single users. My question is, how do I get the "Id"-value of a newly
> inserted address to store it in the referencing user table:
> 
> (a)  INSERT INTO address VALUES (....);
> 
> (b)  INSERT INTO users VALUES ( name, ... , address , ... );
> 
> where address should hold the value of the Id from the Adress table.
> 
> 
> Do have to do an
> SELECT id FROM address WHERE oid = oid_returned_by_insert(a)
> or something like that after doing the insert(a) to get the correct id
> value, or is there a better way to do this.
> 
> Im writing my app in Perl with DBD/DBI
> 
> 
> Thanks in advance,
> 
> Andreas Fromm
> 
> 
> 
> ---------------------------(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

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato

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

Andreas Fromm <andreas.fromm@physik.uni-erlangen.de> — 2003-11-09T13:37:52Z

-----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-----

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

Scott Chapman <scott_list@mischko.com> — 2003-11-09T18:26:51Z

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?

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

Douglas McNaught <doug@mcnaught.org> — 2003-11-09T18:38:16Z

Scott Chapman <scott_list@mischko.com> 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

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

Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-11-09T18:52:45Z

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)

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

Scott Chapman <scott_list@mischko.com> — 2003-11-10T16:09:29Z

On Sunday 09 November 2003 10:52, Alvaro Herrera wrote:
> 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.

I just want to clarify what I mean here to make sure I understand this 
right. I have a table, A, that has a ID field which defaults to nextval 
of a sequence, SA. 

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?

Scott

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

Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-11-10T16:12:59Z

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)

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

David Green <david@sagerobot.com> — 2003-11-10T16:23:09Z

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

On 2 connections:
conn1.execute("INSERT INTO A (fld) VALUES (val)") -- id generated = 1
conn2.execute("INSERT INTO A (fld) VALUES (val2)") -- id generated = 2
conn1.execute("SELECT currval('SA')")
1
conn2.execute("SELECT currval('SA')")
2


David Green
Sage Automation, Inc


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Scott Chapman
Sent: Monday, November 10, 2003 10:09 AM
To: Alvaro Herrera
Cc: Martijn van Oosterhout; Andreas Fromm; pgsql-general@postgresql.org
Subject: Re: [GENERAL] SQL-question: returning the id of an insert
querry


On Sunday 09 November 2003 10:52, Alvaro Herrera wrote:
> 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.

I just want to clarify what I mean here to make sure I understand this 
right. I have a table, A, that has a ID field which defaults to nextval 
of a sequence, SA. 

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?

Scott



---------------------------(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: SQL-question: returning the id of an insert querry

Scott Chapman <scott_list@mischko.com> — 2003-11-10T16:56:03Z

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

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

Kathy Zhu <kathy.zhu@sun.com> — 2003-11-10T17:22:25Z

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

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

Ian Harding <iharding@tpchd.org> — 2003-11-10T20:50:34Z

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. 
>
Huh?  My web application has connection pooling and it goes like this:

<receive a request to do something>
Get a handle from the pool.
Do your insert.
Do your currval select.
Do whatever else you need to do...return data to user maybe.
Put the handle back in pool.
<wait for more requests to do something>

Nobody can grab my database handle til I am done with it.  I can use it 
as much as I like before I put it back.   It is put back by default at 
the end of the function if not explicitly put back.

You will never get "incorrect values" if you call currval immediately 
after an insert while using the same handle.

I would not use a web application that got a new handle for every sql 
statement executed.

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

Paul Thomas <paul@tmsl.demon.co.uk> — 2003-11-10T21:34:52Z

On 10/11/2003 17:22 Kathy Zhu wrote:
> 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

This is one of a number of JDBC3 extensions which are just stub methods 
ATM. You'll find it will just throw an exception if called.


-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller 
Business |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

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

Bruno Wolff III <bruno@wolff.to> — 2003-11-10T21:46:11Z

On Mon, Nov 10, 2003 at 08:56:03 -0800,
  Scott Chapman <scott_list@mischko.com> wrote:
> 
> 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. 

Not normally. Normally both queries are going to be done in one web
request and it would be very unusual to be using a system where
anyone else could use the same connection for a query in between.

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

Andrew Sullivan <andrew@libertyrms.info> — 2003-11-12T11:51:11Z

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 Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

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

Scott Chapman <scott_list@mischko.com> — 2003-11-12T19:17:26Z

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

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

Douglas McNaught <doug@mcnaught.org> — 2003-11-12T19:29:01Z

Scott Chapman <scott_list@mischko.com> 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

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

Scott Chapman <scott_list@mischko.com> — 2003-11-12T19:46:44Z

On Wednesday 12 November 2003 11:29, Doug McNaught wrote:
> Scott Chapman <scott_list@mischko.com> 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?

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.

Scott

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

Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-11-12T20:20:32Z

On Wed, Nov 12, 2003 at 11:46:44AM -0800, Scott Chapman wrote:
> On Wednesday 12 November 2003 11:29, Doug McNaught wrote:
> >
> > 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, but it could pull it from the system catalogs ... (not too
portable)

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No necesitamos banderas
No reconocemos fronteras"                  (Jorge González)

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

Douglas McNaught <doug@mcnaught.org> — 2003-11-12T20:31:28Z

Scott Chapman <scott_list@mischko.com> writes:

> On Wednesday 12 November 2003 11:29, Doug McNaught wrote:
> > Scott Chapman <scott_list@mischko.com> 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

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

Scott Chapman <scott_list@mischko.com> — 2003-11-12T20:35:27Z

On Wednesday 12 November 2003 12:31, Doug McNaught wrote:
> Scott Chapman <scott_list@mischko.com> writes:
> > On Wednesday 12 November 2003 11:29, Doug McNaught wrote:
> > > Scott Chapman <scott_list@mischko.com> 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...

What's the process to suggest changes to PG along these lines?  Say, a 
proposal to make it configurable for a user to have a INSERT return the 
primary key that it just inserted rather than what it returns now?

Scott

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

Douglas McNaught <doug@mcnaught.org> — 2003-11-12T20:41:34Z

Scott Chapman <scott_list@mischko.com> writes:

> What's the process to suggest changes to PG along these lines?  Say, a 
> proposal to make it configurable for a user to have a INSERT return the 
> primary key that it just inserted rather than what it returns now?

What if you have a multicolumn PK?

I'm not actually sure PG keeps track of "primary keyness" -- I think
it translates a PRIMARY KEY constraint into NOT NULL UNIQUE when the
table is created.  So it might be really hard to implement your
feature, even if you can figure out what should happen for multicolumn
PKs.

-Doug

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

Andrew Sullivan <andrew@libertyrms.info> — 2003-11-12T20:45:02Z

On Wed, Nov 12, 2003 at 12:35:27PM -0800, Scott Chapman wrote:
> What's the process to suggest changes to PG along these lines?  Say, a 
> proposal to make it configurable for a user to have a INSERT return the 
> primary key that it just inserted rather than what it returns now?

Take a well-worked-out proposal to the folks on -hackers, and either
wait for someone else to do the work (unlikely), or do the work
yourself, and submit a patch.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

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

scott.marlowe <scott.marlowe@ihs.com> — 2003-11-12T21:17:31Z

On Wed, 12 Nov 2003, Scott Chapman wrote:

> On Wednesday 12 November 2003 12:31, Doug McNaught wrote:
> > Scott Chapman <scott_list@mischko.com> writes:
> > > On Wednesday 12 November 2003 11:29, Doug McNaught wrote:
> > > > Scott Chapman <scott_list@mischko.com> 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...
> 
> What's the process to suggest changes to PG along these lines?  Say, a 
> proposal to make it configurable for a user to have a INSERT return the 
> primary key that it just inserted rather than what it returns now?

I wouldn't suggest changing current bevaiour (i.e. the number of rows 
inserted is probably a SQL SPEC thing) but to have each serial column in a 
table be addressable so you'd just do:

select tablename.fieldname.currval;

and you'd get the currval back for the serial.  note that if the serial 
value was implemented by hand like:

create table test (id int primary key default 
'myseq'::text||nextval('seqname')::text);

that currval or the equivalent would actually give back the key inserted, 
'myseqx' where x was the sequence number.

The folks are -hackers are always willing to listen to a good idea, but 
they've got plenty on their plates, so this kind of thing needs to be at 
the least thought out well enough so they won't have lots of 
implementation problems with the plan.

It might also be possible to have the serial type create a plsql function 
that has the name tablename_fieldname_currval() and returns the last 
currval('seqname') with a simple wrapper.  That solution would be fairly 
easy to implement, and would be quite useful.

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

Greg Stark <gsstark@mit.edu> — 2003-11-12T22:35:28Z

"scott.marlowe" <scott.marlowe@ihs.com> 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

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

Tom Lane <tgl@sss.pgh.pa.us> — 2003-11-12T22:38:31Z

Doug McNaught <doug@mcnaught.org> writes:
> Scott Chapman <scott_list@mischko.com> writes:
>> What's the process to suggest changes to PG along these lines?  Say, a 
>> proposal to make it configurable for a user to have a INSERT return the 
>> primary key that it just inserted rather than what it returns now?

> What if you have a multicolumn PK?

Or a PK that's not an integer?  Or no PK at all?

There's not likely to be any interest in hotwiring INSERT to return
a different command tag than it does now; that would break too much
existing code.  There has been some talk of inventing an "INSERT
... RETURNING ..." syntax extension that would return whatever
expressions you cared to compute from inserted rows --- but in the form
of a SELECT result, not by trying to squeeze it into a command tag.
I think the last discussion of this was a year or so back in pg-hackers.

			regards, tom lane