From: | Raphael Bauduin <raphael(dot)bauduin(at)be(dot)easynet(dot)net> |
---|---|
To: | Joe Conway <mail(at)joeconway(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: building a row with a plpgsql function |
Date: | 2004-11-08 13:18:27 |
Message-ID: | 418F7223.5060003@be.easynet.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kumar S | 2004-11-08 17:26:32 | How to allow a JDBC to connect to my postgres database |
Previous Message | John DeSoi | 2004-11-08 13:12:28 | Re: Pgsql install |