Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

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))
share|improve this answer
    
Thanks @Phil. However, that command returns 128 rows, whereas \dv returns 57 rows. It seems to be giving me system views as well, like "tables", "columns", "domains", "pg_role", etc. How do I get just the views that I created? –  Rob Bednark Sep 6 '12 at 18:28
    
It gives you a list of the ones you have access to. To get ones for a given schema, add where table_schema='USERNAME'to the query –  FreshPhilOfSO Sep 6 '12 at 18:37
    
@phil This works only if there is a schema named identically to the user. By default this is not the case, however, there is the public schema. –  dezso Sep 6 '12 at 19:12
    
INFORMATION_SCHEMA.views just shows the views that current user has right(s) on. If there are views in the database that current user doesn't have right(s) on, the name of those views will not show in the result. From the document in @Phil's link: Only those views are shown that the current user has access to (by way of being the owner or having some privilege). –  Cao Minh Tu Nov 28 '13 at 4:01
add comment

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
;
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.