Re: Subquery for column names of tablefunc crosstab queries

What we have done in the past is dynamically build the crosstab query
while getting the field names through another query. Then we select the
query into a table and then select * from the table to get the result.


Sim



On 01/21/2011 07:34 PM, Julia Jacobson wrote:

> 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
>
>