Search Postgresql Archives

Re: Dynamic table with variable number of columns

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



if i understand well you need to have an history for your products.
i would have a table B with
date           products            price
1/1/2006     prod1                 1.0
1/1/2006     prod2                 3.0

or replace prod_name py product_id, ...


Thomas


On Jul 12, 2006, at 16:08, nkunkov@xxxxxxxxxxxxx wrote:


Bruno Wolff III wrote:
On Tue, Jul 11, 2006 at 06:05:18 -0700,
Hello,
I'm a pgsql novice and here is what I'm trying to do:
1.    I need to create a dynamic table with the column names fetched
from the database using a select statement from some other table.  Is
it possible?  Could you point me to a simple example on how to do it?
2.   I would like to compare the list of coulmn names which are values
fetched from some table with the column names of the existing table.
If one of the names doesn't exist as a column name of my table, I'd
like to dynamically alter the table and add a coulmn with the name just
fetched from the DB.
Your help is greatly appreciated.
Thanks
NK

Information on the column names of tables in the database are available
from the information schema and the catlog tables. You can find more about this
in the documentation:

You might get better help by describing the actual problem you are trying to
solve rather than asking for help with a particular approach to solving that
problem. The approach you are trying seems to be seriously broken and it
would probably be a good idea to consider other approaches.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly

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?

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?

Thanks for your help.
NK


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux