Re: building a row with a plpgsql function - Mailing list pgsql-novice

Raphael Bauduin wrote:
> Raphael Bauduin wrote:
>
>> Joe Conway wrote:
>>
>>> Raphael Bauduin wrote:
>>
>>
>>
>> [snip]
>>
>>>
>>>
>>> See contrib/tablefunc, and read through the following link for
>>> examples similar to what you are doing:
>>>
>>> http://www.joeconway.com/pres_oscon_2004-r1.pdf
>>> http://www.joeconway.com/flex.sql
>>
>>
>>
>> Seems to be exactly what I need! I'll look further at it.
>
>
> I've tested it and it does exactly what I want, but there is one problem
> in my case: I need to specify the column definitions. But in my case the
> number of columns is variable.  I call crosstab like that:
>
> select * from crosstab(
> 'select item_id, detail_name, detail_value from vw_item_details where
> item_id=10',
> 'select detail_name from item_details where item_detail_id = (select
> item_detail_id from vw_item_details where item_id=10)'
> ) AS (  ...  )
> If I have to write the AS ( ... ) part of the query, it means that each
> time we add a detail
> to an item, I'll have to modify this query to make the detail appear.
>
> In case I was not clear in my description, it is similar to the example
> given in the README.
>
> SELECT * FROM crosstab
> (
>  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
> )
> AS
> (
>       rowid text,
>       rowdt timestamp,
>       temperature int4,
>       test_result text,
>       test_startdate timestamp,
>       volts float8
> );
>
> Working on the following data:
> create table cth(id serial, rowid text, rowdt timestamp, attribute text,
> val text);
> insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42');
> insert into cth values(DEFAULT,'test1','01 March
> 2003','test_result','PASS');
> insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987');
> insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53');
> insert into cth values(DEFAULT,'test2','02 March
> 2003','test_result','FAIL');
> insert into cth values(DEFAULT,'test2','02 March
> 2003','test_startdate','01 March 2003');
> insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
>
> you have 4 attributes defined.But if 'SELECT DISTINCT attribute FROM cth
> ORDER BY 1' returns a 5th attribute, you'll have to rewrite the AS ( ..
> ) part of the query to make this 5th attribute
> appear in the results. Is there a way to avoid that?
>
> I could say that all values returned are of type text, so all columns
> would be text.
> Is it possible to generate the AS ( .. ) part dynamically? Or hould I
> modify the C code
> (I hope not ;-)
>
> Thanks in advance for your help.
>
> Raph

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!

Would be great if someone could point me in the right direction to avoid this
duplication of queries. If not I'll continue to search and post my discoveries ;-)
One thing I'll try is to rework the query to get the returned fields (at the moment
it joins 4 or 5 tables, but it could be faster to issue 2 simpler queries to get
the same result)

ciao!


Raph