Re: [GENERAL] Crosstab Problems - Mailing list pgsql-patches

Joe Conway wrote:
> Tom Lane wrote:
>> A couple of minor thoughts:
>>
>> * You could reduce the ugliness of many of the tests by introducing a
>> variant strcmp function that does the "right" things with NULL inputs.
>> It might also be worth adding a variant pstrdup that takes a NULL.
>
> I had thoughts along those lines -- it would certainly make the code
> more readable. I'll go ahead and do that but it won't be in time for a
> 26 October beta2.

I'm not quite ready to commit this, mostly because I'd like to give the
rest of tablefunc.c the once-over for similar issues related to not
checking for NULL return values from SPI_getvalue(). But it is close
enough if needed for a beta2 tomorrow -- let me know if we plan to
bundle up beta2 and I'll get it in.

Thanks,

Joe
Index: tablefunc.c
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/tablefunc.c,v
retrieving revision 1.47
diff -c -r1.47 tablefunc.c
*** tablefunc.c    3 Mar 2007 19:32:54 -0000    1.47
--- tablefunc.c    26 Oct 2007 05:35:23 -0000
***************
*** 106,111 ****
--- 106,123 ----
          } \
      } while (0)

+ #define xpstrdup(tgtvar_, srcvar_) \
+     do { \
+         if (srcvar_) \
+             tgtvar_ = pstrdup(srcvar_); \
+         else \
+             tgtvar_ = NULL; \
+     } while (0)
+
+ #define xstreq(tgtvar_, srcvar_) \
+     (((tgtvar_ == NULL) && (srcvar_ == NULL)) || \
+      ((tgtvar_ != NULL) && (srcvar_ != NULL) && (strcmp(tgtvar_, srcvar_) == 0)))
+
  /* sign, 10 digits, '\0' */
  #define INT32_STRLEN    12

***************
*** 355,360 ****
--- 367,373 ----
      crosstab_fctx *fctx;
      int            i;
      int            num_categories;
+     bool        firstpass = false;
      MemoryContext oldcontext;

      /* stuff done only on the first call of the function */
***************
*** 469,474 ****
--- 482,488 ----
          funcctx->max_calls = proc;

          MemoryContextSwitchTo(oldcontext);
+         firstpass = true;
      }

      /* stuff done on every call of the function */
***************
*** 500,506 ****
          HeapTuple    tuple;
          Datum        result;
          char      **values;
!         bool        allnulls = true;

          while (true)
          {
--- 514,520 ----
          HeapTuple    tuple;
          Datum        result;
          char      **values;
!         bool        skip_tuple = false;

          while (true)
          {
***************
*** 530,555 ****
                  rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);

                  /*
!                  * If this is the first pass through the values for this rowid
!                  * set it, otherwise make sure it hasn't changed on us. Also
!                  * check to see if the rowid is the same as that of the last
!                  * tuple sent -- if so, skip this tuple entirely
                   */
                  if (i == 0)
-                     values[0] = pstrdup(rowid);
-
-                 if ((rowid != NULL) && (strcmp(rowid, values[0]) == 0))
                  {
!                     if ((lastrowid != NULL) && (strcmp(rowid, lastrowid) == 0))
                          break;
!                     else if (allnulls == true)
!                         allnulls = false;

                      /*
!                      * Get the next category item value, which is alway
                       * attribute number three.
                       *
!                      * Be careful to sssign the value to the array index based
                       * on which category we are presently processing.
                       */
                      values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
--- 544,578 ----
                  rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);

                  /*
!                  * If this is the first pass through the values for this
!                  * rowid, set the first column to rowid
                   */
                  if (i == 0)
                  {
!                     xpstrdup(values[0], rowid);
!
!                     /*
!                      * Check to see if the rowid is the same as that of the last
!                      * tuple sent -- if so, skip this tuple entirely
!                      */
!                     if (!firstpass && xstreq(lastrowid, rowid))
!                     {
!                         skip_tuple = true;
                          break;
!                     }
!                 }

+                 /*
+                  * If rowid hasn't changed on us, continue building the
+                  * ouput tuple.
+                  */
+                 if (xstreq(rowid, values[0]))
+                 {
                      /*
!                      * Get the next category item value, which is always
                       * attribute number three.
                       *
!                      * Be careful to assign the value to the array index based
                       * on which category we are presently processing.
                       */
                      values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
***************
*** 572,597 ****
                      call_cntr = --funcctx->call_cntr;
                      break;
                  }
!
!                 if (rowid != NULL)
!                     xpfree(rowid);
              }

