Re: Dynamic table with variable number of columns

Hi Thomas,

No I actually need the product name (prod1, prod2....) to become column headings, which is effectively transposing the table.

Thanks.

NK

----- Original Message -----

From: Thomas Burdairon <tburdairon@entelience.com>

Date: Wednesday, July 12, 2006 10:53 am

Subject: Re: [GENERAL] 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@optonline.net wrote:
>
> >
> > Bruno Wolff III wrote:
> >> On Tue, Jul 11, 2006 at 06:05:18 -0700,
> >>   nkunkov@optonline.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@postgresql.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
>
>