On Wed, Jan 19, 2005 at 12:14:57AM -0500, Ken Tozier wrote: > I recently stumbled upon the system catalog functions here > "http://www.postgresql.org/docs/7.4/static/catalogs.html"; and see that > it's easy to get a list of all databases and relatively easy to get a > list of tables, but there doesn't seem to be any built in method for > retrieving a table definition. See also "The Information Schema" if you're using 7.4 or later. > The best I could come up with would be to do a select something > like this: > > SELECT * FROM pg_attribute WHERE attrelid=<the table oid>; > > The problem I'm running into however, is that given a table name, there > doesn't seem to be any way to get the table oid. See "Object Identifier Types" in the "Data Types" chapter. SELECT * FROM pg_attribute WHERE attrelid = 'tablename'::regclass; > Is there some function or query that does this? Better yet, is there > an easier way to get at this metadata? If you run "psql -E" you can see the queries that psql makes when you issue commands like "\d tablename". As you can see, it takes a lot of information from the system catalogs to generate a description of a table. The Information Schema abstracts these queries through views, so querying them might be the easiest way if they provide what you need. See in particular information_schema.columns. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
