Re: generic crosstab ?

Am 25.04.2012 00:04, schrieb Joe Conway:
> On 04/24/2012 02:42 PM, David Johnston wrote:
>> You must specify the output record structure:
>>
>> SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name
>> colN_type]* )
>>
>> Whether this relates to the “materialization node” message you are
>> receiving I have no idea.
> The error is because you are selecting from a set returning function in
> the target list rather than the from clause. It should be more like:
>
> SELECT * FROM crosstab(text, text) AS ( col1_name col1_type [, colN_name
>> colN_type]* )
>

OK now i get at least some result.
But
1) I need to know how many categories will apear to construct the target
list. This is a wee bit of a problem as this number is actually dynamic.

2) There are some rows in the resulting list with empty columns within
the row.
When I execute the first query for a parent ID that has gaps in the
crosstab I see it shows no gaps in the categories when called outside
crosstab().
E.g. it dumps x1, x2, x3, x4, x5 when called seperately but crosstab()
shows
x1, x2, null, null, x5, null, x6, x7

How does this make sense ?

Thanks for the answers so far :)

select *
from
crosstab (
$$
select
parent_id as row_name,
'x' || row_number() over ( partition by parent_id order by
child_id ) as category,
child_id as value
from
children
order by 1
$$,
$$
select 'x' || generate_series(1, 15) as category order by 1
$$
)
as result (
row_name integer,
x1 integer,
x2 integer,
x3 integer,
x4 integer,
x5 integer,
x6 integer,
x7 integer,
x8 integer,
x9 integer,
x10 integer,
x11 integer,
x12 integer,
x13 integer,
x14 integer,
x15 integer
)