Use of database views
You can create a view over a SELECT query, which gives a name to the query that you can refer to like an ordinary table. Views are very useful for example to filter and sort data, without having to create a new table.
Views can be used in almost any place a real table can be used.
Create a view
- Click on the "New Layer".
- Click on the "Database view".
- Give the new view a name.
- Input the SELECT SQL, which will define the view.
- Click "Create."
Both a table and a view must have a primary key. GC2 detects primary keys on tables, but views do not have primary keys, so GC2 falls back on the field "gid". This means that the view should have a field "gid" with unique values. It must also have a geometry field, so it appears in the list of layers (there is no requirement for the naming of geometry fields). If a table is created through GC2, the table will have "gid" as the primary key. So a SELECT like this will work:
SELECT * FROM foo WHERE bar=1
If * is not used in the query, the gid field and geometry field must be queried:
SELECT gid,the_geom FROM foo WHERE bar=1
There may also be made a "gid" field with "As" syntax. Here a view with one single point:
SELECT 1 As gid, ST_SetSRID (ST_Point(-123.365556, 48.428611),4326)::geometry(Point,4326) AS the_geom
Management of views
Views behave like tables in nearly all contexts. In order to know the views from real tables, the views is given a blue background in the layer list.
A difference between views and tables is that data views can not be edited.
Get the definition of a existing view
- Select a view-layer in the list so that the background becomes gray.
- Click Advanced.
- The SELECT SQL that defines the view, can be seen in the field View definition.