Re: Patch - Have psql show current values

Hi all,

This patch was discussed a few months ago.
I could not complete this patch at that time.
I hope that the current version of my patch is acceptable.

Patch details:
**************
1. Assign a new field called 'Seq Value' for \ds command
2. All the sequence values are '1' initially
3. After executing the query, call AssignSeqValue()
4. This function assigns the respective sequence values back to the
resultset


Please review and comment on this patch.

Thanks
Dhanaraj

Tom Lane wrote:

>Dhanaraj M <Dhanaraj.M@Sun.COM> writes:
>
>
>>However, it was not possible to display the seq. value using this.
>>Hence, I made a small change in the currval() function, so that it
>>retrieves the last_value
>>even if the the value is not cached.
>>
>>
>
>Breaking currval()'s semantics is not an acceptable solution for this.
>
>The best, fully backward compatible solution is for psql to issue
>"SELECT last_value FROM <seq>" queries to get the values.  This might
>be a bit tricky to wedge into the structure of describe.c, but I don't
>see any fundamental reason why it can't be done.
>
>            regards, tom lane
>
>

*** src/bin/psql/describe.c.orig    Mon Aug 21 11:21:56 2006
--- src/bin/psql/describe.c    Thu Aug 24 10:54:59 2006
***************
*** 14,19 ****
--- 14,20 ----
  #include "settings.h"
  #include "print.h"
  #include "variables.h"
+ #include "libpq-int.h"

  #include <ctype.h>

***************
*** 38,43 ****
--- 39,45 ----

  static bool add_tablespace_footer(char relkind, Oid tablespace, char **footers,
                        int *count, PQExpBufferData buf, bool newline);
+ static void AssignSeqValue(PGresult  *resultset);

  /*----------------
   * Handlers for various slash commands displaying some sort of list
***************
*** 1499,1504 ****
--- 1501,1507 ----
      bool        showSeq = strchr(tabtypes, 's') != NULL;
      bool        showSystem = strchr(tabtypes, 'S') != NULL;

+     int64 initialSeqValue = 1;
      PQExpBufferData buf;
      PGresult   *res;
      printQueryOpt myopt = pset.popt;
***************
*** 1521,1526 ****
--- 1524,1533 ----
                        _("table"), _("view"), _("index"), _("sequence"),
                        _("special"), _("Type"), _("Owner"));

+     if ((showSeq) && (!showTables))
+         appendPQExpBuffer(&buf,  ",\n " INT64_FORMAT "  as \"%s\"",
+                         initialSeqValue, _("Seq Value"));
+
      if (showIndexes)
          appendPQExpBuffer(&buf,
                            ",\n c2.relname as \"%s\"",
***************
*** 1587,1592 ****
--- 1594,1602 ----
          myopt.nullPrint = NULL;
          myopt.title = _("List of relations");

+         if ((showSeq) && (!showTables))
+             AssignSeqValue(res);
+
          printQuery(res, &myopt, pset.queryFout, pset.logfile);
      }

***************
*** 1594,1599 ****
--- 1604,1641 ----
      return true;
  }

+
+ /*
+  * \ds
+  *
+  * Assign the respective sequence value.
+  */
+ static void AssignSeqValue(PGresult  *resultset)
+ {
+     int i, rows, nfields;
+     PQExpBufferData buf;
+     PGresult  *seqValue;
+
+     rows = PQntuples(resultset);
+     nfields = PQnfields(resultset);
+
+     /*
+      * Execute the select query to get the sequence value for each sequence separately,
+      * by using the retrieved sequence names from the second field of resultset.
+      * Re-assign the respective sequence values to the last field of resultset.
+      */
+     for(i=0; i<rows; i++)
+     {
+         initPQExpBuffer(&buf);
+         printfPQExpBuffer(&buf, "select last_value from %s", PQgetvalue(resultset, i, 1));
+
+         seqValue = PSQLexec(buf.data, false);
+         termPQExpBuffer(&buf);
+         strcpy(resultset->tuples[i][nfields-1].value, seqValue->tuples[0][0].value);
+         PQclear(seqValue);
+     }
+ }
+

  /*
   * \dD