Re: building a row with a plpgsql function

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
);

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.

Joe