Re: Dynamic table with variable number of columns

Hi,
Thanks again.
One more question. Will crosstab function work if i will not know the
number/names of columns before hand? Or I need to supply colum
headings?

Thanks again.
NK

Bruno Wolff III wrote:
> On Wed, Jul 12, 2006 at 07:08:15 -0700,
> nkunkov(at)optonline(dot)net wrote:
> >
> > Thank you for the suggestions.
> > I will try to describe the problem better.
> > I have two problems to solve. First one is that I have to transpose a
> > table.
> > I have table A that looks like this:
> > date product price description
> > 1/1/2006 prod1 1.00 some product
> > 1/1/2006 prod2 3.00 other product
> >
> > I need to transpose this table to create table B
> > date prod1 prod2
> > 1/1/2006 1.00 3.00
> >
> > I think I can use EXECUTE statement and build the table dynamically by
> > using the result of the select statement for column names. Would that
> > be the right approach? Are there good examples somewhere on how to
> > implement this?
>
> The crosstabs contrib module can transpose tables for you.
>
> > My second problem, is that after creating the above transposed table, I
> > will be inserting more rows to it from table A and i might have more
> > products too. That means I will have to compare the value of product
> > from table A with the column names of table B and alter the table
> > accordingly. To compare coulmn names with the value of product in
> > table A I think I can use pg_attribute function. Would that be a right
> > way to go?
>
> I don't think that will work very well. I expect that adding data to the
> original tables and retransposing when you need reports would be a better
> way to go.
>
> Changing table definitions on the fly is going to be very costly and will
> break concurrent access.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match