!             xpfree(fctx->lastrowid);

!             if (values[0] != NULL)
!             {
!                 /*
!                  * switch to memory context appropriate for multiple function
!                  * calls
!                  */
!                 oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

!                 lastrowid = fctx->lastrowid = pstrdup(values[0]);
!                 MemoryContextSwitchTo(oldcontext);
!             }

!             if (!allnulls)
              {
                  /* build the tuple */
                  tuple = BuildTupleFromCStrings(attinmeta, values);
--- 595,616 ----
                      call_cntr = --funcctx->call_cntr;
                      break;
                  }
!                 xpfree(rowid);
              }

!             /*
!              * switch to memory context appropriate for multiple function
!              * calls
!              */
!             oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

!             xpfree(fctx->lastrowid);
!             xpstrdup(fctx->lastrowid, values[0]);
!             lastrowid = fctx->lastrowid;

!             MemoryContextSwitchTo(oldcontext);

!             if (!skip_tuple)
              {
                  /* build the tuple */
                  tuple = BuildTupleFromCStrings(attinmeta, values);
***************
*** 625,630 ****
--- 644,652 ----
                      SPI_finish();
                      SRF_RETURN_DONE(funcctx);
                  }
+
+                 /* need to reset this before the next tuple is started */
+                 skip_tuple = false;
              }
          }
      }
***************
*** 856,861 ****
--- 878,884 ----
          int            ncols = spi_tupdesc->natts;
          char       *rowid;
          char       *lastrowid = NULL;
+         bool        firstpass = true;
          int            i,
                      j;
          int            result_ncols;
***************
*** 918,938 ****
              /* get the rowid from the current sql result tuple */
              rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);

-             /* if rowid is null, skip this tuple entirely */
-             if (rowid == NULL)
-                 continue;
-
              /*
               * if we're on a new output row, grab the column values up to
               * column N-2 now
               */
!             if ((lastrowid == NULL) || (strcmp(rowid, lastrowid) != 0))
              {
                  /*
                   * a new row means we need to flush the old one first, unless
                   * we're on the very first row
                   */
!                 if (lastrowid != NULL)
                  {
                      /* rowid changed, flush the previous output row */
                      tuple = BuildTupleFromCStrings(attinmeta, values);
--- 941,957 ----
              /* get the rowid from the current sql result tuple */
              rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);

              /*
               * if we're on a new output row, grab the column values up to
               * column N-2 now
               */
!             if (firstpass || !xstreq(lastrowid, rowid))
              {
                  /*
                   * a new row means we need to flush the old one first, unless
                   * we're on the very first row
                   */
!                 if (!firstpass)
                  {
                      /* rowid changed, flush the previous output row */
                      tuple = BuildTupleFromCStrings(attinmeta, values);
***************
*** 949,954 ****
--- 968,976 ----
                  values[0] = rowid;
                  for (j = 1; j < ncols - 2; j++)
                      values[j] = SPI_getvalue(spi_tuple, spi_tupdesc, j + 1);
+
+                 /* we're no longer on the first pass */
+                 firstpass = false;
              }

              /* look up the category and fill in the appropriate column */
***************
*** 964,970 ****
              }

              xpfree(lastrowid);
!             lastrowid = pstrdup(rowid);
          }

          /* flush the last output row */
--- 986,992 ----
              }

              xpfree(lastrowid);
!             xpstrdup(lastrowid, rowid);
          }

          /* flush the last output row */
Index: data/ct.data
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/data/ct.data,v
retrieving revision 1.1
diff -c -r1.1 ct.data
*** data/ct.data    12 Sep 2002 00:14:40 -0000    1.1
--- data/ct.data    25 Oct 2007 21:45:49 -0000
***************
*** 12,14 ****
--- 12,18 ----
  12    group2    test4    att1    val4
  13    group2    test4    att2    val5
  14    group2    test4    att3    val6
