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