Re: Dynamic table with variable number of columns

On Wed, Jul 12, 2006 at 07:08:15 -0700,
  nkunkov@optonline.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.