Dynamic table with variable number of columns

Started by Nonamealmost 20 years ago10 messagesgeneral

nkunkov@optonline.net

almost 20 years ago

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

Bruno Wolff III

bruno@wolff.to

almost 20 years ago

In reply to: Noname (#1)

Re: Dynamic table with variable number of columns

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.

Noname

nkunkov@optonline.net

almost 20 years ago

In reply to: Bruno Wolff III (#2)

Re: Dynamic table with variable number of columns

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

Thomas Burdairon

tburdairon@entelience.com

almost 20 years ago

In reply to: Noname (#3)

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@optonline.net wrote:

Show quoted text

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

Noname

nkunkov@optonline.net

almost 20 years ago

In reply to: Thomas Burdairon (#4)

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

Show quoted text

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

Noname

nkunkov@optonline.net

almost 20 years ago

In reply to: Thomas Burdairon (#4)

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.

Thomas Burdairon wrote:

Show quoted text

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

--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@optonline.net">nkunkov@optonline.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@optonline.net">nkunkov@optonline.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@postgresql.org">majordomo@postgresql.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--

Bruno Wolff III

bruno@wolff.to

almost 20 years ago

In reply to: Noname (#3)

Re: Dynamic table with variable number of columns

On Wed, Jul 12, 2006 at 07:08:15 -0700,
nkunkov@optonline.net 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.

Noname

nkunkov@optonline.net

almost 20 years ago

In reply to: Bruno Wolff III (#7)

Re: 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:

Show quoted text

On Wed, Jul 12, 2006 at 07:08:15 -0700,
nkunkov@optonline.net 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

Bruno Wolff III

bruno@wolff.to

almost 20 years ago

In reply to: Noname (#8)

Re: Dynamic table with variable number of columns

On Wed, Jul 12, 2006 at 13:38:34 -0700,
nkunkov@optonline.net 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

#10

Noname

nkunkov@optonline.net

almost 20 years ago

In reply to: Bruno Wolff III (#9)

Re: Dynamic table with variable number of columns

Thank you very much.
Much appreciated.
NK

----- Original Message -----
From: Bruno Wolff III <bruno@wolff.to>
Date: Friday, July 14, 2006 2:50 pm
Subject: Re: Dynamic table with variable number of columns

Show quoted text

On Wed, Jul 12, 2006 at 13:38:34 -0700,
nkunkov@optonline.net 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