Re: [GENERAL] Crosstab Problems

Joe, are you nearly ready to apply this?

---------------------------------------------------------------------------

Joe Conway wrote:
> 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

[ text/x-patch is unsupported, treating like TEXT/PLAIN ]

> 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'' or attribute = ''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'' or attribute = ''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'' or attribute = ''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'' or attribute = ''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'' or attribute = ''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'' or attribute = ''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(

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +