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
