Re: Subquery for column names of tablefunc crosstab queries

On 01/21/2011 05:48 AM, Julia Jacobson wrote:
> Having read the documentation of the tablefunc module,
> I'm wondering whether it is possible to get the values for the names of
> the columns for the crosstab query from a subquery.
> A minimal example would look like this:
>
> CREATE TABLE mytable(rowid text, rowdt timestamp, temperature int);
> INSERT INTO cth VALUES('test1','01 March 2003','42');
> INSERT INTO cth VALUES('test2','02 March 2003','53');
> INSERT INTO cth VALUES('test3','03 March 2003','49');
>
> SELECT * FROM crosstab
> (
>   'SELECT rowid, rowdt, temperature FROM mytable ORDER BY 1',
>   'SELECT DISTINCT rowdt FROM mytable ORDER BY 1'
> )
> AS
> (
>        rowid text  # This works fine
>        # Here should be a subquery to get column names
>        # automatically,
>        # like 'SELECT rowdt FROM mytable'
> );

No, it is not possible. The column definition list needs to be available
to the parser. I usually recommend running this as two queries from your
application. The first does:

SELECT DISTINCT rowdt::date FROM mytable ORDER BY 1;
   rowdt
------------
 2003-03-01
 2003-03-02
 2003-03-03
(3 rows)


Then the application dynamically builds the second query and executes
it. Following your example, something like:

SELECT * FROM crosstab
(
  $$SELECT rowid, rowdt::date, temperature FROM mytable ORDER BY 1$$,
  $$VALUES('2003-03-01'),('2003-03-02'),('2003-03-03')$$
)
AS
(
   rowid text,
   "2003-03-01" int,
   "2003-03-02" int,
   "2003-03-03" int
);
 rowid | 2003-03-01 | 2003-03-02 | 2003-03-03
-------+------------+------------+------------
 test1 |         42 |            |
 test2 |            |         53 |
 test3 |            |            |         49
(3 rows)

HTH,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support