Views in PostgreSQL
Views in PostgreSQL
|
| Release 9.3 |
   |
Note:This topic was updated for 9.3.1.
Views are named, stored queries in the database, which are called and executed each time the view is included in a query to the database.
This topic covers how views are stored in PostgreSQL, how to create and register a view with ArcSDE, and how to create a view with a join.
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.
Querying the pg_views catalog for view information
When you create views in a PostgreSQL database, information about the views is stored in the pg_views system catalog. You can query this catalog to see information about the views in the database.
pg_views| 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 |
You can create a view on a feature class in the database using the sdetable command. In the following example, a view, vw_tanks_facil3, is created on a table named tanks where the facility_id is equal to 3.
sdetable –o create_view –T vw_tankfacil3 –t tanks
–c * –w "facility_id = 3" –u rocket –p gdb
You can query the pg_views catalog to see the view information.
SELECT * FROM pg_views;
| schemaname |
viewname |
viewowner |
definition |
|---|
| rocket |
vw_tankfacil3 |
rocket |
SELECT tanks.objectid, tanks.symbol, tanks.acad_angle, tanks.facility_id, tanks.elevation, tanks.date_installed, tanks.status, tanks.manufacturer, tanks.shape FROM tanks WHERE ((tanks.facility_id)::text = (3)::text); |
Creating a view with SQL and registering with ArcSDE
You can use sdetable to create and register a view, but the same results can be achieved by first using SQL to create the view and manually registering the view with ArcSDE.
First, create the view, this time for facility_id 5. Be sure to include the owner name in the table definition so it can be found if this view is accessed by other users.
CREATE VIEW vw_tankfacil5
AS SELECT *
FROM rocket.tanks
WHERE facility_id = 5;
Now, if a column is added to the base table of the tanks feature class, the view's schema is not affected, as shown below:
ALTER TABLE tanks ADD COLUMN inspect_date timestamp;
Query the pg_views catalog again.
SELECT * FROM pg_views v WHERE v.viewname LIKE '%5';
| schemaname |
viewname |
viewowner |
definition |
|---|
| rocket |
vw_tankfacil5 |
rocket |
SELECT tanks.objectid, tanks.symbol, tanks.acad_angle, tanks.facility_id, tanks.elevation, tanks.date_installed, tanks.status, tanks.manufacturer, tanks.shape FROM tanks WHERE ((tanks.facility_id)::text = (5)::text); |
As you can see in the definition field, the view is unchanged by this alteration of its base table.
The base table of a view need not be registered with ArcSDE unless it provides a geometry (vector), raster, or XML column managed by ArcSDE. In those cases, the base table is already registered with ArcSDE but the view itself must be registered separately. In the vw_tankfacil3 and vw_tankfacil5 views, the geometry column, shape, is included, so you would register the view with ArcSDE.
Registered row ID columns are unique, nonnull integer columns used by ArcSDE to uniquely identify rows in a table or view. Although you can create a view without registering a row ID column, such a view has limited functionality. A registered row ID column is required for queries and other operations that use ArcSDE log files. ArcGIS also uses the registered row ID column for selections, queries, and other operations. If you use a table or view with ArcGIS that has no registered row ID column, ArcGIS may create a temporary row ID column for use with the table or view. This operation can slow down the process of connecting to a table or view.
You should register row ID columns for your views as user-maintained row ID columns. Generally, ArcSDE does not support editing through database views. Therefore, there is no need for ArcSDE to create a sequence to generate new row IDs for the view, which is what ArcSDE does for row ID columns maintained by ArcSDE.
Creating a view with a join
You can create views with joins by including a comma-delimited list of table names as a quoted string after the –t option. The following view returns the objectid, facility_id, inspect_date, and inspector_name by joining two tables, tanks (owned by the connected user) and inspectors (from the schema hbear):
sdetable –o create_view –T vw_tankinspections
–t "tanks,hbear.inspectors"
–c "tanks.objectid,tanks.facility_id,tanks.inspect_date,hbear.inspectors.inspector_name'
–w "tanks.facility_id = hbear.inspectors.facility_id" –u rocket –p gdb
If the tables are not owned by the connected user, the name must be in owner.table format. When using multiple tables, you must qualify the names of each column listed after –c with the name of the table using the format table.column or owner.table.column.
