Обсуждение: Re: [GENERAL] Crosstab Problems
Tom Lane wrote: > Joe Conway <mail@joeconway.com> writes: >> Tom Lane wrote: >>> 1. Treat NULL rowid as a category in its own right. This would conform >>> with the behavior of GROUP BY and DISTINCT, for instance. > >> In any case, the attached changes the behavior to #1 for both flavors of >> crosstab (the original crosstab(text, int) and the usually more useful >> crosstab(text, text)). > >> It is appropriate for 8.3 but not back-patching as it changes behavior >> in a non-backward compatible way and is probably too invasive anyway. > > Um, if the previous code crashed in this case, why would you worry about > being backward-compatible with it? You're effectively changing the > behavior anyway, so you might as well make it do what you've decided is > the right thing. Well, maybe the attached patches better explain what I mean. In the case of the 8.2 patch, a very small code change allows new regression data including NULL rowids to: 1) not crash 2) have no impact otherwise The much bigger 8.3 patch shows that for the very same new regression data, there is a significant impact on the output (i.e. NULL rowids get their own output row as discussed). I'm still leaning toward applying the 8.2 patch for back branches but I'll bow to the general consensus. 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 25 Oct 2007 02:11:06 -0000 *************** *** 355,360 **** --- 355,361 ---- 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 **** --- 470,476 ---- 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) { --- 502,508 ---- 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); --- 532,574 ---- 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) { ! if (rowid) ! values[0] = pstrdup(rowid); ! else ! values[0] = NULL; ! ! /* ! * Check to see if the rowid is the same as that of the last ! * tuple sent -- if so, skip this tuple entirely ! */ ! if (!firstpass && ! (((lastrowid == NULL) && (rowid == NULL)) || ! ((lastrowid != NULL) && ! (rowid != NULL) && ! (strcmp(rowid, lastrowid) == 0)))) ! { ! skip_tuple = true; break; ! } ! } + /* + * If rowid hasn't changed on us, continue building the + * ouput tuple. + */ + if ((rowid && values[0] && (strcmp(rowid, values[0]) == 0)) || + ((rowid == NULL) && (values[0] == NULL))) + { /* ! * 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,584 **** call_cntr = --funcctx->call_cntr; break; } ! ! if (rowid != NULL) ! xpfree(rowid); } xpfree(fctx->lastrowid); - if (values[0] != NULL) { /* --- 591,600 ---- call_cntr = --funcctx->call_cntr; break; } ! xpfree(rowid); } xpfree(fctx->lastrowid); if (values[0] != NULL) { /* *************** *** 586,597 **** * 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); --- 602,614 ---- * calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); lastrowid = fctx->lastrowid = pstrdup(values[0]); MemoryContextSwitchTo(oldcontext); } + else + lastrowid = fctx->lastrowid = NULL; ! if (!skip_tuple) { /* build the tuple */ tuple = BuildTupleFromCStrings(attinmeta, values); *************** *** 625,630 **** --- 642,650 ---- SPI_finish(); SRF_RETURN_DONE(funcctx); } + + /* need to reset this before the next tuple is started */ + skip_tuple = false; } } } *************** *** 856,861 **** --- 876,882 ---- 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); --- 939,958 ---- /* 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 || ! (lastrowid == NULL && rowid != NULL) || ! (lastrowid != NULL && rowid == NULL) || ! (lastrowid != NULL && rowid != 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 (!firstpass) { /* rowid changed, flush the previous output row */ tuple = BuildTupleFromCStrings(attinmeta, values); *************** *** 949,954 **** --- 969,977 ---- 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 */ --- 987,994 ---- } xpfree(lastrowid); ! if (rowid) ! lastrowid = pstrdup(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( ? tablefunc.8.2.diff Index: tablefunc.c =================================================================== RCS file: /cvsroot/pgsql/contrib/tablefunc/tablefunc.c,v retrieving revision 1.45 diff -c -r1.45 tablefunc.c *** tablefunc.c 4 Oct 2006 00:29:46 -0000 1.45 --- tablefunc.c 25 Oct 2007 22:24:40 -0000 *************** *** 535,542 **** * 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)) { --- 535,544 ---- * check to see if the rowid is the same as that of the last * tuple sent -- if so, skip this tuple entirely */ ! if (rowid && i == 0) values[0] = pstrdup(rowid); + else if (i == 0) + break; if ((rowid != NULL) && (strcmp(rowid, values[0]) == 0)) { Index: data/ct.data =================================================================== RCS file: /cvsroot/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 22:24:40 -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: /cvsroot/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:40 -0000 *************** *** 150,155 **** --- 150,160 ---- 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', Index: sql/tablefunc.sql =================================================================== RCS file: /cvsroot/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:24:40 -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(
Joe Conway <mail@joeconway.com> writes: > Well, maybe the attached patches better explain what I mean. > In the case of the 8.2 patch, a very small code change allows new > regression data including NULL rowids to: > 1) not crash > 2) have no impact otherwise > The much bigger 8.3 patch shows that for the very same new regression > data, there is a significant impact on the output (i.e. NULL rowids get > their own output row as discussed). > I'm still leaning toward applying the 8.2 patch for back branches but > I'll bow to the general consensus. I'd vote for the bigger patch all the way back. The smaller patch has nothing to recommend it except being smaller. It replaces the crash with a behavior that will change in 8.3, thus creating a potential portability issue for users of (post-repair) back branches. Why not get it right the first time? 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. * Surely this bit: > xpfree(lastrowid); > ! if (rowid) > ! lastrowid = pstrdup(rowid); > } needs to be: if (rowid) lastrowid = pstrdup(rowid); else lastrowid = NULL; no? (Again the variant pstrdup would save some notation) regards, tom lane PS: I hear things are pretty crazy out your way -- hope the fire's not too close to you.
Tom Lane wrote: > Joe Conway <mail@joeconway.com> writes: >> Well, maybe the attached patches better explain what I mean. > >> In the case of the 8.2 patch, a very small code change allows new >> regression data including NULL rowids to: >> 1) not crash >> 2) have no impact otherwise > >> The much bigger 8.3 patch shows that for the very same new regression >> data, there is a significant impact on the output (i.e. NULL rowids get >> their own output row as discussed). > >> I'm still leaning toward applying the 8.2 patch for back branches but >> I'll bow to the general consensus. > > I'd vote for the bigger patch all the way back. The smaller patch has > nothing to recommend it except being smaller. It replaces the crash > with a behavior that will change in 8.3, thus creating a potential > portability issue for users of (post-repair) back branches. Why not > get it right the first time? OK, I can live with that. > 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. > * Surely this bit: > >> xpfree(lastrowid); >> ! if (rowid) >> ! lastrowid = pstrdup(rowid); >> } > > needs to be: > > if (rowid) > lastrowid = pstrdup(rowid); > else > lastrowid = NULL; > > no? (Again the variant pstrdup would save some notation) Well I had already defined xpfree like this: 8<------------------ #define xpfree(var_) \ do { \ if (var_ != NULL) \ { \ pfree(var_); \ var_ = NULL; \ } \ } while (0) 8<------------------ so lastrowid is already NULL (I sometimes wish this was the default behavior for pfree() itself). But the point about pstrdup variant is well taken, and I guess the xpfree behavior is not obvious, so it deserves at least a comment. > regards, tom lane > > PS: I hear things are pretty crazy out your way -- hope the fire's > not too close to you. We packed and were ready to evacuate two or three times, but never actually had to leave our house, thankfully. The closest the fire ever got was about 4 miles, and at this point I don't think we're in any more direct danger. But I know many people who were not so fortunate :-( Joe
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(
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'' 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( > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Joe, are you nearly ready to apply this? > Yeah, sorry for the delay. By the end of the weekend. Joe
Bruce Momjian wrote: > Joe, are you nearly ready to apply this? > Done (head and backwards to 7.3). Joe