| From: | nkunkov(at)optonline(dot)net |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Dynamic table with variable number of columns |
| Date: | 2006-07-12 16:14:57 |
| Message-ID: | 1152720897.350115.219190@s13g2000cwa.googlegroups.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Hi Thomas,
No I actually need the product name (prod1, prod2....) to become column
headings, which is effectively transposing the table.
Thanks.
NK
Thomas Burdairon wrote:
> 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
>
>
> --Apple-Mail-2-462651084
> Content-Type: text/html; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 12170
>
> <HTML><BODY style=3D"word-wrap: break-word; -khtml-nbsp-mode: space; =
> -khtml-line-break: after-white-space; ">if i understand well you need to =
> have an history for your products.<DIV>i would have a table B =
> with</DIV><DIV>date =A0 =A0=A0 =A0=A0 =A0 products=A0 =A0=A0 =A0=A0 =A0=A0=
> =A0price</DIV><DIV>1/1/2006=A0 =A0 =A0prod1=A0 =A0 =A0=A0 =A0=A0 =A0=A0 =
> =A0=A0 =A01.0</DIV><DIV>1/1/2006=A0 =A0 =A0prod2=A0 =A0 =A0=A0 =A0=A0 =A0=A0=
> =A0=A0 =A03.0</DIV><DIV><BR =
> class=3D"khtml-block-placeholder"></DIV><DIV>or replace prod_name py =
> product_id, ...<BR><DIV> <P style=3D"margin: 0.0px 0.0px 0.0px 0.0px; =
> font: 12.0px Helvetica; min-height: 14.0px"><BR></P> <P style=3D"margin: =
> 0.0px 0.0px 0.0px 0.0px"><FONT face=3D"Helvetica" size=3D"3" =
> style=3D"font: 12.0px Helvetica">Thomas</FONT></P> =
> </DIV><BR><DIV><DIV>On Jul 12, 2006, at 16:08, <A =
> href=3D"mailto:nkunkov(at)optonline(dot)net">nkunkov(at)optonline(dot)net</A> =
> wrote:</DIV><BR class=3D"Apple-interchange-newline"><BLOCKQUOTE =
> type=3D"cite"><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">Bruno Wolff III wrote:</DIV> <BLOCKQUOTE =
> type=3D"cite"><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">On Tue, Jul 11, 2006 at 06:05:18 =
> -0700,</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; "><SPAN =
> class=3D"Apple-converted-space">=A0 </SPAN><A =
> href=3D"mailto:nkunkov(at)optonline(dot)net">nkunkov(at)optonline(dot)net</A> =
> wrote:</DIV> <BLOCKQUOTE type=3D"cite"><DIV style=3D"margin-top: 0px; =
> margin-right: 0px; margin-bottom: 0px; margin-left: 0px; =
> ">Hello,</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">I'm a pgsql novice and here is =
> what I'm trying to do:</DIV><DIV style=3D"margin-top: 0px; margin-right: =
> 0px; margin-bottom: 0px; margin-left: 0px; ">1.<SPAN =
> class=3D"Apple-converted-space">=A0 =A0 </SPAN>I need to create a =
> dynamic table with the column names fetched</DIV><DIV style=3D"margin-top:=
> 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">from =
> the database using a select statement from some other table.<SPAN =
> class=3D"Apple-converted-space">=A0 </SPAN>Is</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">it possible?<SPAN class=3D"Apple-converted-space">=A0 =
> </SPAN>Could you point me to a simple example on how to do it?</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">2. <SPAN class=3D"Apple-converted-space">=A0 =
> </SPAN>I would like to compare the list of coulmn names which are =
> values</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">fetched from some table with the =
> column names of the existing table.</DIV><DIV style=3D"margin-top: 0px; =
> margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">If one of the =
> names doesn't exist as a column name of my table, I'd</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">like to dynamically alter the table and add a coulmn =
> with the name just</DIV><DIV style=3D"margin-top: 0px; margin-right: =
> 0px; margin-bottom: 0px; margin-left: 0px; ">fetched from the =
> DB.</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">Your help is greatly =
> appreciated.</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">Thanks</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">NK</DIV> </BLOCKQUOTE><DIV style=3D"margin-top: 0px; =
> margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: =
> 14px; "><BR></DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">Information on the column names =
> of tables in the database are available</DIV><DIV style=3D"margin-top: =
> 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">from the =
> information schema and the catlog tables. You can find more about =
> this</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">in the documentation:</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; "><A =
> href=3D"http://www.postgresql.org/docs/8.1/static/information-schema.html"=
> >http://www.postgresql.org/docs/8.1/static/information-schema.html</A></DI=
> V><DIV style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; "><A =
> href=3D"http://www.postgresql.org/docs/8.1/static/catalogs.html">http://ww=
> w.postgresql.org/docs/8.1/static/catalogs.html</A></DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style=3D"margin-top: =
> 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">You =
> might get better help by describing the actual problem you are trying =
> to</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: =
> 0px; margin-left: 0px; ">solve rather than asking for help with a =
> particular approach to solving that</DIV><DIV style=3D"margin-top: 0px; =
> margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">problem. The =
> approach you are trying seems to be seriously broken and it</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">would probably be a good idea to consider other =
> approaches.</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">---------------------------(end of =
> broadcast)---------------------------</DIV><DIV style=3D"margin-top: =
> 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">TIP 1: =
> if posting/reading through Usenet, please send an appropriate</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; "><SPAN class=3D"Apple-converted-space">=A0=A0 =A0 =A0 =
> </SPAN>subscribe-nomail command to <A =
> href=3D"mailto:majordomo(at)postgresql(dot)org">majordomo(at)postgresql(dot)org</A> so =
> that your</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; "><SPAN =
> class=3D"Apple-converted-space">=A0=A0 =A0 =A0 </SPAN>message can get =
> through to the mailing list cleanly</DIV> </BLOCKQUOTE><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style=3D"margin-top: =
> 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Thank =
> you for the suggestions.</DIV><DIV style=3D"margin-top: 0px; =
> margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">I will try to =
> describe the problem better.</DIV><DIV style=3D"margin-top: 0px; =
> margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">I have two =
> problems to solve.<SPAN class=3D"Apple-converted-space">=A0 </SPAN>First =
> one is that I have to transpose a</DIV><DIV style=3D"margin-top: 0px; =
> margin-right: 0px; margin-bottom: 0px; margin-left: 0px; =
> ">table.</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">I have table A that looks like =
> this:</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">date<SPAN =
> class=3D"Apple-converted-space">=A0 =A0 =A0 =A0 =A0 </SPAN>product<SPAN =
> class=3D"Apple-converted-space">=A0 </SPAN>price description</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">1/1/2006 <SPAN class=3D"Apple-converted-space">=A0 =
> </SPAN>prod1<SPAN class=3D"Apple-converted-space">=A0 =A0 =A0 =
> </SPAN>1.00<SPAN class=3D"Apple-converted-space">=A0 </SPAN>some =
> product</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">1/1/2006 <SPAN =
> class=3D"Apple-converted-space">=A0 </SPAN>prod2<SPAN =
> class=3D"Apple-converted-space">=A0 =A0 =A0 </SPAN>3.00<SPAN =
> class=3D"Apple-converted-space">=A0 </SPAN>other product</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style=3D"margin-top: =
> 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">I need =
> to transpose this table to create table B</DIV><DIV style=3D"margin-top: =
> 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">date =
> <SPAN class=3D"Apple-converted-space">=A0 =A0 =A0 =A0 </SPAN>prod1<SPAN =
> class=3D"Apple-converted-space">=A0 =A0 </SPAN>prod2</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">1/1/2006 <SPAN class=3D"Apple-converted-space">=A0 =
> </SPAN>1.00<SPAN class=3D"Apple-converted-space">=A0 =A0 =A0 =
> </SPAN>3.00</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; min-height: 14px; "><BR></DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">I think I can use EXECUTE statement and build the =
> table dynamically by</DIV><DIV style=3D"margin-top: 0px; margin-right: =
> 0px; margin-bottom: 0px; margin-left: 0px; ">using the result of the =
> select statement for column names. Would that</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">be the right approach?<SPAN =
> class=3D"Apple-converted-space">=A0 </SPAN>Are there good examples =
> somewhere on how to</DIV><DIV style=3D"margin-top: 0px; margin-right: =
> 0px; margin-bottom: 0px; margin-left: 0px; ">implement this?</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style=3D"margin-top: =
> 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">My =
> second problem, is that after creating the above transposed table, =
> I</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: =
> 0px; margin-left: 0px; ">will be inserting more rows to it from table A =
> and i might have more</DIV><DIV style=3D"margin-top: 0px; margin-right: =
> 0px; margin-bottom: 0px; margin-left: 0px; ">products too.<SPAN =
> class=3D"Apple-converted-space">=A0 </SPAN>That means I will have to =
> compare the value of product</DIV><DIV style=3D"margin-top: 0px; =
> margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">from table A =
> with the column names of table B and alter the table</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">accordingly.<SPAN class=3D"Apple-converted-space">=A0 =
> </SPAN>To compare coulmn names with the value of product in</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; ">table A I think I can use pg_attribute =
> function.<SPAN class=3D"Apple-converted-space">=A0 </SPAN>Would that be =
> a right</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">way to go?</DIV><DIV =
> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; =
> margin-left: 0px; min-height: 14px; "><BR></DIV><DIV style=3D"margin-top: =
> 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Thanks =
> for your help.</DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">NK</DIV><DIV style=3D"margin-top: =
> 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; =
> min-height: 14px; "><BR></DIV><DIV style=3D"margin-top: 0px; =
> margin-right: 0px; margin-bottom: 0px; margin-left: 0px; min-height: =
> 14px; "><BR></DIV><DIV style=3D"margin-top: 0px; margin-right: 0px; =
> margin-bottom: 0px; margin-left: 0px; ">---------------------------(end =
> of broadcast)---------------------------</DIV><DIV style=3D"margin-top: =
> 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">TIP 6: =
> explain analyze is your friend</DIV> =
> </BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>=
>
> --Apple-Mail-2-462651084--
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tim Hart | 2006-07-12 16:18:23 | Re: Long term database archival |
| Previous Message | Ron Johnson | 2006-07-12 15:59:26 | Re: Long term database archival |