table column information

[Scot L. Harris]

Currently using Postgresql 7.2.4-5.80 with php 4.2.2.-8.0.8 on a redhat
8.0 system.

I am writing some php scripts where I want to generate a list of the
column names in a particular table that the user selects. I could take
the brute force method and hard code the column names but then every
time I add a new table or modify an existing one I would have to modify
the code. What I want is to have a generic function that given the
table name it will pull the column names for my use.

I need to get the table column names for several tables I have setup. I
know if I do a select * from tablename I can then use the pg_fieldname
function to pull the column names for all columns.

But I don't think I want to select the entire contents of the table
every time I want to get the names of the columns. I know this will
work but I think performance will be very poor.

Trying to find something the equivalent of doing a \d tablename in psql.
I did see a function to pull meta data but that is in a 4.3 version of
php.

I have also been trying to track down some information on the pga_layout
table. This appears to be a system table that might contain the
information I want but it does not list every table I have created. Not
sure what that is.

The books I have do not say much if anything about such system tables.

Any help or pointers would be appreciated.
--
Scot L. Harris <we***@cfl.rr.com>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Carl E. McMillin]

Hi,

Use getMetadata of java.sql.Connection. According to jdocs, the
DatabaseMetaData object can probably give you what you are looking for.

Carl <|};-)>

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of Scot L. Harris
Sent: Sunday, May 16, 2004 1:22 PM
To: pg***********@postgresql.org
Subject: [GENERAL] table column information
Currently using Postgresql 7.2.4-5.80 with php 4.2.2.-8.0.8 on a redhat 8.0
system.

I am writing some php scripts where I want to generate a list of the column
names in a particular table that the user selects. I could take the brute
force method and hard code the column names but then every time I add a new
table or modify an existing one I would have to modify the code. What I
want is to have a generic function that given the table name it will pull
the column names for my use.

I need to get the table column names for several tables I have setup. I
know if I do a select * from tablename I can then use the pg_fieldname
function to pull the column names for all columns.

But I don't think I want to select the entire contents of the table every
time I want to get the names of the columns. I know this will work but I
think performance will be very poor.

Trying to find something the equivalent of doing a \d tablename in psql.
I did see a function to pull meta data but that is in a 4.3 version of php.
I have also been trying to track down some information on the pga_layout
table. This appears to be a system table that might contain the information
I want but it does not list every table I have created. Not sure what that
is.

The books I have do not say much if anything about such system tables.

Any help or pointers would be appreciated.


--
Scot L. Harris <we***@cfl.rr.com>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Scot L. Harris]

On Sun, 2004-05-16 at 16:58, Carl E. McMillin wrote:

Hi,

Use getMetadata of java.sql.Connection. According to jdocs, the
DatabaseMetaData object can probably give you what you are looking for.

Carl <|};-)>

Thanks, but I am using php 4.2.2 not java for this application.

--
Scot L. Harris <we***@cfl.rr.com>
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Nick Barr]

Scot L. Harris wrote:

Currently using Postgresql 7.2.4-5.80 with php 4.2.2.-8.0.8 on a redhat
8.0 system.

I am writing some php scripts where I want to generate a list of the
column names in a particular table that the user selects. I could take
the brute force method and hard code the column names but then every
time I add a new table or modify an existing one I would have to modify
the code. What I want is to have a generic function that given the
table name it will pull the column names for my use.

I need to get the table column names for several tables I have setup. I
know if I do a select * from tablename I can then use the pg_fieldname
function to pull the column names for all columns.

But I don't think I want to select the entire contents of the table
every time I want to get the names of the columns. I know this will
work but I think performance will be very poor.

Trying to find something the equivalent of doing a \d tablename in psql.
I did see a function to pull meta data but that is in a 4.3 version of
php.

I have also been trying to track down some information on the pga_layout
table. This appears to be a system table that might contain the
information I want but it does not list every table I have created. Not
sure what that is.

The books I have do not say much if anything about such system tables.

Any help or pointers would be appreciated.

Hi,

You want to be querying the postgres catalog tables. See here for more info:

http://www.postgresql.org/docs/7.2/static/catalogs.html

The tables you want to look at are pg_class and pg_attribute. You will
want to query pg_class to get the oid of the table. Then you can query
pg_attribute using that oid to get the column names and types. This is
all the \d tablename does in psql, send a query to the db.

I cant remember exactly what you need to do but you can find out what
query psql sends to the backend by adding the -E parameter. For example:

psql -d tesdb -E

Then whenever psql fires off a query you can see it. So you could do:

psql -d testdb -R

testdb> \d sometable

And you will see what the query that you would need to execute to get
the column names ;-)

HTH
Nick

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Scot L. Harris]

Wanted to thank everyone that responded. I have my application working
now just the way I wanted it. The pointers to the pg_class and
pg_attribute tables did the trick. And I will be reading some more on
the documentation that was pointed out.

Again thanks for the help.
--
Scot L. Harris <we***@cfl.rr.com>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org