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

  1. Click on the "New Layer".
  2. Click on the "Database view".
  3. Give the new view a name.
  4. Input the SELECT SQL, which will define the view.
  5. 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

  1. Select a view-layer in the list so that the background becomes gray.
  2. Click Advanced.
  3. The SELECT SQL that defines the view, can be seen in the field View definition.

0 Comments

Add your comment

E-Mail me when someone replies to this comment