Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA12353 for ; Sun, 6 Jun 1999 08:30:54 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP for id 6954E4B511; Sun, 6 Jun 1999 15:30:53 +0300 (IDT) Received: from [147.233.159.109] (mac-herouth.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA1E01; Sun, 6 Jun 1999 15:33:27 +0300 X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <551413BBBB03D111BF880060B01AACCE0B38FA@home.priority.net> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Sun, 6 Jun 1999 15:31:45 +0300 To: "Pham, Thinh" , "'pgsql-sql@postgresql.org'" From: Herouth Maoz Subject: Re: [SQL] Getting primary key from insert statement At 00:46 +0300 on 03/06/1999, Pham, Thinh wrote: > Hi, is there a way you can get back the primary key (serialized) from an > insert you just do on a table. For example i have a table called "order" and > i want to get the pid of that record so i can use it in the "orderdetail" > table. It would be nice if that same insert statement could also return the > pid. Yes. You have to know the name of the sequence which the serial type caused to be created. You are notified of the name when you create the table. It would be 'order_pid_seq' in the case you described (remember that ORDER is a reserved word, though). Anyway, after you make the insert, you can retrieve the recently-created value of the sequence, using currval( 'order_pid_seq' ). This returns the value of the last invocation of nextval( 'order_pid_seq' ) in the current session. When you do an insert, it calls nextval internally. And thus you will get a proper currval. The question everybody asks is "but what if somebody else manages to insert another row before I use currval?". This is not a concern, because currval always returns the number your own session generated, and not anybody else. Note that currval will not work until somebody does a nextval on the same sequence. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma