3.8. Views
Views are objects generated from queries that reference one or more tables or even use ordinary SQL expressions to compose their data. Once a view is created, it'll work like a table in the way that the data is stored and retrieved. In PostgreSQL, there are two kinds of views: the ordinary ones and the materialized ones. The ordinary view is not physically materialized, instead, the definition query is executed every time the view is referenced in another query. For the materialized view, its definition query is executed and used to populate it at the time the command is issued, making it possible to refresh its data later by running the special instruction REFRESH MATERIALIZED VIEW.
Despite the way that PostgreSQL treats both views and materialized views, in pgModeler, they are treated as a single object handled by the same dialog. In that dialog, it's 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 precisely the column names of a view, which means that referencing a view from another one is a limited feature, while referencing tables from a view is completely possible.
| Attribute | Description |
|---|---|
Tag |
This attribute is used to configure the tag object associated with 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 every time 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 needs to be refreshed using the REFRESH MATERIALIZED VIEW command. |
With no data |
Indicates that the materialized view should not be populated at creation time. |
Options |
The combobox controls the behavior of automatically updatable views regarding how INSERT, UPDATE, and MERGE commands on the view will be checked to ensure that new rows satisfy the view-defining condition. The values in this combobox are: No check, cascaded, and local. The value No check disables completely any checking over the view. Cascade causes new rows to be checked against the conditions of the view and all underlying base views. Local causes rows to be only checked against the conditions defined directly in the view itself. Security barrier should be used if the view is intended to provide row-level security. Security_invoker causes the underlying base relations to be checked against the privileges of the user of the view rather than the view owner. |
References |
This tab is used to create view references. These references are used to construct the view's SQL definition. More details about references can be seen in 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 that produces the view as the user configures the attributes on the form. |
3.7.1. View references
To configure views, one can freely type SQL commands with special placeholder variables enclosed by {} that is called references. Any reference in the typed SQL command that defines the view will be replaced by the referenced object's name, which can be columns, functions, procedures, tables, foreign tables, and views. Once the view is created, pgModeler will create relationships between it and the referenced tables (foreign tables and views).
The image below gives an idea of how the view structure works. The view's SQL definition has two references named {ta} and {td}, where, respectively, point to the tables public.table_a and schema_a.table_d. Also, in the code below, there are two other forms of references, @{ta} and @{td}, which are replaced by the respective reference aliases.
So once the view's SQL code is generated, the referenced object names as well as the reference aliases are used, resulting in the code below.
Views can deduce their columns from the referenced objects (relations and columns), but the user can also hint to pgModeler that a referenced object is a column provider. In that case, when configuring a reference in the References tab, make sure to check the option Use column(s). Based on that, when creating the graphical representation of the view in the model, pgModeler will get each flagged object's columns and create a copy of them in the view.
The image above shows a view referencing another view. To achieve a similar result, just create as many references to views as needed when designing a new view. The only downside of this feature is that you can't reference individual view columns like in tables or foreign tables, so if you check the Use column(s) option in a reference to a view, all columns of that object will be copied to the new view.