Subquery for column names of tablefunc crosstab queries

From: Julia Jacobson <julia(dot)jacobson(at)arcor(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Subquery for column names of tablefunc crosstab queries
Date: 2011-01-21 13:48:51
Message-ID: 4D398EC3.9080609@arcor.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vick Khera 2011-01-21 13:55:30 Re: Moving from SQL Anywhere to PostGres - First Time
Previous Message Vick Khera 2011-01-21 13:46:51 Re: Large object corruption during 'piped' pg_restore