Search Postgresql Archives

Re: Dynamic table with variable number of columns

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

 



Hi,
Thanks again.
One more question.  Will crosstab function work if i will not know the
number/names of columns before hand?  Or I need to supply colum
headings?

Thanks again.
NK

Bruno Wolff III wrote:
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match



[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