Here is a small patch that implements a function lastval() that
works just like currval() except that it give the current
value of the last sequence used by nextval().

Using this function one can do:

# CREATE TABLE abc (a serial, b int);
CREATE TABLE

# SELECT lastval();
ERROR:  nextval have not been used in the current session

# INSERT INTO abc(b) VALUES (42);
INSERT 0 1

# SELECT lastval();
 lastval
---------
       1


Some comments about the implementetion
--------------------------------------

Each backend keeps a list of all used sequences in the session. This patch
adds a sequence pointer that point out one of the sequences in the list
and which is updated by nextval(). This is a simple pointer assignment so
it's very cheap (almost zero cost).

lastval() works just like currval but use the pointed out sequence
instead of geting a sequence name as an argument.

One can implement this by storing the value instead of the sequence
pointer but I decided it's a good thing that it works just like
currval(), behaving the same with respect to rights, locks and such.

General comments
----------------

I know that some of you might want to name this function the same as the
similar function in mysql (LAST_INSERT_ID), but I prefer to name it
similar to the old sequence functions. It's easy to add a LAST_INSERT_ID()  
function that call lastval() if needed. Also, LAST_INSERT_ID() in mysql
will always succeed and it returns 0 if there have not been any row
inserted (at least what I think it will do that based on a quick look in
the mysql doc). The above function does not work like that.

-- 
/Dennis Björklund
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.245
diff -u -c -r1.245 func.sgml
*** doc/src/sgml/func.sgml      13 Apr 2005 00:20:10 -0000      1.245
--- doc/src/sgml/func.sgml      8 May 2005 16:24:16 -0000
***************
*** 6475,6480 ****
--- 6475,6483 ----
     <primary>currval</primary>
    </indexterm>
    <indexterm>
+    <primary>lastval</primary>
+   </indexterm>
+   <indexterm>
     <primary>setval</primary>
    </indexterm>
  
***************
*** 6509,6514 ****
--- 6512,6523 ----
          <entry>Return value most recently obtained with 
<function>nextval</function></entry>
        </row>
        <row>
+         <entry><literal><function>lastval</function>()</literal></entry>
+         <entry><type>bigint</type></entry>
+         <entry>Return the current value of the last sequence that 
<function>nextval</function>
+                was invoked on.</entry>
+       </row>
+       <row>
          <entry><literal><function>setval</function>(<type>text</type>, 
<type>bigint</type>)</literal></entry>
          <entry><type>bigint</type></entry>
          <entry>Set sequence's current value</entry>
***************
*** 6575,6580 ****
--- 6584,6600 ----
       </varlistentry>
  
       <varlistentry>
+       <term><function>lastval</function></term>
+       <listitem>
+        <para>
+         This function works exactly as <function>currval</function> except 
that
+         instead of taking the sequence name as an argument it will fetch the 
current
+       value of the last sequence that <function>nextval</function> was used 
on.
+        </para>
+       </listitem>
+      </varlistentry>
+ 
+      <varlistentry>
        <term><function>setval</function></term>
        <listitem>
         <para>
Index: src/backend/commands/sequence.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/sequence.c,v
retrieving revision 1.119
diff -u -c -r1.119 sequence.c
*** src/backend/commands/sequence.c     31 Dec 2004 21:59:41 -0000      1.119
--- src/backend/commands/sequence.c     8 May 2005 16:24:18 -0000
***************
*** 68,74 ****
--- 68,80 ----
  
  static SeqTable seqtab = NULL;        /* Head of list of SeqTable items */
  
+ /*
+  * last_used_seq is updated by nextval() to point out the last used
+  * sequence. It is the sequence used by lastval()
+  */
+ static SeqTableData *last_used_seq = NULL;
  
+ static void acquire_share_lock (Relation seqrel, SeqTableData *data);
  static void init_sequence(RangeVar *relation,
                          SeqTable *p_elm, Relation *p_rel);
  static Form_pg_sequence read_info(SeqTable elm, Relation rel, Buffer *buf);
***************
*** 402,407 ****
--- 408,414 ----
        if (elm->last != elm->cached)           /* some numbers were cached */
        {
                elm->last += elm->increment;
+               last_used_seq = elm;
                relation_close(seqrel, NoLock);
                PG_RETURN_INT64(elm->last);
        }
***************
*** 522,527 ****
--- 529,536 ----
        elm->last = result;                     /* last returned number */
        elm->cached = last;                     /* last fetched number */
  
+       last_used_seq = elm;
+ 
        START_CRIT_SECTION();
  
        /* XLOG stuff */
***************
*** 604,609 ****
--- 613,653 ----
        PG_RETURN_INT64(result);
  }
  
