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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD