Forum OpenACS Q&A: How do I get a table listing?

Collapse
1: How do I get a table listing?
Posted by peter sadlon on 06/09/01 01:58 AM
I have run into a little problem and can't seem to find anyone mentioning this in your archives. With oracle you can "select TABLE_NAME from USER_TABLES" or something simular to get a list of all the tables in your database. With postgres I can't seem to find such a thing, instead they have "d" which can be used in a variety of ways. There is only one problem, this does not work:
    set selection [ns_db select $db "d"]
neither does dml, exec in place of select or d in place of d. Is there a way to get a table listing (and then a field listing of a given table) in Postgres using a select statement?

There are a number of applications I wrote in an Oracle install, for example one which would draw a Bachman Diagram of your database, which I would really like to port over to Postgres.

_Peter

Collapse
2: Response to How do I get a table listing? (response to 1)
Posted by Michael A. Cleverly on 06/09/01 02:17 AM
You can query the pg_class table: select relname from pg_class where relkind = 'r'. (This will include the system tables that psql doesn't show you when you do a dt).

If anyone should ever happen upon this thread searching for the equivalent answer for Solid, the query would be: select table_name from sys_tables.

Collapse
3: Response to How do I get a table listing? (response to 1)
Posted by Stephen van Egmond on 06/09/01 05:39 AM
To find the answer to these questions in general, take note of the -E parameter to pgsql:

-E Display queries that internal commands generate This will show you what d produces, as well as dd, l, etc.

Collapse
4: Response to How do I get a table listing? (response to 1)
Posted by Dan Wickstrom on 06/11/01 03:45 PM
Support for this is also built in to the pg driver.  You can do something like ns_pg list $db or ns_pg listall $db.  The list command excludes the system tables, while the listall command includes them.