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