[GENERAL] Dynamic table with variable number of columns
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Dynamic table with variable number of columns
On Tue, Jul 11, 2006 at 06:05:18 -0700, [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Dynamic table with variable number of columns
Bruno Wolff III wrote: > On Tue, Jul 11, 2006 at 06:05:18 -0700, > [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 prod1prod2 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
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 withdate products price1/1/2006 prod1 1.01/1/2006 prod2 3.0or replace prod_name py product_id, ... Thomas On Jul 12, 2006, at 16:08, [EMAIL PROTECTED] wrote:Bruno Wolff III wrote: On Tue, Jul 11, 2006 at 06:05:18 -0700, [EMAIL PROTECTED] 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 fetchedfrom the database using a select statement from some other table. Isit 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 valuesfetched 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'dlike to dynamically alter the table and add a coulmn with the name justfetched from the DB.Your help is greatly appreciated.ThanksNK Information on the column names of tables in the database are availablefrom the information schema and the catlog tables. You can find more about thisin the documentation:http://www.postgresql.org/docs/8.1/static/information-schema.htmlhttp://www.postgresql.org/docs/8.1/static/catalogs.htmlYou might get better help by describing the actual problem you are trying tosolve rather than asking for help with a particular approach to solving thatproblem. The approach you are trying seems to be seriously broken and itwould 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 [EMAIL PROTECTED] 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 atable.I have table A that looks like this:date product price description1/1/2006 prod1 1.00 some product1/1/2006 prod2 3.00 other productI need to transpose this table to create table Bdate prod1 prod21/1/2006 1.00 3.00I think I can use EXECUTE statement and build the table dynamically byusing the result of the select statement for column names. Would thatbe the right approach? Are there good examples somewhere on how toimplement this?My second problem, is that after creating the above transposed table, Iwill be inserting more rows to it from table A and i might have moreproducts too. That means I will have to compare the value of productfrom table A with the column names of table B and alter the tableaccordingly. To compare coulmn names with the value of product intable A I think I can use pg_attribute function. Would that be a rightway to go?Thanks for your help.NK---(end of broadcast)---TIP 6: explain analyze is your friend
Re: [GENERAL] Dynamic table with variable number of columns
On Wed, Jul 12, 2006 at 07:08:15 -0700, [EMAIL PROTECTED] 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 prod1prod2 > 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
Re: [GENERAL] 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 Thomas Burdairon wrote: > if i understand well you need to have an history for your products. > i would have a table B with > date productsprice > 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, [EMAIL PROTECTED] wrote: > > > > > Bruno Wolff III wrote: > >> On Tue, Jul 11, 2006 at 06:05:18 -0700, > >> [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 prod1prod2 > > 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 > > -khtml-line-break: after-white-space; ">if i understand well you need to = > have an history for your products.i would have a table B = > withdate =A0 =A0=A0 =A0=A0 =A0 products=A0 =A0=A0 =A0=A0 =A0=A0= > =A0price1/1/2006=A0 =A0 =A0prod1=A0 =A0 =A0=A0 =A0=A0 =A0=A0 = > =A0=A0 =A01.01/1/2006=A0 =A0 =A0prod2=A0 =A0 =A0=A0 =A0=A0 =A0=A0= > =A0=A0 =A03.0 class=3D"khtml-block-placeholder">or replace prod_name py = > product_id, ... font: 12.0px Helvetica; min-height: 14.0px"> 0.0px 0.0px 0.0px 0.0px"> style=3D"font: 12.0px Helvetica">Thomas = > On Jul 12, 2006, at 16:08, href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED] = > wrote: type=3D"cite"> margin-bottom: 0px; margin-left: 0px; min-height: 14px; "> style=3D"margin-top: 0px; margin-right: 0px; margin-bottom: 0px; = > margin-left: 0px; ">Bruno Wolff III wrote: type=3D"cite"> margin-bottom: 0px; margin-left: 0px; ">On Tue, Jul 11, 2006 at 06:05:18 = > -0700, margin-bottom: 0px; margin-left: 0px; "> class=3D"Apple-converted-space">=A0 href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED] = > wrote: margin-right: 0px; margin-bottom: 0px; margin-left: 0px; = > ">Hello, margin-bottom: 0px; margin-left: 0px; ">I'm a pgs
Re: [GENERAL] 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 <[EMAIL PROTECTED]> 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, [EMAIL PROTECTED] wrote: > > > > > Bruno Wolff III wrote: > >> On Tue, Jul 11, 2006 at 06:05:18 -0700, > >> [EMAIL PROTECTED] 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 > [EMAIL PROTECTED] 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 > >
Re: [GENERAL] Dynamic table with variable number of columns
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, > [EMAIL PROTECTED] 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 prod1prod2 > > 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Dynamic table with variable number of columns
On Wed, Jul 12, 2006 at 13:38:34 -0700, [EMAIL PROTECTED] wrote: > 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? I checked a bit into this, and the actual contrib name is tablefunc, not crosstab. It provides crosstab functions for up to 4 columns, but it isn't hard to make ones that handle more columns. You can read the included readme file at: http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/tablefunc/README.tablefunc?rev=1.14 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Dynamic table with variable number of columns
Thank you very much. Much appreciated. NK - Original Message - From: Bruno Wolff III <[EMAIL PROTECTED]> Date: Friday, July 14, 2006 2:50 pm Subject: Re: Dynamic table with variable number of columns > On Wed, Jul 12, 2006 at 13:38:34 -0700, > [EMAIL PROTECTED] wrote: > > 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? > > I checked a bit into this, and the actual contrib name is > tablefunc, not > crosstab. It provides crosstab functions for up to 4 columns, but > it isn't > hard to make ones that handle more columns. > > You can read the included readme file at: > http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/tablefunc/README.tablefunc?rev=1.14 > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
