Thread: 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 <webid@cfl.rr.com>

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: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Scot L. Harris
Sent: Sunday, May 16, 2004 1:22 PM
To: pgsql-general@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 <webid@cfl.rr.com>


---------------------------(end of broadcast)---------------------------
TIP 3: 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

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 <webid@cfl.rr.com>

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

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 <webid@cfl.rr.com>