Re: table column information

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