current version: Patch - Have psql show current values for a sequence] - Mailing list pgsql-patches

sorry for sending the old version in the previous mail . Here I attach
the recent version of the patch file.

Dhanaraj M wrote:

> I saw the following in the TODO list.
> clients-psql
> ============
> 1. Have psql show current values for a sequence
>
> Hence, this patch displays the current seq. value in a separate column
> when \ds is executed.
> I attach the patch here. The display format may have to be changed. I
> would like to change the display format as you will suggest me.
> I am awaiting for the review.
>
> Thanks
> Dhanaraj
>
>
>

*** ./src/backend/commands/sequence.c.orig    Tue May  2 14:51:03 2006
--- ./src/backend/commands/sequence.c    Tue May  2 15:00:19 2006
***************
*** 1183,1185 ****
--- 1183,1274 ----
              xlrec->node.spcNode, xlrec->node.dbNode, xlrec->node.relNode);
  }

+
+ /* Returns the current sequence value even if not available in the cache */
+ Datum
+ retrieveval_oid(PG_FUNCTION_ARGS)
+ {
+
+         Oid                     relid = PG_GETARG_DATUM(0);
+         int64           result;
+         SeqTable        elm;
+         Relation        seqrel;
+         Form_pg_sequence seq;
+         Buffer          buf;
+
+         /* open and AccessShareLock sequence */
+         init_sequence(relid, &elm, &seqrel);
+
+         if (pg_class_aclcheck(elm->relid, GetUserId(), ACL_SELECT) != ACLCHECK_OK &&
+                 pg_class_aclcheck(elm->relid, GetUserId(), ACL_USAGE) != ACLCHECK_OK)
+                 ereport(ERROR,
+                                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                                  errmsg("permission denied for sequence %s",
+                                                 RelationGetRelationName(seqrel))));
+
+
+         if ((elm->increment != 0) ||(elm->last != elm->cached)) /* some numbers were cached */
+         {
+                 result = elm->last;
+                 relation_close(seqrel, NoLock);
+                 PG_RETURN_INT64(result);
+         }
+
+         /* lock page' buffer and read tuple if not cached */
+         seq = read_info(elm, seqrel, &buf);
+         result = seq->last_value;
+
+       UnlockReleaseBuffer(buf);
+         relation_close(seqrel, NoLock);
+
+         seqtab = elm->next;
+         free(elm);
+
+         PG_RETURN_INT64(result);
+ }
+
+
+ /* Checks whether the sequence value is already used or not */
+ Datum
+ retrievecheck_oid(PG_FUNCTION_ARGS)
+ {
+
+         Oid                     relid = PG_GETARG_DATUM(0);
+         SeqTable        elm;
+         Relation        seqrel;
+         Form_pg_sequence seq;
+         Buffer          buf;
+         bool            result;
+
+         /* open and AccessShareLock sequence */
+         init_sequence(relid, &elm, &seqrel);
+
+         if (pg_class_aclcheck(elm->relid, GetUserId(), ACL_SELECT) != ACLCHECK_OK &&
+                 pg_class_aclcheck(elm->relid, GetUserId(), ACL_USAGE) != ACLCHECK_OK)
+                 ereport(ERROR,
+                                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                                  errmsg("permission denied for sequence %s",
+                                                 RelationGetRelationName(seqrel))));
+
+
+         if ((elm->increment != 0) ||(elm->last != elm->cached)) /* some numbers were cached */
+         {
+                 relation_close(seqrel, NoLock);
+                 PG_RETURN_INT64(0);
+         }
+
+         /* lock page' buffer and read tuple */
+         seq = read_info(elm, seqrel, &buf);
+         result = seq->is_called;
+
+       UnlockReleaseBuffer(buf);
+         relation_close(seqrel, NoLock);
+
+         seqtab = elm->next;
+         free(elm);
+
+         if (result == true)
+                 PG_RETURN_INT64(0);
+         else
+                 PG_RETURN_INT64(1);
+ }
*** ./src/backend/utils/fmgroids.h.orig    Tue May  2 14:19:25 2006
--- ./src/backend/utils/fmgroids.h    Tue May  2 15:11:08 2006
***************
*** 901,906 ****
--- 901,908 ----
  #define F_NEXTVAL_OID 1574
  #define F_CURRVAL_OID 1575
  #define F_SETVAL_OID 1576
+ #define F_RETRIEVEVAL_OID 1577
+ #define F_RETRIEVECHECK_OID 1578
  #define F_VARBIT_IN 1579
  #define F_VARBIT_OUT 1580
  #define F_BITEQ 1581
