Views in PostgreSQL
Creating views can improve query response times by restricting the number of tables and columns searched when a data request is made. You can create views that appear to client applications as feature classes or nonspatial tables. An ArcSDE view can reference a single table or feature class, or you can create views to join data from multiple tables.
Views in PostgreSQL use rules. The definition query for the view is passed to the rule system, which checks for any rules associated with the view. If there are, it adds those rules to the view and submits the whole query, including the rules, to the planner to create an execution plan and execute the query.
Any user that has the required permissions can create a view using SQL or the ArcSDE sdetable command. For information on privileges needed to create views, consult the PostgreSQL documentation for your release.
You can access view definitions in PostgreSQL by querying the pg_views system view.
The pg_views system view
When you create views in a PostgreSQL database, information about the views is stored PostgreSQL system tables. Rather than query the system tables, though, query the pg_views system view to see view definitions.
|
Field name |
Data type |
Description |
|---|---|---|
|
schemaname |
name |
The name of the schema in which the view is stored; references pg_namespace.nspname |
|
viewname |
name |
The name of the view; references pg_class.relname |
|
viewowner |
name |
The name of the owner of the view; references pg_authid.rolname |
|
definition |
text |
The reconstructed SELECT query that defines the view |