PostgreSQL Catalogs: Table List by Schema
This is an example of a simple master detail report.
tablelist
set search_path to ere, rugby, public;
SELECT n.nspname as "schema", c.relname as "name",
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence' WHEN 's'
THEN 'special' END as "type",
u.usename as "owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname <> 'pg_toast' :vschema:
ORDER BY 1,2;
schema, name, type, owner
schema, detail
| Schema: _schema_ |
|---|
| Name | Type | Owner |
|---|
| _name_ | _type_ | _owner_ |