Couple of interesting points (of which I'm sure most of you are aware, but many pgsql newbies aren't)...
select from pg_tables shows all the tables, including the special pg_ internal tables, which is cool.
But, did you know it was a view?
try this from psql:
\dS
you'll see that pg_tables is a view. To view it's definition, try \d pg_tables to see how big it is. Then look at pg_rewrite to really flip out.
Anyway, it's interesting to see what lives under a simple "select * from pg_tables;", huh?
Oh, another way to list them all is to use the exec command to run psql, like so:
psql dbname -c '\d'
and grab the output. That one give you the tables, views, and sequences, and tells you the type.
to view the build of a table you can grab the output of a 'psql dbname -c "\d tablename"' to see how a table is out together.
Note that if psql isn't in the path of the user your web server runs under you'll need to type it as "/usr/local/pgsql/bin/psql" or something similar with the whole path to psql.