+ 15    group1    \N    att1    val9
+ 16    group1    \N    att2    val10
+ 17    group1    \N    att3    val11
+ 18    group1    \N    att4    val12
Index: expected/tablefunc.out
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/expected/tablefunc.out,v
retrieving revision 1.13
diff -c -r1.13 tablefunc.out
*** expected/tablefunc.out    27 Feb 2006 16:09:49 -0000    1.13
--- expected/tablefunc.out    25 Oct 2007 22:24:01 -0000
***************
*** 23,64 ****
  ----------+------------+------------
   test1    | val2       | val3
   test2    | val6       | val7
! (2 rows)

  SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') ORDER BY 1,2;'); 
   row_name | category_1 | category_2 | category_3
  ----------+------------+------------+------------
   test1    | val2       | val3       |
   test2    | val6       | val7       |
! (2 rows)

  SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') ORDER BY 1,2;'); 
   row_name | category_1 | category_2 | category_3 | category_4
  ----------+------------+------------+------------+------------
   test1    | val2       | val3       |            |
   test2    | val6       | val7       |            |
! (2 rows)

  SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
   row_name | category_1 | category_2
  ----------+------------+------------
   test1    | val1       | val2
   test2    | val5       | val6
! (2 rows)

  SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
   row_name | category_1 | category_2 | category_3
  ----------+------------+------------+------------
   test1    | val1       | val2       | val3
   test2    | val5       | val6       | val7
! (2 rows)

  SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
   row_name | category_1 | category_2 | category_3 | category_4
  ----------+------------+------------+------------+------------
   test1    | val1       | val2       | val3       | val4
   test2    | val5       | val6       | val7       | val8
! (2 rows)

  SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') ORDER BY 1,2;'); 
   row_name | category_1 | category_2
--- 23,70 ----
  ----------+------------+------------
   test1    | val2       | val3
   test2    | val6       | val7
!           | val10      | val11
! (3 rows)

  SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') ORDER BY 1,2;'); 
   row_name | category_1 | category_2 | category_3
  ----------+------------+------------+------------
   test1    | val2       | val3       |
   test2    | val6       | val7       |
!           | val10      | val11      |
! (3 rows)

  SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') ORDER BY 1,2;'); 
   row_name | category_1 | category_2 | category_3 | category_4
  ----------+------------+------------+------------+------------
   test1    | val2       | val3       |            |
   test2    | val6       | val7       |            |
!           | val10      | val11      |            |
! (3 rows)

  SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
   row_name | category_1 | category_2
  ----------+------------+------------
   test1    | val1       | val2
   test2    | val5       | val6
!           | val9       | val10
! (3 rows)

  SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
   row_name | category_1 | category_2 | category_3
  ----------+------------+------------+------------
   test1    | val1       | val2       | val3
   test2    | val5       | val6       | val7
!           | val9       | val10      | val11
! (3 rows)

  SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
   row_name | category_1 | category_2 | category_3 | category_4
  ----------+------------+------------+------------+------------
   test1    | val1       | val2       | val3       | val4
   test2    | val5       | val6       | val7       | val8
!           | val9       | val10      | val11      | val12
! (3 rows)

  SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') ORDER BY 1,2;'); 
   row_name | category_1 | category_2
***************
*** 103,127 ****
  (2 rows)

  SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid
text,att1 text, att2 text); 
!  rowid | att1 | att2
! -------+------+------
   test1 | val1 | val2
   test2 | val5 | val6
! (2 rows)

  SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid
text,att1 text, att2 text, att3 text); 
!  rowid | att1 | att2 | att3
! -------+------+------+------
!  test1 | val1 | val2 | val3
!  test2 | val5 | val6 | val7
! (2 rows)

  SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid
text,att1 text, att2 text, att3 text, att4 text); 
!  rowid | att1 | att2 | att3 | att4
! -------+------+------+------+------
!  test1 | val1 | val2 | val3 | val4
!  test2 | val5 | val6 | val7 | val8
! (2 rows)

  -- check it works with OUT parameters, too
  CREATE FUNCTION crosstab_out(text,
--- 109,136 ----
  (2 rows)

  SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid
text,att1 text, att2 text); 
!  rowid | att1 | att2
! -------+------+-------
   test1 | val1 | val2
   test2 | val5 | val6
!        | val9 | val10
! (3 rows)

  SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid
