Re: \crosstabview fixes - Mailing list pgsql-hackers

I wrote:
> "Daniel Verite" <daniel@manitou-mail.org> writes:
>> To avoid the confusion between "2:4" and "2":"4" or 2:4,
>> and the ambiguity with a possibly existing "2:4" column,
>> maybe we should abandon this syntax and require the optional
>> scolH to be on its own at the end of the command.

> That would be OK with me; it's certainly less of a hack than what's
> there now.  (I went back and forth about how much effort to put into
> dealing with the colon syntax; I think the version I have in my patch
> would be all right, but it's not perfect.)

Here's a patch along those lines.  Any objections?

            regards, tom lane

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index b2b2adc..9eeb1ca 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** testdb=>
*** 993,1001 ****
        <varlistentry id="APP-PSQL-meta-commands-crosstabview">
          <term><literal>\crosstabview [
              <replaceable class="parameter">colV</replaceable>
!             <replaceable class="parameter">colH</replaceable>[:<replaceable class="parameter">scolH</replaceable>]
!             [<replaceable class="parameter">colD</replaceable>]
!             ] </literal></term>
          <listitem>
          <para>
          Executes the current query buffer (like <literal>\g</literal>) and
--- 993,1002 ----
        <varlistentry id="APP-PSQL-meta-commands-crosstabview">
          <term><literal>\crosstabview [
              <replaceable class="parameter">colV</replaceable>
!             [ <replaceable class="parameter">colH</replaceable>
!             [ <replaceable class="parameter">colD</replaceable>
!             [ <replaceable class="parameter">scolH</replaceable>
!             ] ] ] ] </literal></term>
          <listitem>
          <para>
          Executes the current query buffer (like <literal>\g</literal>) and
*************** testdb=>
*** 1004,1019 ****
          The output column identified by <replaceable class="parameter">colV</>
          becomes a vertical header and the output column identified by
          <replaceable class="parameter">colH</replaceable>
!         becomes a horizontal header, optionally sorted by ranking data obtained
!         from column <replaceable class="parameter">scolH</replaceable>.
          <replaceable class="parameter">colD</replaceable> identifies
          the output column to display within the grid.
!         If <replaceable class="parameter">colD</replaceable> is not
!         specified and there are exactly three columns in the result set,
!         the column that is neither
!         <replaceable class="parameter">colV</replaceable> nor
!         <replaceable class="parameter">colH</replaceable>
!         is displayed; if there are more columns, an error is reported.
          </para>

          <para>
--- 1005,1015 ----
          The output column identified by <replaceable class="parameter">colV</>
          becomes a vertical header and the output column identified by
          <replaceable class="parameter">colH</replaceable>
!         becomes a horizontal header.
          <replaceable class="parameter">colD</replaceable> identifies
          the output column to display within the grid.
!         <replaceable class="parameter">scolH</replaceable> identifies
!         an optional sort column for the horizontal header.
          </para>

          <para>
*************** testdb=>
*** 1024,1029 ****
--- 1020,1031 ----
          and <replaceable class="parameter">colH</replaceable> as column 2.
          <replaceable class="parameter">colH</replaceable> must differ from
          <replaceable class="parameter">colV</replaceable>.
+         If <replaceable class="parameter">colD</replaceable> is not
+         specified and there are exactly three columns in the result set,
+         the column that is neither
+         <replaceable class="parameter">colV</replaceable> nor
+         <replaceable class="parameter">colH</replaceable>
+         is displayed; if there are more columns, an error is reported.
          </para>

          <para>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 227d180..e1f5805 100644
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*************** exec_command(const char *cmd,
*** 368,380 ****
      /* \crosstabview -- execute a query and display results in crosstab */
      else if (strcmp(cmd, "crosstabview") == 0)
      {
!         pset.ctv_col_V = psql_scan_slash_option(scan_state,
!                                                 OT_NORMAL, NULL, false);
!         pset.ctv_col_H = psql_scan_slash_option(scan_state,
!                                                 OT_NORMAL, NULL, false);
!         pset.ctv_col_D = psql_scan_slash_option(scan_state,
!                                                 OT_NORMAL, NULL, false);

          pset.crosstab_flag = true;
          status = PSQL_CMD_SEND;
      }
--- 368,378 ----
      /* \crosstabview -- execute a query and display results in crosstab */
      else if (strcmp(cmd, "crosstabview") == 0)
      {
!         int            i;

+         for (i = 0; i < lengthof(pset.ctv_args); i++)
+             pset.ctv_args[i] = psql_scan_slash_option(scan_state,
+                                                       OT_SQLID, NULL, true);
          pset.crosstab_flag = true;
          status = PSQL_CMD_SEND;
      }
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index 437cb56..2c0d781 100644
*** a/src/bin/psql/common.c
--- b/src/bin/psql/common.c
*************** SendQuery(const char *query)
*** 1130,1135 ****
--- 1130,1136 ----
      PGTransactionStatusType transaction_status;
      double        elapsed_msec = 0;
      bool        OK = false;
+     int            i;
      bool        on_error_rollback_savepoint = false;
      static bool on_error_rollback_warning = false;

*************** sendquery_cleanup:
*** 1362,1381 ****

      /* reset \crosstabview trigger */
      pset.crosstab_flag = false;
!     if (pset.ctv_col_V)
!     {
!         free(pset.ctv_col_V);
!         pset.ctv_col_V = NULL;
!     }
!     if (pset.ctv_col_H)
!     {
!         free(pset.ctv_col_H);
!         pset.ctv_col_H = NULL;
!     }
!     if (pset.ctv_col_D)
      {
!         free(pset.ctv_col_D);
!         pset.ctv_col_D = NULL;
      }

      return OK;
--- 1363,1372 ----

      /* reset \crosstabview trigger */
      pset.crosstab_flag = false;
!     for (i = 0; i < lengthof(pset.ctv_args); i++)
      {
!         pg_free(pset.ctv_args[i]);
!         pset.ctv_args[i] = NULL;
      }

      return OK;
diff --git a/src/bin/psql/crosstabview.c b/src/bin/psql/crosstabview.c
index 3cc15ed..56cae7a 100644
*** a/src/bin/psql/crosstabview.c
--- b/src/bin/psql/crosstabview.c
*************** static bool printCrosstab(const PGresult
*** 82,90 ****
                int num_columns, pivot_field *piv_columns, int field_for_columns,
                int num_rows, pivot_field *piv_rows, int field_for_rows,
                int field_for_data);
- static int parseColumnRefs(const char *arg, const PGresult *res,
-                 int **col_numbers,
-                 int max_columns, char separator);
  static void avlInit(avl_tree *tree);
  static void avlMergeValue(avl_tree *tree, char *name, char *sort_value);
  static int avlCollectFields(avl_tree *tree, avl_node *node,
--- 82,87 ----
*************** static int    rankCompare(const void *a, co
*** 99,231 ****
  /*
   * Main entry point to this module.
   *
!  * Process the data from *res according the display options in pset (global),
   * to generate the horizontal and vertical headers contents,
   * then call printCrosstab() for the actual output.
   */
  bool
  PrintResultsInCrosstab(const PGresult *res)
  {
!     char       *opt_field_for_rows = pset.ctv_col_V;
!     char       *opt_field_for_columns = pset.ctv_col_H;
!     char       *opt_field_for_data = pset.ctv_col_D;
!     int            rn;
      avl_tree    piv_columns;
      avl_tree    piv_rows;
      pivot_field *array_columns = NULL;
      pivot_field *array_rows = NULL;
      int            num_columns = 0;
      int            num_rows = 0;
-     int           *colsV = NULL,
-                *colsH = NULL,
-                *colsD = NULL;
-     int            n;
-     int            field_for_columns;
-     int            sort_field_for_columns = -1;
      int            field_for_rows;
!     int            field_for_data = -1;
!     bool        retval = false;

      avlInit(&piv_rows);
      avlInit(&piv_columns);

-     if (res == NULL)
-     {
-         psql_error(_("No result\n"));
-         goto error_return;
-     }
-
      if (PQresultStatus(res) != PGRES_TUPLES_OK)
      {
!         psql_error(_("The query must return results to be shown in crosstab\n"));
!         goto error_return;
!     }
!
!     if (opt_field_for_rows && !opt_field_for_columns)
!     {
!         psql_error(_("A second column must be specified for the horizontal header\n"));
          goto error_return;
      }

!     if (PQnfields(res) <= 2)
      {
!         psql_error(_("The query must return at least two columns to be shown in crosstab\n"));
          goto error_return;
      }

!     /*
!      * Arguments processing for the vertical header (1st arg) displayed in the
!      * left-most column. Only a reference to a field is accepted (no sort
!      * column).
!      */
!
!     if (opt_field_for_rows == NULL)
!     {
          field_for_rows = 0;
-     }
      else
      {
!         n = parseColumnRefs(opt_field_for_rows, res, &colsV, 1, ':');
!         if (n != 1)
              goto error_return;
-         field_for_rows = colsV[0];
      }

!     if (field_for_rows < 0)
!         goto error_return;
!
!     /*----------
!      * Arguments processing for the horizontal header (2nd arg)
!      * (pivoted column that gets displayed as the first row).
!      * Determine:
!      * - the field number for the horizontal header column
!      * - the field number of the associated sort column, if any
!      */
!
!     if (opt_field_for_columns == NULL)
          field_for_columns = 1;
      else
      {
!         n = parseColumnRefs(opt_field_for_columns, res, &colsH, 2, ':');
!         if (n <= 0)
!             goto error_return;
!         if (n == 1)
!             field_for_columns = colsH[0];
!         else
!         {
!             field_for_columns = colsH[0];
!             sort_field_for_columns = colsH[1];
!         }
!
          if (field_for_columns < 0)
              goto error_return;
      }

      if (field_for_columns == field_for_rows)
      {
!         psql_error(_("The same column cannot be used for both vertical and horizontal headers\n"));
          goto error_return;
      }

!     /*
!      * Arguments processing for the data columns (3rd arg).  Determine the
!      * column to display in the grid.
!      */
!     if (opt_field_for_data == NULL)
      {
!         int        i;

          /*
           * If the data column was not specified, we search for the one not
!          * used as either vertical or horizontal headers.  If the result has
!          * more than three columns, raise an error.
           */
!         if (PQnfields(res) > 3)
          {
!             psql_error(_("Data column must be specified when the result set has more than three columns\n"));
              goto error_return;
          }

          for (i = 0; i < PQnfields(res); i++)
          {
              if (i != field_for_rows && i != field_for_columns)
--- 96,180 ----
  /*
   * Main entry point to this module.
   *
!  * Process the data from *res according to the options in pset (global),
   * to generate the horizontal and vertical headers contents,
   * then call printCrosstab() for the actual output.
   */
  bool
  PrintResultsInCrosstab(const PGresult *res)
  {
!     bool        retval = false;
      avl_tree    piv_columns;
      avl_tree    piv_rows;
      pivot_field *array_columns = NULL;
      pivot_field *array_rows = NULL;
      int            num_columns = 0;
      int            num_rows = 0;
      int            field_for_rows;
!     int            field_for_columns;
!     int            field_for_data;
!     int            sort_field_for_columns;
!     int            rn;

      avlInit(&piv_rows);
      avlInit(&piv_columns);

      if (PQresultStatus(res) != PGRES_TUPLES_OK)
      {
!         psql_error(_("\\crosstabview: query must return results to be shown in crosstab\n"));
          goto error_return;
      }

!     if (PQnfields(res) < 3)
      {
!         psql_error(_("\\crosstabview: query must return at least three columns\n"));
          goto error_return;
      }

!     /* Process first optional arg (vertical header column) */
!     if (pset.ctv_args[0] == NULL)
          field_for_rows = 0;
      else
      {
!         field_for_rows = indexOfColumn(pset.ctv_args[0], res);
!         if (field_for_rows < 0)
              goto error_return;
      }

!     /* Process second optional arg (horizontal header column) */
!     if (pset.ctv_args[1] == NULL)
          field_for_columns = 1;
      else
      {
!         field_for_columns = indexOfColumn(pset.ctv_args[1], res);
          if (field_for_columns < 0)
              goto error_return;
      }

+     /* Insist that header columns be distinct */
      if (field_for_columns == field_for_rows)
      {
!         psql_error(_("\\crosstabview: vertical and horizontal headers must be different columns\n"));
          goto error_return;
      }

!     /* Process third optional arg (data column) */
!     if (pset.ctv_args[2] == NULL)
      {
!         int            i;

          /*
           * If the data column was not specified, we search for the one not
!          * used as either vertical or horizontal headers.  Must be exactly
!          * three columns, or this won't be unique.
           */
!         if (PQnfields(res) != 3)
          {
!             psql_error(_("\\crosstabview: data column must be specified when query returns more than three
columns\n"));
              goto error_return;
          }

+         field_for_data = -1;
          for (i = 0; i < PQnfields(res); i++)
          {
              if (i != field_for_rows && i != field_for_columns)
*************** PrintResultsInCrosstab(const PGresult *r
*** 238,250 ****
      }
      else
      {
!         int        num_fields;

!         /* If a field was given, find out what it is.  Only one is allowed. */
!         num_fields = parseColumnRefs(opt_field_for_data, res, &colsD, 1, ',');
!         if (num_fields < 1)
              goto error_return;
-         field_for_data = colsD[0];
      }

      /*
--- 187,205 ----
      }
      else
      {
!         field_for_data = indexOfColumn(pset.ctv_args[2], res);
!         if (field_for_data < 0)
!             goto error_return;
!     }

!     /* Process fourth optional arg (horizontal header sort column) */
!     if (pset.ctv_args[3] == NULL)
!         sort_field_for_columns = -1;    /* no sort column */
!     else
!     {
!         sort_field_for_columns = indexOfColumn(pset.ctv_args[3], res);
!         if (sort_field_for_columns < 0)
              goto error_return;
      }

      /*
*************** PrintResultsInCrosstab(const PGresult *r
*** 271,277 ****

          if (piv_columns.count > CROSSTABVIEW_MAX_COLUMNS)
          {
!             psql_error(_("Maximum number of columns (%d) exceeded\n"),
                         CROSSTABVIEW_MAX_COLUMNS);
              goto error_return;
          }
--- 226,232 ----

          if (piv_columns.count > CROSSTABVIEW_MAX_COLUMNS)
          {
!             psql_error(_("\\crosstabview: maximum number of columns (%d) exceeded\n"),
                         CROSSTABVIEW_MAX_COLUMNS);
              goto error_return;
          }
*************** error_return:
*** 319,327 ****
      avlFree(&piv_rows, piv_rows.root);
      pg_free(array_columns);
      pg_free(array_rows);
-     pg_free(colsV);
-     pg_free(colsH);
-     pg_free(colsD);

      return retval;
  }
--- 274,279 ----
*************** printCrosstab(const PGresult *results,
*** 442,448 ****
               */
              if (cont.cells[idx] != NULL)
              {
!                 psql_error(_("data cell already contains a value: (row: \"%s\", column: \"%s\")\n"),
                               piv_rows[row_number].name ? piv_rows[row_number].name :
                               popt.nullPrint ? popt.nullPrint : "(null)",
                               piv_columns[col_number].name ? piv_columns[col_number].name :
--- 394,400 ----
               */
              if (cont.cells[idx] != NULL)
              {
!                 psql_error(_("\\crosstabview: query result contains multiple data values for row \"%s\", column
\"%s\"\n"),
                               piv_rows[row_number].name ? piv_rows[row_number].name :
                               popt.nullPrint ? popt.nullPrint : "(null)",
                               piv_columns[col_number].name ? piv_columns[col_number].name :
*************** error:
*** 476,583 ****
  }

  /*
-  * Parse "arg", which is a string of column IDs separated by "separator".
-  *
-  * Each column ID can be:
-  * - a number from 1 to PQnfields(res)
-  * - an unquoted column name matching (case insensitively) one of PQfname(res,...)
-  * - a quoted column name matching (case sensitively) one of PQfname(res,...)
-  *
-  * If max_columns > 0, it is the max number of column IDs allowed.
-  *
-  * On success, return number of column IDs found (possibly 0), and return a
-  * malloc'd array of the matching column numbers of "res" into *col_numbers.
-  *
-  * On failure, return -1 and set *col_numbers to NULL.
-  */
- static int
- parseColumnRefs(const char *arg,
-                 const PGresult *res,
-                 int **col_numbers,
-                 int max_columns,
-                 char separator)
- {
-     const char *p = arg;
-     char        c;
-     int            num_cols = 0;
-
-     *col_numbers = NULL;
-     while ((c = *p) != '\0')
-     {
-         const char *field_start = p;
-         bool        quoted_field = false;
-
-         /* first char */
-         if (c == '"')
-         {
-             quoted_field = true;
-             p++;
-         }
-
-         while ((c = *p) != '\0')
-         {
-             if (c == separator && !quoted_field)
-                 break;
-             if (c == '"')        /* end of field or embedded double quote */
-             {
-                 p++;
-                 if (*p == '"')
-                 {
-                     if (quoted_field)
-                     {
-                         p++;
-                         continue;
-                     }
-                 }
-                 else if (quoted_field && *p == separator)
-                     break;
-             }
-             if (*p)
-                 p += PQmblen(p, pset.encoding);
-         }
-
-         if (p != field_start)
-         {
-             char   *col_name;
-             int        col_num;
-
-             /* enforce max_columns limit */
-             if (max_columns > 0 && num_cols == max_columns)
-             {
-                 psql_error(_("No more than %d column references expected\n"),
-                            max_columns);
-                 goto errfail;
-             }
-             /* look up the column and add its index into *col_numbers */
-             col_name = pg_malloc(p - field_start + 1);
-             memcpy(col_name, field_start, p - field_start);
-             col_name[p - field_start] = '\0';
-             col_num = indexOfColumn(col_name, res);
-             pg_free(col_name);
-             if (col_num < 0)
-                 goto errfail;
-             *col_numbers = (int *) pg_realloc(*col_numbers,
-                                               (num_cols + 1) * sizeof(int));
-             (*col_numbers)[num_cols++] = col_num;
-         }
-         else
-         {
-             psql_error(_("Empty column reference\n"));
-             goto errfail;
-         }
-
-         if (*p)
-             p += PQmblen(p, pset.encoding);
-     }
-     return num_cols;
-
- errfail:
-     pg_free(*col_numbers);
-     *col_numbers = NULL;
-     return -1;
- }
-
- /*
   * The avl* functions below provide a minimalistic implementation of AVL binary
   * trees, to efficiently collect the distinct values that will form the horizontal
   * and vertical headers. It only supports adding new values, no removal or even
--- 428,433 ----
*************** rankSort(int num_columns, pivot_field *p
*** 773,833 ****
  }

  /*
!  * Compare a user-supplied argument against a field name obtained by PQfname(),
!  * which is already case-folded.
!  * If arg is not enclosed in double quotes, pg_strcasecmp applies, otherwise
!  * do a case-sensitive comparison with these rules:
!  * - double quotes enclosing 'arg' are filtered out
!  * - double quotes inside 'arg' are expected to be doubled
!  */
! static bool
! fieldNameEquals(const char *arg, const char *fieldname)
! {
!     const char *p = arg;
!     const char *f = fieldname;
!     char        c;
!
!     if (*p++ != '"')
!         return (pg_strcasecmp(arg, fieldname) == 0);
!
!     while ((c = *p++))
!     {
!         if (c == '"')
!         {
!             if (*p == '"')
!                 p++;            /* skip second quote and continue */
!             else if (*p == '\0')
!                 return (*f == '\0');    /* p is shorter than f, or is
!                                          * identical */
!         }
!         if (*f == '\0')
!             return false;        /* f is shorter than p */
!         if (c != *f)            /* found one byte that differs */
!             return false;
!         f++;
!     }
!     return (*f == '\0');
! }
!
! /*
!  * arg can be a number or a column name, possibly quoted (like in an ORDER BY clause)
!  * Returns:
!  *    on success, the 0-based index of the column
!  *    or -1 if the column number or name is not found in the result's structure,
!  *          or if it's ambiguous (arg corresponding to several columns)
   */
  static int
  indexOfColumn(const char *arg, const PGresult *res)
  {
      int            idx;

!     if (strspn(arg, "0123456789") == strlen(arg))
      {
          /* if arg contains only digits, it's a column number */
          idx = atoi(arg) - 1;
          if (idx < 0 || idx >= PQnfields(res))
          {
!             psql_error(_("Invalid column number: %s\n"), arg);
              return -1;
          }
      }
--- 623,646 ----
  }

  /*
!  * Look up a column reference, which can be either:
!  * - a number from 1 to PQnfields(res)
!  * - a column name matching one of PQfname(res,...)
!  *
!  * Returns zero-based column number, or -1 if not found or ambiguous.
   */
  static int
  indexOfColumn(const char *arg, const PGresult *res)
  {
      int            idx;

!     if (arg[0] && strspn(arg, "0123456789") == strlen(arg))
      {
          /* if arg contains only digits, it's a column number */
          idx = atoi(arg) - 1;
          if (idx < 0 || idx >= PQnfields(res))
          {
!             psql_error(_("\\crosstabview: invalid column number: \"%s\"\n"), arg);
              return -1;
          }
      }
*************** indexOfColumn(const char *arg, const PGr
*** 838,849 ****
          idx = -1;
          for (i = 0; i < PQnfields(res); i++)
          {
!             if (fieldNameEquals(arg, PQfname(res, i)))
              {
                  if (idx >= 0)
                  {
!                     /* if another idx was already found for the same name */
!                     psql_error(_("Ambiguous column name: %s\n"), arg);
                      return -1;
                  }
                  idx = i;
--- 651,662 ----
          idx = -1;
          for (i = 0; i < PQnfields(res); i++)
          {
!             if (strcmp(arg, PQfname(res, i)) == 0)
              {
                  if (idx >= 0)
                  {
!                     /* another idx was already found for the same name */
!                     psql_error(_("\\crosstabview: ambiguous column name: \"%s\"\n"), arg);
                      return -1;
                  }
                  idx = i;
*************** indexOfColumn(const char *arg, const PGr
*** 851,857 ****
          }
          if (idx == -1)
          {
!             psql_error(_("Invalid column name: %s\n"), arg);
              return -1;
          }
      }
--- 664,670 ----
          }
          if (idx == -1)
          {
!             psql_error(_("\\crosstabview: column name not found: \"%s\"\n"), arg);
              return -1;
          }
      }
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 643ff8c..8cfe9d2 100644
*** a/src/bin/psql/settings.h
--- b/src/bin/psql/settings.h
*************** typedef struct _psqlSettings
*** 94,102 ****
      char       *gset_prefix;    /* one-shot prefix argument for \gset */
      bool        gexec_flag;        /* one-shot flag to execute query's results */
      bool        crosstab_flag;    /* one-shot request to crosstab results */
!     char       *ctv_col_V;        /* \crosstabview 1st argument */
!     char       *ctv_col_H;        /* \crosstabview 2nd argument */
!     char       *ctv_col_D;        /* \crosstabview 3nd argument */

      bool        notty;            /* stdin or stdout is not a tty (as determined
                                   * on startup) */
--- 94,100 ----
      char       *gset_prefix;    /* one-shot prefix argument for \gset */
      bool        gexec_flag;        /* one-shot flag to execute query's results */
      bool        crosstab_flag;    /* one-shot request to crosstab results */
!     char       *ctv_args[4];    /* \crosstabview arguments */

      bool        notty;            /* stdin or stdout is not a tty (as determined
                                   * on startup) */
diff --git a/src/test/regress/expected/psql_crosstab.out b/src/test/regress/expected/psql_crosstab.out
index c87c2fc..c508f87 100644
*** a/src/test/regress/expected/psql_crosstab.out
--- b/src/test/regress/expected/psql_crosstab.out
*************** SELECT v, EXTRACT(year FROM d), count(*)
*** 35,41 ****
  -- ordered months in horizontal header, quoted column name
  SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
   count(*) FROM ctv_data  GROUP BY 1,2,3 ORDER BY 1
!  \crosstabview v "month name":num 4
   v  | Jan | Apr | Jul | Dec
  ----+-----+-----+-----+-----
   v0 |     |     |   2 |   1
--- 35,41 ----
  -- ordered months in horizontal header, quoted column name
  SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
   count(*) FROM ctv_data  GROUP BY 1,2,3 ORDER BY 1
!  \crosstabview v "month name" 4 num
   v  | Jan | Apr | Jul | Dec
  ----+-----+-----+-----+-----
   v0 |     |     |   2 |   1
*************** SELECT EXTRACT(year FROM d) AS year, to_
*** 50,56 ****
    FROM ctv_data
    GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
  ORDER BY month
! \crosstabview "month name" year:year format
   month name |      2014       |      2015
  ------------+-----------------+----------------
   Jan        |                 | sum=3 avg=3.0
--- 50,56 ----
    FROM ctv_data
    GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
  ORDER BY month
! \crosstabview "month name" year format year
   month name |      2014       |      2015
  ------------+-----------------+----------------
   Jan        |                 | sum=3 avg=3.0
*************** SELECT v, h, string_agg(c, E'\n') FROM c
*** 74,80 ****
  -- horizontal ASC order from window function
  SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
  FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
!  \crosstabview v h:r c
   v  | h0  | h1  |  h2  | h4  |
  ----+-----+-----+------+-----+-----
   v0 |     |     |      | qux+| qux
--- 74,80 ----
  -- horizontal ASC order from window function
  SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
  FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
!  \crosstabview v h c r
   v  | h0  | h1  |  h2  | h4  |
  ----+-----+-----+------+-----+-----
   v0 |     |     |      | qux+| qux
*************** FROM ctv_data GROUP BY v, h ORDER BY 1,3
*** 87,93 ****
  -- horizontal DESC order from window function
  SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
  FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
!  \crosstabview v h:r c
   v  |     | h4  |  h2  | h1  | h0
  ----+-----+-----+------+-----+-----
   v0 | qux | qux+|      |     |
--- 87,93 ----
  -- horizontal DESC order from window function
  SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
  FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
!  \crosstabview v h c r
   v  |     | h4  |  h2  | h1  | h0
  ----+-----+-----+------+-----+-----
   v0 | qux | qux+|      |     |
*************** FROM ctv_data GROUP BY v, h ORDER BY 1,3
*** 100,106 ****
  -- horizontal ASC order from window function, NULLs pushed rightmost
  SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
  FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
!  \crosstabview v h:r c
   v  | h0  | h1  |  h2  | h4  |
  ----+-----+-----+------+-----+-----
   v0 |     |     |      | qux+| qux
--- 100,106 ----
  -- horizontal ASC order from window function, NULLs pushed rightmost
  SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
  FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
!  \crosstabview v h c r
   v  | h0  | h1  |  h2  | h4  |
  ----+-----+-----+------+-----+-----
   v0 |     |     |      | qux+| qux
*************** FROM ctv_data GROUP BY v, h ORDER BY 1,3
*** 112,118 ****

  -- only null, no column name, 2 columns: error
  SELECT null,null \crosstabview
! The query must return at least two columns to be shown in crosstab
  -- only null, no column name, 3 columns: works
  SELECT null,null,null \crosstabview
   ?column? |
--- 112,118 ----

  -- only null, no column name, 2 columns: error
  SELECT null,null \crosstabview
! \crosstabview: query must return at least three columns
  -- only null, no column name, 3 columns: works
  SELECT null,null,null \crosstabview
   ?column? |
*************** FROM ctv_data GROUP BY v, h ORDER BY h,v
*** 166,185 ****
  -- error: bad column name
  SELECT v,h,c,i FROM ctv_data
   \crosstabview v h j
! Invalid column name: j
  -- error: bad column number
  SELECT v,h,i,c FROM ctv_data
   \crosstabview 2 1 5
! Invalid column number: 5
  -- error: same H and V columns
  SELECT v,h,i,c FROM ctv_data
   \crosstabview 2 h 4
! The same column cannot be used for both vertical and horizontal headers
  -- error: too many columns
  SELECT a,a,1 FROM generate_series(1,3000) AS a
   \crosstabview
! Maximum number of columns (1600) exceeded
  -- error: only one column
  SELECT 1 \crosstabview
! The query must return at least two columns to be shown in crosstab
  DROP TABLE ctv_data;
--- 166,185 ----
  -- error: bad column name
  SELECT v,h,c,i FROM ctv_data
   \crosstabview v h j
! \crosstabview: column name not found: "j"
  -- error: bad column number
  SELECT v,h,i,c FROM ctv_data
   \crosstabview 2 1 5
! \crosstabview: invalid column number: "5"
  -- error: same H and V columns
  SELECT v,h,i,c FROM ctv_data
   \crosstabview 2 h 4
! \crosstabview: vertical and horizontal headers must be different columns
  -- error: too many columns
  SELECT a,a,1 FROM generate_series(1,3000) AS a
   \crosstabview
! \crosstabview: maximum number of columns (1600) exceeded
  -- error: only one column
  SELECT 1 \crosstabview
! \crosstabview: query must return at least three columns
  DROP TABLE ctv_data;
diff --git a/src/test/regress/sql/psql_crosstab.sql b/src/test/regress/sql/psql_crosstab.sql
index e602676..d47555f 100644
*** a/src/test/regress/sql/psql_crosstab.sql
--- b/src/test/regress/sql/psql_crosstab.sql
*************** SELECT v, EXTRACT(year FROM d), count(*)
*** 23,29 ****
  -- ordered months in horizontal header, quoted column name
  SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
   count(*) FROM ctv_data  GROUP BY 1,2,3 ORDER BY 1
!  \crosstabview v "month name":num 4

  -- ordered months in vertical header, ordered years in horizontal header
  SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS "month name",
--- 23,29 ----
  -- ordered months in horizontal header, quoted column name
  SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
   count(*) FROM ctv_data  GROUP BY 1,2,3 ORDER BY 1
!  \crosstabview v "month name" 4 num

  -- ordered months in vertical header, ordered years in horizontal header
  SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS "month name",
*************** SELECT EXTRACT(year FROM d) AS year, to_
*** 32,38 ****
    FROM ctv_data
    GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
  ORDER BY month
! \crosstabview "month name" year:year format

  -- combine contents vertically into the same cell (V/H duplicates)
  SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3
--- 32,38 ----
    FROM ctv_data
    GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
  ORDER BY month
! \crosstabview "month name" year format year

  -- combine contents vertically into the same cell (V/H duplicates)
  SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3
*************** SELECT v, h, string_agg(c, E'\n') FROM c
*** 41,57 ****
  -- horizontal ASC order from window function
  SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
  FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
!  \crosstabview v h:r c

  -- horizontal DESC order from window function
  SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
  FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
!  \crosstabview v h:r c

  -- horizontal ASC order from window function, NULLs pushed rightmost
  SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
  FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
!  \crosstabview v h:r c

  -- only null, no column name, 2 columns: error
  SELECT null,null \crosstabview
--- 41,57 ----
  -- horizontal ASC order from window function
  SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
  FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
!  \crosstabview v h c r

  -- horizontal DESC order from window function
  SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
  FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
!  \crosstabview v h c r

  -- horizontal ASC order from window function, NULLs pushed rightmost
  SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
  FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
!  \crosstabview v h c r

  -- only null, no column name, 2 columns: error
  SELECT null,null \crosstabview