+ Datum
+ lastval(PG_FUNCTION_ARGS)
+ {
+       Relation        seqrel;
+       int64           result;
+ 
+       if (last_used_seq == NULL) {
+               ereport(ERROR,
+                               
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+                                errmsg("nextval have not been used in the 
current session")));
+       }
+ 
+       seqrel = relation_open(last_used_seq->relid, NoLock);
+ 
+       acquire_share_lock (seqrel, last_used_seq);
+ 
+       if (pg_class_aclcheck(last_used_seq->relid, GetUserId(), ACL_SELECT) != 
ACLCHECK_OK)
+               ereport(ERROR,
+                               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                                errmsg("permission denied for sequence with 
OID %d",
+                                               last_used_seq->relid)));
+ 
+       if (last_used_seq->increment == 0)      /* nextval/read_info were not 
called */
+               ereport(ERROR,
+                               
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+                                errmsg("currval of sequence with OID %d is not 
yet defined in this session",
+                                               last_used_seq->relid)));
+ 
+       result = last_used_seq->last;
+ 
+       relation_close(seqrel, NoLock);
+ 
+       PG_RETURN_INT64(result);
+ }
+ 
  /*
   * Main internal procedure that handles 2 & 3 arg forms of SETVAL.
   *
***************
*** 745,750 ****
--- 789,831 ----
  
  
  /*
+  * If we haven't touched the sequence already in this transaction,
+  * we need to acquire AccessShareLock.  We arrange for the lock to
+  * be owned by the top transaction, so that we don't need to do it
+  * more than once per xact.
+  */
+ static void
+ acquire_share_lock (Relation seqrel,
+                                       SeqTableData *data)
+ {
+       TransactionId thisxid = GetTopTransactionId();
+ 
+       if (data->xid != thisxid)
+       {
+               ResourceOwner currentOwner;
+               
+               currentOwner = CurrentResourceOwner;
+               PG_TRY();
+               {
+                       CurrentResourceOwner = TopTransactionResourceOwner;
+ 
+                       LockRelation(seqrel, AccessShareLock);
+               }
+               PG_CATCH();
+               {
+                       /* Ensure CurrentResourceOwner is restored on error */
+                       CurrentResourceOwner = currentOwner;
+                       PG_RE_THROW();
+               }
+               PG_END_TRY();
+               CurrentResourceOwner = currentOwner;
+ 
+               /* Flag that we have a lock in the current xact. */
+               data->xid = thisxid;
+       }
+ }
+ 
+ /*
   * Given a relation name, open and lock the sequence.  p_elm and p_rel are
   * output parameters.
   */
***************
*** 752,758 ****
  init_sequence(RangeVar *relation, SeqTable *p_elm, Relation *p_rel)
  {
        Oid                     relid = RangeVarGetRelid(relation, false);
-       TransactionId thisxid = GetTopTransactionId();
        volatile SeqTable elm;
        Relation        seqrel;
  
--- 833,838 ----
***************
*** 800,834 ****
                seqtab = elm;
        }
  
!       /*
!        * If we haven't touched the sequence already in this transaction,
!        * we need to acquire AccessShareLock.  We arrange for the lock to
!        * be owned by the top transaction, so that we don't need to do it
!        * more than once per xact.
!        */
!       if (elm->xid != thisxid)
!       {
!               ResourceOwner currentOwner;
! 
!               currentOwner = CurrentResourceOwner;
!               PG_TRY();
!               {
!                       CurrentResourceOwner = TopTransactionResourceOwner;
! 
!                       LockRelation(seqrel, AccessShareLock);
!               }
!               PG_CATCH();
!               {
!                       /* Ensure CurrentResourceOwner is restored on error */
!                       CurrentResourceOwner = currentOwner;
!                       PG_RE_THROW();
!               }
!               PG_END_TRY();
!               CurrentResourceOwner = currentOwner;
! 
!               /* Flag that we have a lock in the current xact. */
!               elm->xid = thisxid;
!       }
  
        *p_elm = elm;
        *p_rel = seqrel;
--- 880,886 ----
                seqtab = elm;
        }
  
!       acquire_share_lock (seqrel, elm);
  
        *p_elm = elm;
        *p_rel = seqrel;
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.360
diff -u -c -r1.360 pg_proc.h
*** src/include/catalog/pg_proc.h       30 Apr 2005 20:31:38 -0000      1.360
--- src/include/catalog/pg_proc.h       8 May 2005 16:24:21 -0000
***************
*** 3636,3641 ****
--- 3636,3643 ----
  DESCR("convert int4 to boolean");
  DATA(insert OID = 2558 ( int4                            PGNSP PGUID 12 f f t 
f i 1  23 "16" _null_ _null_ _null_     bool_int4 - _null_ ));
  DESCR("convert boolean to int4");
+ DATA(insert OID = 2559 ( lastval                         PGNSP PGUID 12 f f t 
f v 0 20 "" _null_ _null_ _null_        lastval - _null_ ));
+ DESCR("current value from last used sequence");
  
  
  /*
Index: src/include/commands/sequence.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/sequence.h,v
retrieving revision 1.30
diff -u -c -r1.30 sequence.h
*** src/include/commands/sequence.h     31 Dec 2004 22:03:28 -0000      1.30
--- src/include/commands/sequence.h     8 May 2005 16:24:21 -0000
***************
*** 82,87 ****
--- 82,88 ----
  
  extern Datum nextval(PG_FUNCTION_ARGS);
  extern Datum currval(PG_FUNCTION_ARGS);
+ extern Datum lastval(PG_FUNCTION_ARGS);
  extern Datum setval(PG_FUNCTION_ARGS);
  extern Datum setval_and_iscalled(PG_FUNCTION_ARGS);
  
---------------------------(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

Reply via email to