*** ./src/backend/utils/fmgrtab.c.orig    Tue May  2 14:19:25 2006
--- ./src/backend/utils/fmgrtab.c    Tue May  2 15:11:08 2006
***************
*** 952,957 ****
--- 952,959 ----
  extern Datum nextval_oid (PG_FUNCTION_ARGS);
  extern Datum currval_oid (PG_FUNCTION_ARGS);
  extern Datum setval_oid (PG_FUNCTION_ARGS);
+ extern Datum retrieveval_oid (PG_FUNCTION_ARGS);
+ extern Datum retrievecheck_oid (PG_FUNCTION_ARGS);
  extern Datum varbit_in (PG_FUNCTION_ARGS);
  extern Datum varbit_out (PG_FUNCTION_ARGS);
  extern Datum biteq (PG_FUNCTION_ARGS);
***************
*** 2661,2666 ****
--- 2663,2670 ----
    { 1574, "nextval_oid", 1, true, false, nextval_oid },
    { 1575, "currval_oid", 1, true, false, currval_oid },
    { 1576, "setval_oid", 2, true, false, setval_oid },
+   { 1577, "retrieveval_oid", 1, true, false, retrieveval_oid },
+   { 1578, "retrievecheck_oid", 1, true, false, retrievecheck_oid },
    { 1579, "varbit_in", 3, true, false, varbit_in },
    { 1580, "varbit_out", 1, true, false, varbit_out },
    { 1581, "biteq", 2, true, false, biteq },
*** ./src/bin/psql/describe.c.orig    Thu Apr 27 04:45:45 2006
--- ./src/bin/psql/describe.c    Tue May  2 15:22:49 2006
***************
*** 1479,1484 ****
--- 1479,1489 ----
                        _("Schema"), _("Name"),
                        _("table"), _("view"), _("index"), _("sequence"),
                        _("special"), _("Type"), _("Owner"));
+     if (showSeq && !showTables)
+         appendPQExpBuffer(&buf,
+                     ",\n retrieveval(c.oid) as \"%s\""
+                     ",\n CASE retrievecheck(c.oid) WHEN '1' THEN '%s' WHEN '0' THEN '%s' END as \"%s\"",
+                     _("value"),_("  ***"),_(""),_("Start from"));

      if (showIndexes)
          appendPQExpBuffer(&buf,
***************
*** 1546,1551 ****
--- 1551,1559 ----
          myopt.nullPrint = NULL;
          myopt.title = _("List of relations");

+         if (showSeq && !showTables)
+             myopt.title = _("List of relations (*** - specifies the starting value and it has not yet been used)");
+
          printQuery(res, &myopt, pset.queryFout, pset.logfile);
      }

*** ./src/include/catalog/pg_proc.h.orig    Tue May  2 15:06:15 2006
--- ./src/include/catalog/pg_proc.h    Tue May  2 15:07:33 2006
***************
*** 2057,2062 ****
--- 2057,2067 ----
  DESCR("sequence current value");
  DATA(insert OID = 1576 (  setval            PGNSP PGUID 12 f f t f v 2 20 "2205 20" _null_ _null_ _null_  setval_oid
-_null_ )); 
  DESCR("set sequence value");
+ DATA(insert OID = 1577 (  retrieveval                   PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_
retrieveval_oid- _null_ )); 
+ DESCR("sequence current value");
+ DATA(insert OID = 1578 (  retrievecheck                 PGNSP PGUID 12 f f t f v 1 20 "26" _null_ _null_ _null_
retrievecheck_oid- _null_ )); 
+ DESCR("sequence current value check");
+
  DATA(insert OID = 1765 (  setval            PGNSP PGUID 12 f f t f v 3 20 "2205 20 16" _null_ _null_ _null_
setval3_oid- _null_ )); 
  DESCR("set sequence value and iscalled status");

*** ./src/include/commands/sequence.h.orig    Tue May  2 14:52:23 2006
--- ./src/include/commands/sequence.h    Tue May  2 15:00:06 2006
***************
*** 86,91 ****
--- 86,93 ----
  extern Datum setval_oid(PG_FUNCTION_ARGS);
  extern Datum setval3_oid(PG_FUNCTION_ARGS);
  extern Datum lastval(PG_FUNCTION_ARGS);
+ extern Datum retrieveval_oid(PG_FUNCTION_ARGS);
+ extern Datum retrievecheck_oid(PG_FUNCTION_ARGS);

  extern void DefineSequence(CreateSeqStmt *stmt);
  extern void AlterSequence(AlterSeqStmt *stmt);