3.8. Views

Views are objects generated from queries which reference one or more tables or even use ordinary SQL expressions to compose their data. Once created a view it'll work like a table in the way data is stored and retrieved. In PostgreSQL there're two kinds of views: the ordinary ones and the materialized ones. The ordinary view is not physically materialized, instead, the query is run every time the view is referenced in a query. For the materialized view its definition query is executed and used to populate it at the time the command is issued being possible to refresh its data later by running the special instruction REFRESH MATERIALIZED VIEW.

Despite the way PostgreSQL treat both views and materialized views, in pgModeler they are treated as a single object handled by the same dialog. In that dialog its possible to create a view or a materialized view just by checking the respective option and setting the needed attributes to correctly create both kinds. Currently, the implementation of views in pgModeler can't infer the column names of a view which means that its not possible to reference a view column from another one by using the mechanisms provided by the tool.

Attribute Description
Tag This attribute is used to configure the tag object associated to the view. Tags are used to graphically separate tables and views even if they are owned by the same schema.
Mode Mode in which the view is constructed. Ordinary means that the view is a common one (refreshed everytime it is referenced). Recursive constructs a recursive view. Materialized indicates that a materialized view must be created, which means that the view is populated once and need to be refreshed using REFRESH MATERIALIZED VIEWcommand.
With no data Indicates that the materialized view should not be populated at creation time.
References This group of fields is used to create view references. These references are used to construct the view's SQL definition. More details about references can be seen on the next subsection.
Triggers This tab is used to configure triggers to be assigned to the view.
Rules This tab is used to configure rules to be assigned to the view.
Indexes This tab is used to configure indexes to be assigned to the view.
Table Expression This tab is used to configure auxiliary queries to be executed together with the view's SQL definition. These queries are commonly known as Common Table Expression as stated by the PostgreSQL docs.
Code Preview This tab provides a preview of SQL code which produces the view as the user configures the attributes on the form.

3.7.1. View references

In order to keep track of column's and table's names pgModeler constructs views in the database model using objects called references. These objects are responsible to generate portions of the DDL that, in the end, will form the whole view's definition. The main advantage to use these references is that user don't need to write the complete command to generate the view, also any reference to columns and tables are automatically updated when the referenced objects have their names changed. Generally, a view's definition is a simple select command as follow:

The elements col1, col2 are column identifiers and now() + interval '1 hour' is a simple SQL expression. The next elements, tableA and tableB are the table identifiers. The elements ca, cb, expr, ta and tb are the aliases assigned to their respective identifiers. Finally, boolean_expr is a filtering clause being a SQL expression that returns a boolean result.

Looking carefully to the complete command above we can notice that there are elements between keywords SELECT and FROM, FROM and WHERE, and after the WHERE. In pgModeler these elements are the view references and they behave differently depending on the portion of the SQL definition they are placed. Let's detail this situation based upon the image below.

In the form above we use the field Reference Type to configure a reference from a column or expression. The next field Used in is where the reference is placed in the view's definition, we will talk in details about this field soon.

When configuring a column reference, the fields Table and Column are used to select the column(s) to be used in the reference. There is one important thing to note here: these two object pickers are mutually exclusive, this means, if a table is selected the column picker is cleared and it is assumed that all columns from the table will be referenced (this is the same as * on a SELECT command); if a column is selected then only that column will be referenced and the table picker will be updated with the column's owner table. The user can specify aliases for either columns and tables, if no alias is specified then the involved objects will be referenced by their full names.

For expression references, there are only two fields to be used: Expression and Expression Alias. The first is the expression that defines the reference itself and the second one is the alias of that expression. In both cases, for column reference or expression reference, the aliases are used to generate the view's column names in PostgreSQL.

Returning to the field Used in, user can choose multiple places in the definition where to insert the reference: SELECT..., FROM... and WHERE... and End expression. The End expression option is used to create expressions to be used in GROUP BY / HAVING clauses. There is a fifth option called View Definition, available only for an expression reference, that enables the user to create the entire view's definition from a single expression (a fully detailed SELECT command). In this case the user will lose the advantage of tracking the column's and table's names and any attempt to create additional references will raise errors.

Finally, different SQL statements will be generated depending on the place user inserts the desired reference. The table below details this situation and the subsequent image represents the view created from the SQL command used in this section.

Place Reference type
ColumnExpression
SELECT...table.column AS col_aliasexpression AS expr_alias
FROM... table AS tab_aliasexpression
WHERE... table.columnexpression
End expressiontable.columnexpression



Jun 15, 2018 at 15:39