Subquery for column names of tablefunc crosstab queries

Julia Jacobson <julia.jacobson@xxxxxxxx> · Fri, 21 Jan 2011 14:48:51 +0100

Hello everybody out there familiar with the tablefunc module,

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'
);

Thanks in advance,
Julia

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general