Re: Subquery for column names of tablefunc crosstab queries - Mailing list pgsql-general

Thanks for your answer. The fact that the column definition list must be
available to the parser was not obvious to me.
Instead of building the second query dynamically in an application, I
would prefer a user-defined function, because there will probably be
several applications in need of it and (please correct me if I'm wrong)
I hope for a better performance by creating a permanent view of my
crosstab query.
So I have tried something like:

CREATE FUNCTION denorm_table(mytable_name) RETURNS text AS '
DECLARE
     tab_def_list TEXT;
BEGIN
     # First query to create table definition list
     SELECT DISTINCT rowdt::date INTO tab_def_list FROM mytable_name;
     # Crosstab Query
     SELECT * FROM crosstab
     (
       'SELECT rowid, rowdt, temperature FROM mytable ORDER BY 1',
       'SELECT DISTINCT rowdt FROM mytable ORDER BY 1'
     )
     AS
     (
             rowid text,
             tab_def_list
     );
END;
' LANGUAGE plpgsql;

Being rather unexperienced in PL/pgSQL, I have problems to get the code
working.
Two further problems are the fact that the names of columns are not
allowed to begin with a number and every entry in the table definition
list must not only contain the name of the column but of course also a
data type (always the same - int).
Is it possible for a newbie to solve my problem by a user-defined
function in PL/pgSQL or is it rather complicated?


On 01/21/2011 16:08, Joe Conway wrote:
> 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