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);
+ }
***************
*** 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:19:31 2006
***************
*** 1479,1484 ****
--- 1479,1489 ----
_("Schema"), _("Name"),
_("table"), _("view"), _("index"), _("sequence"),
_("special"), _("Type"), _("Owner"));
+ if (showSeq && !showTables)
+ appendPQExpBuffer(&buf,
+ ",\n curval(c.oid) as \"%s\""
+ ",\n CASE curvalcheck(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 curent 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 curent 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);
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings