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

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