Re: Have psql show current sequnce values - (Resubmission) - Mailing list pgsql-patches

Sorry for resubmitting this patch.
Just now I found a problem.
Instead of assigning initial sequence value to 1,
I assign LLONG_MAX to avoid the buffer overflow problem.
Please find the current version here.


Dhanaraj M wrote:

> 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 17:18:21 2006
***************
*** 14,20 ****
--- 14,22 ----
  #include "settings.h"
  #include "print.h"
  #include "variables.h"
+ #include "libpq-int.h"

+ #include <limits.h>
  #include <ctype.h>

  #ifdef WIN32
***************
*** 38,43 ****
--- 40,46 ----

  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 ****
--- 1502,1508 ----
      bool        showSeq = strchr(tabtypes, 's') != NULL;
      bool        showSystem = strchr(tabtypes, 'S') != NULL;

+     int64 initialSeqValue = LLONG_MAX;
      PQExpBufferData buf;
      PGresult   *res;
      printQueryOpt myopt = pset.popt;
***************
*** 1521,1526 ****
--- 1525,1534 ----
                        _("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 ****
--- 1595,1603 ----
          myopt.nullPrint = NULL;
          myopt.title = _("List of relations");

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

***************
*** 1594,1599 ****
--- 1605,1642 ----
      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