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