Re: building a row with a plpgsql function

Joe Conway wrote:
> Raphael Bauduin wrote:
>
>> A little update on what I do (in case someone gets in the same
>> situation as I am).
>> Rather than writing the AS ( field type, ....) part of the query, I
>> build it in my application
>> each time a crosstab query is issued.
>> For example for this query:
>>
>> SELECT * FROM crosstab
>> (
>>  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>>  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
>> )
>> AS
>> (
>>    XXXXX
>> );
>>
>> the application code replaces the XXXXX by getting the results of
>> "SELECT DISTINCT attribute FROM cth ORDER BY 1" and iterating over
>> the attributes returned to build the columns list (all columns are text).
>>
>> This works really fine. My problem now is that the query to get the
>> attributes
>> is taking a looong time (2.7 seconds), and it is issued twice!
>>
>
> Sorry for the slow response. Couple of thoughts:
>
> 1. As long as you are building the query in your application, use the
> results of the distinct query to build the category sql as a UNION ALL
> of literals -- e.g.:
>
> SELECT * FROM crosstab
> (
>  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>  'SELECT ''temperature''
>   UNION ALL SELECT ''test_result''
>   UNION ALL SELECT ''test_startdate''
>   UNION ALL SELECT ''volts'''
> )
> AS
> (
>   rowid text,
>   rowdt timestamp,
>   temperature int4,
>   test_result text,
>   test_startdate timestamp,
>   volts float8
> );
>

I've experimented a bit and I'm confident we can optimize the queries as needed.
the use of crosstab wil require some tweaking and optimising, but I think it's really
 worth it in our case.



> 2. How often do new attributes show up? If it is relatively infrequent,
> you might want to build a table ("materialized view") from
>  "SELECT DISTINCT attribute FROM cth ORDER BY 1"
> and then refresh it periodically.


the problem is that the attribute list depends of the item we display.

But the crosstab function does exactly what I need. I'll work with it
and look at optimize it later on. you might hear from me again at that time ;-)


Thanks for your help!

Raph


>
> Joe