Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] Crosstab Problems

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Jorge Godoy <jgodoy(at)gmail(dot)com>, scott(dot)marlowe(at)gmail(dot)com, Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch>, regmeplease(at)gmail(dot)com, "Patches (PostgreSQL)" <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [GENERAL] Crosstab Problems
Date: 2007-11-09 21:20:17
Message-ID: 200711092120.lA9LKHD28819@momjian.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-patches
Joe, are you nearly ready to apply this?

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

Joe Conway wrote:
> Joe Conway wrote:
> > Tom Lane wrote:
> >> A couple of minor thoughts:
> >>
> >> * You could reduce the ugliness of many of the tests by introducing a
> >> variant strcmp function that does the "right" things with NULL inputs.
> >> It might also be worth adding a variant pstrdup that takes a NULL.
> > 
> > I had thoughts along those lines -- it would certainly make the code 
> > more readable. I'll go ahead and do that but it won't be in time for a 
> > 26 October beta2.
> 
> I'm not quite ready to commit this, mostly because I'd like to give the 
> rest of tablefunc.c the once-over for similar issues related to not 
> checking for NULL return values from SPI_getvalue(). But it is close 
> enough if needed for a beta2 tomorrow -- let me know if we plan to 
> bundle up beta2 and I'll get it in.
> 
> Thanks,
> 
> Joe

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

