Re: [GENERAL] Crosstab Problems
| От | Joe Conway |
|---|---|
| Тема | Re: [GENERAL] Crosstab Problems |
| Дата | |
| Msg-id | 47217EF0.1040604@joeconway.com обсуждение исходный текст |
| Ответ на | Re: [GENERAL] Crosstab Problems (Joe Conway <mail@joeconway.com>) |
| Ответы |
Re: [GENERAL] Crosstab Problems
(Bruce Momjian <bruce@momjian.us>)
|
| Список | pgsql-patches |
Joe Conway wrote:
> Tom Lane wrote:
>> A couple of minor thoughts:
>>
>> * You could reduce the ugliness of many of the tests by introducing a
>> variant strcmp function that does the "right" things with NULL inputs.
>> It might also be worth adding a variant pstrdup that takes a NULL.
>
> I had thoughts along those lines -- it would certainly make the code
> more readable. I'll go ahead and do that but it won't be in time for a
> 26 October beta2.
I'm not quite ready to commit this, mostly because I'd like to give the
rest of tablefunc.c the once-over for similar issues related to not
checking for NULL return values from SPI_getvalue(). But it is close
enough if needed for a beta2 tomorrow -- let me know if we plan to
bundle up beta2 and I'll get it in.
Thanks,
Joe
Index: tablefunc.c
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/tablefunc.c,v
retrieving revision 1.47
diff -c -r1.47 tablefunc.c
*** tablefunc.c 3 Mar 2007 19:32:54 -0000 1.47
--- tablefunc.c 26 Oct 2007 05:35:23 -0000
***************
*** 106,111 ****
--- 106,123 ----
} \
} while (0)
+ #define xpstrdup(tgtvar_, srcvar_) \
+ do { \
+ if (srcvar_) \
+ tgtvar_ = pstrdup(srcvar_); \
+ else \
+ tgtvar_ = NULL; \
+ } while (0)
+
+ #define xstreq(tgtvar_, srcvar_) \
+ (((tgtvar_ == NULL) && (srcvar_ == NULL)) || \
+ ((tgtvar_ != NULL) && (srcvar_ != NULL) && (strcmp(tgtvar_, srcvar_) == 0)))
+
/* sign, 10 digits, '\0' */
#define INT32_STRLEN 12
***************
*** 355,360 ****
--- 367,373 ----
crosstab_fctx *fctx;
int i;
int num_categories;
+ bool firstpass = false;
MemoryContext oldcontext;
/* stuff done only on the first call of the function */
***************
*** 469,474 ****
--- 482,488 ----
funcctx->max_calls = proc;
MemoryContextSwitchTo(oldcontext);
+ firstpass = true;
}
/* stuff done on every call of the function */
***************
*** 500,506 ****
HeapTuple tuple;
Datum result;
char **values;
! bool allnulls = true;
while (true)
{
--- 514,520 ----
HeapTuple tuple;
Datum result;
char **values;
! bool skip_tuple = false;
while (true)
{
***************
*** 530,555 ****
rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
/*
! * If this is the first pass through the values for this rowid
! * set it, otherwise make sure it hasn't changed on us. Also
! * check to see if the rowid is the same as that of the last
! * tuple sent -- if so, skip this tuple entirely
*/
if (i == 0)
- values[0] = pstrdup(rowid);
-
- if ((rowid != NULL) && (strcmp(rowid, values[0]) == 0))
{
! if ((lastrowid != NULL) && (strcmp(rowid, lastrowid) == 0))
break;
! else if (allnulls == true)
! allnulls = false;
/*
! * Get the next category item value, which is alway
* attribute number three.
*
! * Be careful to sssign the value to the array index based
* on which category we are presently processing.
*/
values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
--- 544,578 ----
rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
/*
! * If this is the first pass through the values for this
! * rowid, set the first column to rowid
*/
if (i == 0)
{
! xpstrdup(values[0], rowid);
!
! /*
! * Check to see if the rowid is the same as that of the last
! * tuple sent -- if so, skip this tuple entirely
! */
! if (!firstpass && xstreq(lastrowid, rowid))
! {
! skip_tuple = true;
break;
! }
! }
+ /*
+ * If rowid hasn't changed on us, continue building the
+ * ouput tuple.
+ */
+ if (xstreq(rowid, values[0]))
+ {
/*
! * Get the next category item value, which is always
* attribute number three.
*
! * Be careful to assign the value to the array index based
* on which category we are presently processing.
*/
values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
***************
*** 572,597 ****
call_cntr = --funcctx->call_cntr;
break;
}
!
! if (rowid != NULL)
! xpfree(rowid);
}
! xpfree(fctx->lastrowid);
! if (values[0] != NULL)
! {
! /*
! * switch to memory context appropriate for multiple function
! * calls
! */
! oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
! lastrowid = fctx->lastrowid = pstrdup(values[0]);
! MemoryContextSwitchTo(oldcontext);
! }
! if (!allnulls)
{
/* build the tuple */
tuple = BuildTupleFromCStrings(attinmeta, values);
--- 595,616 ----
call_cntr = --funcctx->call_cntr;
break;
}
! xpfree(rowid);
}
! /*
! * switch to memory context appropriate for multiple function
! * calls
! */
! oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
! xpfree(fctx->lastrowid);
! xpstrdup(fctx->lastrowid, values[0]);
! lastrowid = fctx->lastrowid;
! MemoryContextSwitchTo(oldcontext);
! if (!skip_tuple)
{
/* build the tuple */
tuple = BuildTupleFromCStrings(attinmeta, values);
***************
*** 625,630 ****
--- 644,652 ----
SPI_finish();
SRF_RETURN_DONE(funcctx);
}
+
+ /* need to reset this before the next tuple is started */
+ skip_tuple = false;
}
}
}
***************
*** 856,861 ****
--- 878,884 ----
int ncols = spi_tupdesc->natts;
char *rowid;
char *lastrowid = NULL;
+ bool firstpass = true;
int i,
j;
int result_ncols;
***************
*** 918,938 ****
/* get the rowid from the current sql result tuple */
rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
- /* if rowid is null, skip this tuple entirely */
- if (rowid == NULL)
- continue;
-
/*
* if we're on a new output row, grab the column values up to
* column N-2 now
*/
! if ((lastrowid == NULL) || (strcmp(rowid, lastrowid) != 0))
{
/*
* a new row means we need to flush the old one first, unless
* we're on the very first row
*/
! if (lastrowid != NULL)
{
/* rowid changed, flush the previous output row */
tuple = BuildTupleFromCStrings(attinmeta, values);
--- 941,957 ----
/* get the rowid from the current sql result tuple */
rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
/*
* if we're on a new output row, grab the column values up to
* column N-2 now
*/
! if (firstpass || !xstreq(lastrowid, rowid))
{
/*
* a new row means we need to flush the old one first, unless
* we're on the very first row
*/
! if (!firstpass)
{
/* rowid changed, flush the previous output row */
tuple = BuildTupleFromCStrings(attinmeta, values);
***************
*** 949,954 ****
--- 968,976 ----
values[0] = rowid;
for (j = 1; j < ncols - 2; j++)
values[j] = SPI_getvalue(spi_tuple, spi_tupdesc, j + 1);
+
+ /* we're no longer on the first pass */
+ firstpass = false;
}
/* look up the category and fill in the appropriate column */
***************
*** 964,970 ****
}
xpfree(lastrowid);
! lastrowid = pstrdup(rowid);
}
/* flush the last output row */
--- 986,992 ----
}
xpfree(lastrowid);
! xpstrdup(lastrowid, rowid);
}
/* flush the last output row */
Index: data/ct.data
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/data/ct.data,v
retrieving revision 1.1
diff -c -r1.1 ct.data
*** data/ct.data 12 Sep 2002 00:14:40 -0000 1.1
--- data/ct.data 25 Oct 2007 21:45:49 -0000
***************
*** 12,14 ****
--- 12,18 ----
12 group2 test4 att1 val4
13 group2 test4 att2 val5
14 group2 test4 att3 val6
+ 15 group1 \N att1 val9
+ 16 group1 \N att2 val10
+ 17 group1 \N att3 val11
+ 18 group1 \N att4 val12
Index: expected/tablefunc.out
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/expected/tablefunc.out,v
retrieving revision 1.13
diff -c -r1.13 tablefunc.out
*** expected/tablefunc.out 27 Feb 2006 16:09:49 -0000 1.13
--- expected/tablefunc.out 25 Oct 2007 22:24:01 -0000
***************
*** 23,64 ****
----------+------------+------------
test1 | val2 | val3
test2 | val6 | val7
! (2 rows)
SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') ORDER BY 1,2;');
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val2 | val3 |
test2 | val6 | val7 |
! (2 rows)
SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') ORDER BY 1,2;');
row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
test1 | val2 | val3 | |
test2 | val6 | val7 | |
! (2 rows)
SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
row_name | category_1 | category_2
----------+------------+------------
test1 | val1 | val2
test2 | val5 | val6
! (2 rows)
SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val1 | val2 | val3
test2 | val5 | val6 | val7
! (2 rows)
SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
test1 | val1 | val2 | val3 | val4
test2 | val5 | val6 | val7 | val8
! (2 rows)
SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') ORDER BY 1,2;');
row_name | category_1 | category_2
--- 23,70 ----
----------+------------+------------
test1 | val2 | val3
test2 | val6 | val7
! | val10 | val11
! (3 rows)
SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') ORDER BY 1,2;');
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val2 | val3 |
test2 | val6 | val7 |
! | val10 | val11 |
! (3 rows)
SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') ORDER BY 1,2;');
row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
test1 | val2 | val3 | |
test2 | val6 | val7 | |
! | val10 | val11 | |
! (3 rows)
SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
row_name | category_1 | category_2
----------+------------+------------
test1 | val1 | val2
test2 | val5 | val6
! | val9 | val10
! (3 rows)
SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val1 | val2 | val3
test2 | val5 | val6 | val7
! | val9 | val10 | val11
! (3 rows)
SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
test1 | val1 | val2 | val3 | val4
test2 | val5 | val6 | val7 | val8
! | val9 | val10 | val11 | val12
! (3 rows)
SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') ORDER BY 1,2;');
row_name | category_1 | category_2
***************
*** 103,127 ****
(2 rows)
SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid
text,att1 text, att2 text);
! rowid | att1 | att2
! -------+------+------
test1 | val1 | val2
test2 | val5 | val6
! (2 rows)
SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid
text,att1 text, att2 text, att3 text);
! rowid | att1 | att2 | att3
! -------+------+------+------
! test1 | val1 | val2 | val3
! test2 | val5 | val6 | val7
! (2 rows)
SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid
text,att1 text, att2 text, att3 text, att4 text);
! rowid | att1 | att2 | att3 | att4
! -------+------+------+------+------
! test1 | val1 | val2 | val3 | val4
! test2 | val5 | val6 | val7 | val8
! (2 rows)
-- check it works with OUT parameters, too
CREATE FUNCTION crosstab_out(text,
--- 109,136 ----
(2 rows)
SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid
text,att1 text, att2 text);
! rowid | att1 | att2
! -------+------+-------
test1 | val1 | val2
test2 | val5 | val6
! | val9 | val10
! (3 rows)
SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid
text,att1 text, att2 text, att3 text);
! rowid | att1 | att2 | att3
! -------+------+-------+-------
! test1 | val1 | val2 | val3
! test2 | val5 | val6 | val7
! | val9 | val10 | val11
! (3 rows)
SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid
text,att1 text, att2 text, att3 text, att4 text);
! rowid | att1 | att2 | att3 | att4
! -------+------+-------+-------+-------
! test1 | val1 | val2 | val3 | val4
! test2 | val5 | val6 | val7 | val8
! | val9 | val10 | val11 | val12
! (3 rows)
-- check it works with OUT parameters, too
CREATE FUNCTION crosstab_out(text,
***************
*** 130,140 ****
AS '$libdir/tablefunc','crosstab'
LANGUAGE C STABLE STRICT;
SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
! rowid | att1 | att2 | att3
! -------+------+------+------
! test1 | val1 | val2 | val3
! test2 | val5 | val6 | val7
! (2 rows)
--
-- hash based crosstab
--- 139,150 ----
AS '$libdir/tablefunc','crosstab'
LANGUAGE C STABLE STRICT;
SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
! rowid | att1 | att2 | att3
! -------+------+-------+-------
! test1 | val1 | val2 | val3
! test2 | val5 | val6 | val7
! | val9 | val10 | val11
! (3 rows)
--
-- hash based crosstab
***************
*** 150,187 ****
insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
-- return attributes as plain text
SELECT * FROM crosstab(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
! rowid | rowdt | temperature | test_result | test_startdate | volts
! -------+--------------------------+-------------+-------------+----------------+--------
! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | 01 March 2003 | 3.1234
! (2 rows)
-- this time without rowdt
SELECT * FROM crosstab(
'SELECT rowid, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
! rowid | temperature | test_result | test_startdate | volts
! -------+-------------+-------------+----------------+--------
! test1 | 42 | PASS | | 2.6987
! test2 | 53 | FAIL | 01 March 2003 | 3.1234
! (2 rows)
-- convert attributes to specific datatypes
SELECT * FROM crosstab(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
! rowid | rowdt | temperature | test_result | test_startdate | volts
! -------+--------------------------+-------------+-------------+--------------------------+--------
! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234
! (2 rows)
-- source query and category query out of sync
SELECT * FROM crosstab(
--- 160,205 ----
insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
+ -- next group tests for NULL rowids
+ insert into cth values(DEFAULT,NULL,'25 October 2007','temperature','57');
+ insert into cth values(DEFAULT,NULL,'25 October 2007','test_result','PASS');
+ insert into cth values(DEFAULT,NULL,'25 October 2007','test_startdate','24 October 2007');
+ insert into cth values(DEFAULT,NULL,'25 October 2007','volts','1.41234');
-- return attributes as plain text
SELECT * FROM crosstab(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
! rowid | rowdt | temperature | test_result | test_startdate | volts
! -------+--------------------------+-------------+-------------+-----------------+---------
! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | 01 March 2003 | 3.1234
! | Thu Oct 25 00:00:00 2007 | 57 | PASS | 24 October 2007 | 1.41234
! (3 rows)
-- this time without rowdt
SELECT * FROM crosstab(
'SELECT rowid, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
! rowid | temperature | test_result | test_startdate | volts
! -------+-------------+-------------+-----------------+---------
! test1 | 42 | PASS | | 2.6987
! test2 | 53 | FAIL | 01 March 2003 | 3.1234
! | 57 | PASS | 24 October 2007 | 1.41234
! (3 rows)
-- convert attributes to specific datatypes
SELECT * FROM crosstab(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
! rowid | rowdt | temperature | test_result | test_startdate | volts
! -------+--------------------------+-------------+-------------+--------------------------+---------
! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234
! | Thu Oct 25 00:00:00 2007 | 57 | PASS | Wed Oct 24 00:00:00 2007 | 1.41234
! (3 rows)
-- source query and category query out of sync
SELECT * FROM crosstab(
***************
*** 192,198 ****
-------+--------------------------+-------------+-------------+--------------------------
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS |
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003
! (2 rows)
-- if category query generates no rows, get expected error
SELECT * FROM crosstab(
--- 210,217 ----
-------+--------------------------+-------------+-------------+--------------------------
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS |
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003
! | Thu Oct 25 00:00:00 2007 | 57 | PASS | Wed Oct 24 00:00:00 2007
! (3 rows)
-- if category query generates no rows, get expected error
SELECT * FROM crosstab(
***************
*** 235,245 ****
SELECT * FROM crosstab_named(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1');
! rowid | rowdt | temperature | test_result | test_startdate | volts
! -------+--------------------------+-------------+-------------+--------------------------+--------
! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234
! (2 rows)
-- check it works with OUT parameters
CREATE FUNCTION crosstab_out(text, text,
--- 254,265 ----
SELECT * FROM crosstab_named(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1');
! rowid | rowdt | temperature | test_result | test_startdate | volts
! -------+--------------------------+-------------+-------------+--------------------------+---------
! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234
! | Thu Oct 25 00:00:00 2007 | 57 | PASS | Wed Oct 24 00:00:00 2007 | 1.41234
! (3 rows)
-- check it works with OUT parameters
CREATE FUNCTION crosstab_out(text, text,
***************
*** 252,262 ****
SELECT * FROM crosstab_out(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1');
! rowid | rowdt | temperature | test_result | test_startdate | volts
! -------+--------------------------+-------------+-------------+--------------------------+--------
! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234
! (2 rows)
--
-- connectby
--- 272,283 ----
SELECT * FROM crosstab_out(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1');
! rowid | rowdt | temperature | test_result | test_startdate | volts
! -------+--------------------------+-------------+-------------+--------------------------+---------
! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234
! | Thu Oct 25 00:00:00 2007 | 57 | PASS | Wed Oct 24 00:00:00 2007 | 1.41234
! (3 rows)
--
-- connectby
Index: sql/tablefunc.sql
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/sql/tablefunc.sql,v
retrieving revision 1.12
diff -c -r1.12 tablefunc.sql
*** sql/tablefunc.sql 27 Feb 2006 16:09:49 -0000 1.12
--- sql/tablefunc.sql 25 Oct 2007 22:20:09 -0000
***************
*** 61,66 ****
--- 61,71 ----
insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
+ -- next group tests for NULL rowids
+ insert into cth values(DEFAULT,NULL,'25 October 2007','temperature','57');
+ insert into cth values(DEFAULT,NULL,'25 October 2007','test_result','PASS');
+ insert into cth values(DEFAULT,NULL,'25 October 2007','test_startdate','24 October 2007');
+ insert into cth values(DEFAULT,NULL,'25 October 2007','volts','1.41234');
-- return attributes as plain text
SELECT * FROM crosstab(
В списке pgsql-patches по дате отправления: