Re: Dynamic table with variable number of columns

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.