> Index: tablefunc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/tablefunc.c,v
> retrieving revision 1.47
> diff -c -r1.47 tablefunc.c
> *** tablefunc.c	3 Mar 2007 19:32:54 -0000	1.47
> --- tablefunc.c	26 Oct 2007 05:35:23 -0000
> ***************
> *** 106,111 ****
> --- 106,123 ----
>   		} \
>   	} while (0)
>   
> + #define xpstrdup(tgtvar_, srcvar_) \
> + 	do { \
> + 		if (srcvar_) \
> + 			tgtvar_ = pstrdup(srcvar_); \
> + 		else \
> + 			tgtvar_ = NULL; \
> + 	} while (0)
> + 
> + #define xstreq(tgtvar_, srcvar_) \
> + 	(((tgtvar_ == NULL) && (srcvar_ == NULL)) || \
> + 	 ((tgtvar_ != NULL) && (srcvar_ != NULL) && (strcmp(tgtvar_, srcvar_) == 0)))
> + 
>   /* sign, 10 digits, '\0' */
>   #define INT32_STRLEN	12
>   
> ***************
> *** 355,360 ****
> --- 367,373 ----
>   	crosstab_fctx *fctx;
>   	int			i;
>   	int			num_categories;
> + 	bool		firstpass = false;
>   	MemoryContext oldcontext;
>   
>   	/* stuff done only on the first call of the function */
> ***************
> *** 469,474 ****
> --- 482,488 ----
>   		funcctx->max_calls = proc;
>   
>   		MemoryContextSwitchTo(oldcontext);
> + 		firstpass = true;
>   	}
>   
>   	/* stuff done on every call of the function */
> ***************
> *** 500,506 ****
>   		HeapTuple	tuple;
>   		Datum		result;
>   		char	  **values;
> ! 		bool		allnulls = true;
>   
>   		while (true)
>   		{
> --- 514,520 ----
>   		HeapTuple	tuple;
>   		Datum		result;
>   		char	  **values;
> ! 		bool		skip_tuple = false;
>   
>   		while (true)
>   		{
> ***************
> *** 530,555 ****
>   				rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
>   
>   				/*
> ! 				 * If this is the first pass through the values for this rowid
> ! 				 * set it, otherwise make sure it hasn't changed on us. Also
> ! 				 * check to see if the rowid is the same as that of the last
> ! 				 * tuple sent -- if so, skip this tuple entirely
>   				 */
>   				if (i == 0)
> - 					values[0] = pstrdup(rowid);
> - 
> - 				if ((rowid != NULL) && (strcmp(rowid, values[0]) == 0))
>   				{
> ! 					if ((lastrowid != NULL) && (strcmp(rowid, lastrowid) == 0))
>   						break;
> ! 					else if (allnulls == true)
> ! 						allnulls = false;
>   
>   					/*
> ! 					 * Get the next category item value, which is alway
>   					 * attribute number three.
>   					 *
> ! 					 * Be careful to sssign the value to the array index based
>   					 * on which category we are presently processing.
>   					 */
>   					values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
> --- 544,578 ----
>   				rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
>   
>   				/*
> ! 				 * If this is the first pass through the values for this
> ! 				 * rowid, set the first column to rowid
>   				 */
>   				if (i == 0)
>   				{
> ! 					xpstrdup(values[0], rowid);
> ! 
> ! 					/*
> ! 					 * Check to see if the rowid is the same as that of the last
> ! 					 * tuple sent -- if so, skip this tuple entirely
> ! 					 */
> ! 					if (!firstpass && xstreq(lastrowid, rowid))
> ! 					{
> ! 						skip_tuple = true;
>   						break;
> ! 					}
> ! 				}
>   
> + 				/*
> + 				 * If rowid hasn't changed on us, continue building the
> + 				 * ouput tuple.
> + 				 */
> + 				if (xstreq(rowid, values[0]))
> + 				{
>   					/*
> ! 					 * Get the next category item value, which is always
>   					 * attribute number three.
>   					 *
> ! 					 * Be careful to assign the value to the array index based
>   					 * on which category we are presently processing.
>   					 */
>   					values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
> ***************
> *** 572,597 ****
>   					call_cntr = --funcctx->call_cntr;
>   					break;
>   				}
> ! 
> ! 				if (rowid != NULL)
> ! 					xpfree(rowid);
>   			}
>   
> ! 			xpfree(fctx->lastrowid);
>   
> ! 			if (values[0] != NULL)
> ! 			{
> ! 				/*
> ! 				 * switch to memory context appropriate for multiple function
> ! 				 * calls
> ! 				 */
> ! 				oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
>   
> ! 				lastrowid = fctx->lastrowid = pstrdup(values[0]);
> ! 				MemoryContextSwitchTo(oldcontext);
> ! 			}
>   
> ! 			if (!allnulls)
>   			{
>   				/* build the tuple */
>   				tuple = BuildTupleFromCStrings(attinmeta, values);
> --- 595,616 ----
>   					call_cntr = --funcctx->call_cntr;
>   					break;
>   				}
> ! 				xpfree(rowid);
>   			}
>   
> ! 			/*
> ! 			 * switch to memory context appropriate for multiple function
> ! 			 * calls
> ! 			 */
> ! 			oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
>   
> ! 			xpfree(fctx->lastrowid);
> ! 			xpstrdup(fctx->lastrowid, values[0]);
> ! 			lastrowid = fctx->lastrowid;
>   
> ! 			MemoryContextSwitchTo(oldcontext);
>   
> ! 			if (!skip_tuple)
>   			{
>   				/* build the tuple */
>   				tuple = BuildTupleFromCStrings(attinmeta, values);
> ***************
> *** 625,630 ****
> --- 644,652 ----
>   					SPI_finish();
>   					SRF_RETURN_DONE(funcctx);
>   				}
> + 
> + 				/* need to reset this before the next tuple is started */
> + 				skip_tuple = false;
>   			}
>   		}
>   	}
> ***************
> *** 856,861 ****
> --- 878,884 ----
>   		int			ncols = spi_tupdesc->natts;
>   		char	   *rowid;
>   		char	   *lastrowid = NULL;
> + 		bool		firstpass = true;
>   		int			i,
>   					j;
>   		int			result_ncols;
> ***************
> *** 918,938 ****
>   			/* get the rowid from the current sql result tuple */
>   			rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
>   
> - 			/* if rowid is null, skip this tuple entirely */
> - 			if (rowid == NULL)
> - 				continue;
> - 
>   			/*
>   			 * if we're on a new output row, grab the column values up to
>   			 * column N-2 now
>   			 */
> ! 			if ((lastrowid == NULL) || (strcmp(rowid, lastrowid) != 0))
>   			{
>   				/*
>   				 * a new row means we need to flush the old one first, unless
>   				 * we're on the very first row
>   				 */
> ! 				if (lastrowid != NULL)
>   				{
>   					/* rowid changed, flush the previous output row */
>   					tuple = BuildTupleFromCStrings(attinmeta, values);
> --- 941,957 ----
>   			/* get the rowid from the current sql result tuple */
>   			rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
>   
>   			/*
>   			 * if we're on a new output row, grab the column values up to
>   			 * column N-2 now
>   			 */
> ! 			if (firstpass || !xstreq(lastrowid, rowid))
>   			{
>   				/*
>   				 * a new row means we need to flush the old one first, unless
>   				 * we're on the very first row
>   				 */
> ! 				if (!firstpass)
>   				{
>   					/* rowid changed, flush the previous output row */
>   					tuple = BuildTupleFromCStrings(attinmeta, values);
> ***************
> *** 949,954 ****
> --- 968,976 ----
>   				values[0] = rowid;
>   				for (j = 1; j < ncols - 2; j++)
>   					values[j] = SPI_getvalue(spi_tuple, spi_tupdesc, j + 1);
> + 
> + 				/* we're no longer on the first pass */
> + 				firstpass = false;
>   			}
>   
>   			/* look up the category and fill in the appropriate column */
> ***************
> *** 964,970 ****
>   			}
>   
>   			xpfree(lastrowid);
> ! 			lastrowid = pstrdup(rowid);
>   		}
>   
>   		/* flush the last output row */
> --- 986,992 ----
>   			}
>   
>   			xpfree(lastrowid);
> ! 			xpstrdup(lastrowid, rowid);
>   		}
>   
>   		/* flush the last output row */
> Index: data/ct.data
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/data/ct.data,v
> retrieving revision 1.1
> diff -c -r1.1 ct.data
> *** data/ct.data	12 Sep 2002 00:14:40 -0000	1.1
> --- data/ct.data	25 Oct 2007 21:45:49 -0000
> ***************
> *** 12,14 ****
> --- 12,18 ----
>   12	group2	test4	att1	val4
>   13	group2	test4	att2	val5
>   14	group2	test4	att3	val6
> + 15	group1	\N	att1	val9
> + 16	group1	\N	att2	val10
> + 17	group1	\N	att3	val11
> + 18	group1	\N	att4	val12
> Index: expected/tablefunc.out
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/expected/tablefunc.out,v
> retrieving revision 1.13
> diff -c -r1.13 tablefunc.out
> *** expected/tablefunc.out	27 Feb 2006 16:09:49 -0000	1.13
> --- expected/tablefunc.out	25 Oct 2007 22:24:01 -0000
> ***************
> *** 23,64 ****
>   ----------+------------+------------
>    test1    | val2       | val3
>    test2    | val6       | val7
> ! (2 rows)
>   
>   SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
>    row_name | category_1 | category_2 | category_3 
>   ----------+------------+------------+------------
>    test1    | val2       | val3       | 
>    test2    | val6       | val7       | 
> ! (2 rows)
>   
>   SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
>    row_name | category_1 | category_2 | category_3 | category_4 
>   ----------+------------+------------+------------+------------
>    test1    | val2       | val3       |            | 
>    test2    | val6       | val7       |            | 
> ! (2 rows)
>   
>   SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
>    row_name | category_1 | category_2 
>   ----------+------------+------------
>    test1    | val1       | val2
>    test2    | val5       | val6
> ! (2 rows)
>   
>   SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
>    row_name | category_1 | category_2 | category_3 
>   ----------+------------+------------+------------
>    test1    | val1       | val2       | val3
>    test2    | val5       | val6       | val7
> ! (2 rows)
>   
>   SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
>    row_name | category_1 | category_2 | category_3 | category_4 
>   ----------+------------+------------+------------+------------
>    test1    | val1       | val2       | val3       | val4
>    test2    | val5       | val6       | val7       | val8
> ! (2 rows)
>   
>   SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
>    row_name | category_1 | category_2 
> --- 23,70 ----
>   ----------+------------+------------
>    test1    | val2       | val3
>    test2    | val6       | val7
> !           | val10      | val11
> ! (3 rows)
>   
>   SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
>    row_name | category_1 | category_2 | category_3 
>   ----------+------------+------------+------------
>    test1    | val2       | val3       | 
>    test2    | val6       | val7       | 
> !           | val10      | val11      | 
> ! (3 rows)
>   
>   SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
>    row_name | category_1 | category_2 | category_3 | category_4 
>   ----------+------------+------------+------------+------------
>    test1    | val2       | val3       |            | 
>    test2    | val6       | val7       |            | 
> !           | val10      | val11      |            | 
> ! (3 rows)
>   
>   SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
>    row_name | category_1 | category_2 
>   ----------+------------+------------
>    test1    | val1       | val2
>    test2    | val5       | val6
> !           | val9       | val10
> ! (3 rows)
>   
>   SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
>    row_name | category_1 | category_2 | category_3 
>   ----------+------------+------------+------------
>    test1    | val1       | val2       | val3
>    test2    | val5       | val6       | val7
> !           | val9       | val10      | val11
> ! (3 rows)
>   
>   SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
>    row_name | category_1 | category_2 | category_3 | category_4 
>   ----------+------------+------------+------------+------------
>    test1    | val1       | val2       | val3       | val4
>    test2    | val5       | val6       | val7       | val8
> !           | val9       | val10      | val11      | val12
> ! (3 rows)
>   
>   SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
>    row_name | category_1 | category_2 
> ***************
> *** 103,127 ****
>   (2 rows)
>   
>   SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text);
> !  rowid | att1 | att2 
> ! -------+------+------
>    test1 | val1 | val2
>    test2 | val5 | val6
> ! (2 rows)
>   
>   SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text);
> !  rowid | att1 | att2 | att3 
> ! -------+------+------+------
> !  test1 | val1 | val2 | val3
> !  test2 | val5 | val6 | val7
> ! (2 rows)
>   
>   SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text, att4 text);
> !  rowid | att1 | att2 | att3 | att4 
> ! -------+------+------+------+------
> !  test1 | val1 | val2 | val3 | val4
> !  test2 | val5 | val6 | val7 | val8
> ! (2 rows)
>   
>   -- check it works with OUT parameters, too
>   CREATE FUNCTION crosstab_out(text,
> --- 109,136 ----
>   (2 rows)
>   
>   SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text);
> !  rowid | att1 | att2  
> ! -------+------+-------
>    test1 | val1 | val2
>    test2 | val5 | val6
> !        | val9 | val10
> ! (3 rows)
>   
>   SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text);
> !  rowid | att1 | att2  | att3  
> ! -------+------+-------+-------
> !  test1 | val1 | val2  | val3
> !  test2 | val5 | val6  | val7
> !        | val9 | val10 | val11
> ! (3 rows)
>   
>   SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text, att4 text);
> !  rowid | att1 | att2  | att3  | att4  
> ! -------+------+-------+-------+-------
> !  test1 | val1 | val2  | val3  | val4
> !  test2 | val5 | val6  | val7  | val8
> !        | val9 | val10 | val11 | val12
> ! (3 rows)
>   
>   -- check it works with OUT parameters, too
>   CREATE FUNCTION crosstab_out(text,
> ***************
> *** 130,140 ****
>   AS '$libdir/tablefunc','crosstab'
>   LANGUAGE C STABLE STRICT;
>   SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
> !  rowid | att1 | att2 | att3 
> ! -------+------+------+------
> !  test1 | val1 | val2 | val3
> !  test2 | val5 | val6 | val7
> ! (2 rows)
>   
>   --
>   -- hash based crosstab
> --- 139,150 ----
>   AS '$libdir/tablefunc','crosstab'
>   LANGUAGE C STABLE STRICT;
>   SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
> !  rowid | att1 | att2  | att3  
> ! -------+------+-------+-------
> !  test1 | val1 | val2  | val3
> !  test2 | val5 | val6  | val7
> !        | val9 | val10 | val11
> ! (3 rows)
>   
>   --
>   -- hash based crosstab
> ***************
> *** 150,187 ****
>   insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
>   insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
>   insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
>   -- return attributes as plain text
>   SELECT * FROM crosstab(
>     'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>     'SELECT DISTINCT attribute FROM cth ORDER BY 1')
>   AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
> !  rowid |          rowdt           | temperature | test_result | test_startdate | volts  
> ! -------+--------------------------+-------------+-------------+----------------+--------
> !  test1 | Sat Mar 01 00:00:00 2003 | 42          | PASS        |                | 2.6987
> !  test2 | Sun Mar 02 00:00:00 2003 | 53          | FAIL        | 01 March 2003  | 3.1234
> ! (2 rows)
>   
>   -- this time without rowdt
>   SELECT * FROM crosstab(
>     'SELECT rowid, attribute, val FROM cth ORDER BY 1',
>     'SELECT DISTINCT attribute FROM cth ORDER BY 1')
>   AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
> !  rowid | temperature | test_result | test_startdate | volts  
> ! -------+-------------+-------------+----------------+--------
> !  test1 | 42          | PASS        |                | 2.6987
> !  test2 | 53          | FAIL        | 01 March 2003  | 3.1234
> ! (2 rows)
>   
>   -- convert attributes to specific datatypes
>   SELECT * FROM crosstab(
>     'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>     'SELECT DISTINCT attribute FROM cth ORDER BY 1')
>   AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
> !  rowid |          rowdt           | temperature | test_result |      test_startdate      | volts  
> ! -------+--------------------------+-------------+-------------+--------------------------+--------
> !  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
> !  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
> ! (2 rows)
>   
>   -- source query and category query out of sync
>   SELECT * FROM crosstab(
> --- 160,205 ----
>   insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
>   insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
>   insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
> + -- next group tests for NULL rowids
> + insert into cth values(DEFAULT,NULL,'25 October 2007','temperature','57');
> + insert into cth values(DEFAULT,NULL,'25 October 2007','test_result','PASS');
> + insert into cth values(DEFAULT,NULL,'25 October 2007','test_startdate','24 October 2007');
> + insert into cth values(DEFAULT,NULL,'25 October 2007','volts','1.41234');
>   -- return attributes as plain text
>   SELECT * FROM crosstab(
>     'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>     'SELECT DISTINCT attribute FROM cth ORDER BY 1')
>   AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
> !  rowid |          rowdt           | temperature | test_result | test_startdate  |  volts  
> ! -------+--------------------------+-------------+-------------+-----------------+---------
> !  test1 | Sat Mar 01 00:00:00 2003 | 42          | PASS        |                 | 2.6987
> !  test2 | Sun Mar 02 00:00:00 2003 | 53          | FAIL        | 01 March 2003   | 3.1234
> !        | Thu Oct 25 00:00:00 2007 | 57          | PASS        | 24 October 2007 | 1.41234
> ! (3 rows)
>   
>   -- this time without rowdt
>   SELECT * FROM crosstab(
>     'SELECT rowid, attribute, val FROM cth ORDER BY 1',
>     'SELECT DISTINCT attribute FROM cth ORDER BY 1')
>   AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
> !  rowid | temperature | test_result | test_startdate  |  volts  
> ! -------+-------------+-------------+-----------------+---------
> !  test1 | 42          | PASS        |                 | 2.6987
> !  test2 | 53          | FAIL        | 01 March 2003   | 3.1234
> !        | 57          | PASS        | 24 October 2007 | 1.41234
> ! (3 rows)
>   
>   -- convert attributes to specific datatypes
>   SELECT * FROM crosstab(
>     'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>     'SELECT DISTINCT attribute FROM cth ORDER BY 1')
>   AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
> !  rowid |          rowdt           | temperature | test_result |      test_startdate      |  volts  
> ! -------+--------------------------+-------------+-------------+--------------------------+---------
> !  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          |  2.6987
> !  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 |  3.1234
> !        | Thu Oct 25 00:00:00 2007 |          57 | PASS        | Wed Oct 24 00:00:00 2007 | 1.41234
> ! (3 rows)
>   
>   -- source query and category query out of sync
>   SELECT * FROM crosstab(
> ***************
> *** 192,198 ****
>   -------+--------------------------+-------------+-------------+--------------------------
>    test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        | 
>    test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003
> ! (2 rows)
>   
>   -- if category query generates no rows, get expected error
>   SELECT * FROM crosstab(
> --- 210,217 ----
>   -------+--------------------------+-------------+-------------+--------------------------
>    test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        | 
>    test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003
> !        | Thu Oct 25 00:00:00 2007 |          57 | PASS        | Wed Oct 24 00:00:00 2007
> ! (3 rows)
>   
>   -- if category query generates no rows, get expected error
>   SELECT * FROM crosstab(
> ***************
> *** 235,245 ****
>   SELECT * FROM crosstab_named(
>     'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>     'SELECT DISTINCT attribute FROM cth ORDER BY 1');
> !  rowid |          rowdt           | temperature | test_result |      test_startdate      | volts  
> ! -------+--------------------------+-------------+-------------+--------------------------+--------
> !  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
> !  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
> ! (2 rows)
>   
>   -- check it works with OUT parameters
>   CREATE FUNCTION crosstab_out(text, text,
> --- 254,265 ----
>   SELECT * FROM crosstab_named(
>     'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>     'SELECT DISTINCT attribute FROM cth ORDER BY 1');
> !  rowid |          rowdt           | temperature | test_result |      test_startdate      |  volts  
> ! -------+--------------------------+-------------+-------------+--------------------------+---------
> !  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          |  2.6987
> !  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 |  3.1234
> !        | Thu Oct 25 00:00:00 2007 |          57 | PASS        | Wed Oct 24 00:00:00 2007 | 1.41234
> ! (3 rows)
>   
>   -- check it works with OUT parameters
>   CREATE FUNCTION crosstab_out(text, text,
> ***************
> *** 252,262 ****
>   SELECT * FROM crosstab_out(
>     'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>     'SELECT DISTINCT attribute FROM cth ORDER BY 1');
> !  rowid |          rowdt           | temperature | test_result |      test_startdate      | volts  
> ! -------+--------------------------+-------------+-------------+--------------------------+--------
> !  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
> !  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
> ! (2 rows)
>   
>   --
>   -- connectby
> --- 272,283 ----
>   SELECT * FROM crosstab_out(
>     'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>     'SELECT DISTINCT attribute FROM cth ORDER BY 1');
> !  rowid |          rowdt           | temperature | test_result |      test_startdate      |  volts  
> ! -------+--------------------------+-------------+-------------+--------------------------+---------
> !  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          |  2.6987
> !  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 |  3.1234
> !        | Thu Oct 25 00:00:00 2007 |          57 | PASS        | Wed Oct 24 00:00:00 2007 | 1.41234
> ! (3 rows)
>   
>   --
>   -- connectby
> Index: sql/tablefunc.sql
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/sql/tablefunc.sql,v
> retrieving revision 1.12
> diff -c -r1.12 tablefunc.sql
> *** sql/tablefunc.sql	27 Feb 2006 16:09:49 -0000	1.12
> --- sql/tablefunc.sql	25 Oct 2007 22:20:09 -0000
> ***************
> *** 61,66 ****
> --- 61,71 ----
>   insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
>   insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
>   insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
> + -- next group tests for NULL rowids
> + insert into cth values(DEFAULT,NULL,'25 October 2007','temperature','57');
> + insert into cth values(DEFAULT,NULL,'25 October 2007','test_result','PASS');
> + insert into cth values(DEFAULT,NULL,'25 October 2007','test_startdate','24 October 2007');
> + insert into cth values(DEFAULT,NULL,'25 October 2007','volts','1.41234');
>   
>   -- return attributes as plain text
>   SELECT * FROM crosstab(

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

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

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

In response to

Responses

pgsql-patches by date

Next:From: Bruce MomjianDate: 2007-11-09 22:07:24
Subject: Re: [HACKERS] install-strip causes dyld errors on OS X
Previous:From: Albert Cervera i ArenyDate: 2007-11-09 19:47:45
Subject: Re: Contrib docs v1

pgsql-general by date

Next:From: Dann CorbitDate: 2007-11-09 21:47:43
Subject: Re: Importance of CPU floating point performance...
Previous:From: Ted ByersDate: 2007-11-09 21:18:17
Subject: Re: Optimal time series sampling.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group