How do I list all views for a database using an SQL command in PostgreSQL?
I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g.,
SELECT ...;
my_view_1
my_view_2
my_view_3
I'm running PostgreSQL v9.1.4 on Ubuntu Linux.
From the documentation:
select table_name from INFORMATION_SCHEMA.views;
If you don't want the system views is your result, try this:
select table_name from INFORMATION_SCHEMA.views WHERE table_schema = ANY (current_schemas(false))
You can query pg_catalog.pg_views for your desired information:
select viewname from pg_catalog.pg_views;
Refined query to get schema name also - just in case you have multiple views with the same name in different schemas - and left out those system views:
select schemaname, viewname from pg_catalog.pg_views
where schemaname NOT IN ('pg_catalog', 'information_schema')
order by schemaname, viewname;
IMHO, this way is better than query INFORMATION_SCHEMA.views for reasons stated in my comment to Phil's answer.
If you only need this interactively while in psql, you can also use \dv to show views, or \dm for materialized views. Or use with +, like \dm+ for example to show some additional information (mostly useful to see materialized view size).
Try:
SELECT n.nspname AS table_schema,
pg_catalog.pg_get_userbyid(c.relowner) AS table_owner,
c.relname AS table_name
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.relkind = 'v'
;
If you want more detail you can modify the following to suit your needs:
SELECT n.nspname AS table_schema,
pg_catalog.pg_get_userbyid(c.relowner) AS table_owner,
c.relname AS table_name,
s.n_live_tup AS row_count,
count (a.attname) AS column_count,
pg_catalog.obj_description(c.oid, 'pg_class') AS comments,
CASE c.relkind
WHEN 'v'
THEN pg_catalog.pg_get_viewdef(c.oid, true)
ELSE null
END AS query
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
LEFT JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid AND a.attnum > 0 AND NOT a.attisdropped)
LEFT JOIN pg_catalog.pg_stat_all_tables s ON (c.oid = s.relid)
WHERE c.relkind = 'v'
GROUP BY n.nspname,
c.relowner,
c.relkind,
c.relname,
s.n_live_tup,
c.oid
ORDER BY n.nspname,
c.relname
;
Here's a query that will bring up your materialized views as well and show you the views' dependencies.
-- Get a list of views that have dependencies on other views
with view_oids as (
select
distinct(dependent_view.oid) as view_oid
from pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
WHERE
dependent_ns.nspname = 'public'
), view_dependencies as (
select
dependent_view.oid as dependent_oid,
dependent_ns.nspname as dependent_schema,
dependent_view.relname as dependent_view,
source_table.oid as dependency_oid,
source_ns.nspname as source_schema,
source_table.relname as source_view
from pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid
JOIN view_oids on source_table.oid = view_oids.view_oid
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE
source_ns.nspname = 'public'
group by
dependent_view.oid,
dependent_ns.nspname,
dependent_view.relname,
source_table.oid,
source_ns.nspname,
source_table.relname
)
select
view_dependencies.*
from view_dependencies
;
I created a view to list a catalog of views:
create or replace view show_views as
select table_name from INFORMATION_SCHEMA.views
WHERE table_schema = ANY (current_schemas(false));
And when I want to see all views in the database I write:
select * from show_views;
This can show all user-defined views in the current database:
\dv
And, this can show all user-defined views in the current database in detail:
\dv+
*There is no way to show all user-defined views in all databases at once.
In addition, this can show all user-defined and system views in the current database:
\dvS
And, this can show all user-defined and system views in the current database in detail:
\dvS+
And, you can show all user-defined and system views in the current database with information_schema.views and pg_proc as shown below:
SELECT table_name FROM information_schema.views;
SELECT viewname FROM pg_views;