Re: Dynamic table with variable number of columns

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(at)optonline(dot)net wrote:

>
> Bruno Wolff III wrote:
>> On Tue, Jul 11, 2006 at 06:05:18 -0700,
>> nkunkov(at)optonline(dot)net wrote:
>>> 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:
>> http://www.postgresql.org/docs/8.1/static/information-schema.html
>> http://www.postgresql.org/docs/8.1/static/catalogs.html
>>
>> 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(at)postgresql(dot)org 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