text,att1 text, att2 text, att3 text); 
!  rowid | att1 | att2  | att3
! -------+------+-------+-------
!  test1 | val1 | val2  | val3
!  test2 | val5 | val6  | val7
!        | val9 | val10 | val11
! (3 rows)

  SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid
text,att1 text, att2 text, att3 text, att4 text); 
!  rowid | att1 | att2  | att3  | att4
! -------+------+-------+-------+-------
!  test1 | val1 | val2  | val3  | val4
!  test2 | val5 | val6  | val7  | val8
!        | val9 | val10 | val11 | val12
! (3 rows)

  -- check it works with OUT parameters, too
  CREATE FUNCTION crosstab_out(text,
***************
*** 130,140 ****
  AS '$libdir/tablefunc','crosstab'
  LANGUAGE C STABLE STRICT;
  SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
!  rowid | att1 | att2 | att3
! -------+------+------+------
!  test1 | val1 | val2 | val3
!  test2 | val5 | val6 | val7
! (2 rows)

  --
  -- hash based crosstab
--- 139,150 ----
  AS '$libdir/tablefunc','crosstab'
  LANGUAGE C STABLE STRICT;
  SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
!  rowid | att1 | att2  | att3
! -------+------+-------+-------
!  test1 | val1 | val2  | val3
!  test2 | val5 | val6  | val7
!        | val9 | val10 | val11
! (3 rows)

  --
  -- hash based crosstab
***************
*** 150,187 ****
  insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
  insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
  insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
  -- return attributes as plain text
  SELECT * FROM crosstab(
    'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
    'SELECT DISTINCT attribute FROM cth ORDER BY 1')
  AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
!  rowid |          rowdt           | temperature | test_result | test_startdate | volts
! -------+--------------------------+-------------+-------------+----------------+--------
!  test1 | Sat Mar 01 00:00:00 2003 | 42          | PASS        |                | 2.6987
!  test2 | Sun Mar 02 00:00:00 2003 | 53          | FAIL        | 01 March 2003  | 3.1234
! (2 rows)

  -- this time without rowdt
  SELECT * FROM crosstab(
    'SELECT rowid, attribute, val FROM cth ORDER BY 1',
    'SELECT DISTINCT attribute FROM cth ORDER BY 1')
  AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
!  rowid | temperature | test_result | test_startdate | volts
! -------+-------------+-------------+----------------+--------
!  test1 | 42          | PASS        |                | 2.6987
!  test2 | 53          | FAIL        | 01 March 2003  | 3.1234
! (2 rows)

  -- convert attributes to specific datatypes
  SELECT * FROM crosstab(
    'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
    'SELECT DISTINCT attribute FROM cth ORDER BY 1')
  AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
!  rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
! -------+--------------------------+-------------+-------------+--------------------------+--------
!  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
!  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
! (2 rows)

  -- source query and category query out of sync
  SELECT * FROM crosstab(
--- 160,205 ----
  insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
  insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
  insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
+ -- next group tests for NULL rowids
+ insert into cth values(DEFAULT,NULL,'25 October 2007','temperature','57');
+ insert into cth values(DEFAULT,NULL,'25 October 2007','test_result','PASS');
+ insert into cth values(DEFAULT,NULL,'25 October 2007','test_startdate','24 October 2007');
+ insert into cth values(DEFAULT,NULL,'25 October 2007','volts','1.41234');
  -- return attributes as plain text
  SELECT * FROM crosstab(
    'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
    'SELECT DISTINCT attribute FROM cth ORDER BY 1')
  AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
!  rowid |          rowdt           | temperature | test_result | test_startdate  |  volts
! -------+--------------------------+-------------+-------------+-----------------+---------
!  test1 | Sat Mar 01 00:00:00 2003 | 42          | PASS        |                 | 2.6987
!  test2 | Sun Mar 02 00:00:00 2003 | 53          | FAIL        | 01 March 2003   | 3.1234
!        | Thu Oct 25 00:00:00 2007 | 57          | PASS        | 24 October 2007 | 1.41234
! (3 rows)

  -- this time without rowdt
  SELECT * FROM crosstab(
    'SELECT rowid, attribute, val FROM cth ORDER BY 1',
    'SELECT DISTINCT attribute FROM cth ORDER BY 1')
  AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
!  rowid | temperature | test_result | test_startdate  |  volts
! -------+-------------+-------------+-----------------+---------
!  test1 | 42          | PASS        |                 | 2.6987
!  test2 | 53          | FAIL        | 01 March 2003   | 3.1234
!        | 57          | PASS        | 24 October 2007 | 1.41234
! (3 rows)

  -- convert attributes to specific datatypes
  SELECT * FROM crosstab(
    'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
    'SELECT DISTINCT attribute FROM cth ORDER BY 1')
  AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
!  rowid |          rowdt           | temperature | test_result |      test_startdate      |  volts
! -------+--------------------------+-------------+-------------+--------------------------+---------
!  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          |  2.6987
!  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 |  3.1234
!        | Thu Oct 25 00:00:00 2007 |          57 | PASS        | Wed Oct 24 00:00:00 2007 | 1.41234
! (3 rows)

  -- source query and category query out of sync
  SELECT * FROM crosstab(
***************
*** 192,198 ****
  -------+--------------------------+-------------+-------------+--------------------------
   test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |
   test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003
! (2 rows)

  -- if category query generates no rows, get expected error
  SELECT * FROM crosstab(
--- 210,217 ----
  -------+--------------------------+-------------+-------------+--------------------------
   test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |
   test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003
!        | Thu Oct 25 00:00:00 2007 |          57 | PASS        | Wed Oct 24 00:00:00 2007
! (3 rows)

  -- if category query generates no rows, get expected error
  SELECT * FROM crosstab(
***************
*** 235,245 ****
  SELECT * FROM crosstab_named(
    'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
    'SELECT DISTINCT attribute FROM cth ORDER BY 1');
!  rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
! -------+--------------------------+-------------+-------------+--------------------------+--------
!  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
!  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
! (2 rows)

  -- check it works with OUT parameters
  CREATE FUNCTION crosstab_out(text, text,
--- 254,265 ----
  SELECT * FROM crosstab_named(
    'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
    'SELECT DISTINCT attribute FROM cth ORDER BY 1');
!  rowid |          rowdt           | temperature | test_result |      test_startdate      |  volts
! -------+--------------------------+-------------+-------------+--------------------------+---------
!  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          |  2.6987
!  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 |  3.1234
!        | Thu Oct 25 00:00:00 2007 |          57 | PASS        | Wed Oct 24 00:00:00 2007 | 1.41234
! (3 rows)

  -- check it works with OUT parameters
  CREATE FUNCTION crosstab_out(text, text,
***************
*** 252,262 ****
  SELECT * FROM crosstab_out(
    'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
    'SELECT DISTINCT attribute FROM cth ORDER BY 1');
!  rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
! -------+--------------------------+-------------+-------------+--------------------------+--------
!  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
!  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
! (2 rows)

  --
  -- connectby
--- 272,283 ----
  SELECT * FROM crosstab_out(
    'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
    'SELECT DISTINCT attribute FROM cth ORDER BY 1');
!  rowid |          rowdt           | temperature | test_result |      test_startdate      |  volts
! -------+--------------------------+-------------+-------------+--------------------------+---------
!  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          |  2.6987
!  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 |  3.1234
!        | Thu Oct 25 00:00:00 2007 |          57 | PASS        | Wed Oct 24 00:00:00 2007 | 1.41234
! (3 rows)

  --
  -- connectby
Index: sql/tablefunc.sql
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/sql/tablefunc.sql,v
retrieving revision 1.12
diff -c -r1.12 tablefunc.sql
*** sql/tablefunc.sql    27 Feb 2006 16:09:49 -0000    1.12
--- sql/tablefunc.sql    25 Oct 2007 22:20:09 -0000
***************
*** 61,66 ****
--- 61,71 ----
  insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
  insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
  insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
+ -- next group tests for NULL rowids
+ insert into cth values(DEFAULT,NULL,'25 October 2007','temperature','57');
+ insert into cth values(DEFAULT,NULL,'25 October 2007','test_result','PASS');
+ insert into cth values(DEFAULT,NULL,'25 October 2007','test_startdate','24 October 2007');
+ insert into cth values(DEFAULT,NULL,'25 October 2007','volts','1.41234');

  -- return attributes as plain text
  SELECT * FROM crosstab(