Re: listing relations

On Thu, 11 Jun 2009, Brandon Metcalf wrote:

> Is there a "\" command to show all tables in the current search path?

SELECT
   nspname,relname,relkind
FROM
   pg_class c,
   pg_namespace n
WHERE
   c.relnamespace = n.oid AND
   relkind='r' AND
   nspname !~ '^pg_toast' AND
   nspname = ANY(current_schemas(false))
ORDER BY
   nspname,relname;

Change "false" to "true" if you want to include the stuff in pg_catalog
too.  Remove the filter on relkind if you want to see things besides just
tables.  There's a bunch of other system info functions you might find
useful documented at
http://www.postgresql.org/docs/8.3/static/functions-info.html as well.

(The pg_toast filter is probably redundant here, I try to keep that in all
these pg_class/pg_namespace join examples because it's handy for more
normal queries)

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD