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

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




> Thanks!
>
> Raph
>
>
>>
>> HTH,
>>
>> Joe
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings