Postgresql – How to list all views in SQL in PostgreSQL

postgresqlpostgresql-9.1view

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.

Best Answer

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))

Related Solutions

PostgreSQL – List Database Privileges Using psql

postgres=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

The docs on Privileges give an explanation of how to interpret the output. For specific privileges on a table of the current database, use \z myTable.

Postgresql – Users groups/permissions when working with PostgreSQL

There are two basic approaches to users and groups. I will also describe what approach I favor here and why.

The first approach is to use login roles to describe what software is logging on and restrict rights accordingly. This has the advantage of being relatively simple to maintain and a number of disadvantages as well, but it is probably the most common approach because of its administrative simplicity.

The second approach, which I favor, is to give every user his or her own role. DB permissions are then attached to groups (i.e. nologin inherit roles) which are granted to individual users. This is more complex to administer. Keep in mind that unlike Linux, PostgreSQL groups can be nested and unlike Windows, there is no deny acl, so permissions are always fully cumulative. In some cases where someone needs a restricted account we can create a separate user. This adds complexity to the administration process but it also helps mitigate security issues in the application because something like a sql injection attack is less likely to have privilege escalation possibilities, and it essentially allows outsourcing of authentication to the database, and PostgreSQL can outsource this to a variety of sources (RADIUS, PAM, KRB5, GSSAPI, SSPI, etc).

Best Answer

Related Solutions

PostgreSQL – List Database Privileges Using psql

Postgresql – Users groups/permissions when working with PostgreSQL

Related Question