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 precisely the column names of a view which means that it's not possible to make references between views, only between a view and other tables.
||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 in which the view is constructed.
||Indicates that the materialized view should not be populated at creation time.|
||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 on the next subsection.|
||This tab is used to configure triggers to be assigned to the view.|
||This tab is used to configure rules to be assigned to the view.|
||This tab is used to configure indexes to be assigned to the view.|
||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.|
||This tab provides a preview of SQL code which produces the view as the user configures the attributes on the form.|
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:
col2 are column identifiers and
now() + interval '1 hour' is a simple SQL expression. The next elements,
tableB are the table identifiers. The elements
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
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
Ref. type to configure a reference from a table, 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
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:
Expr. 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, the user can choose multiple places in the definition where to insert the reference:
WHERE or in the
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 the automatic tracking of the column's and table's names and any attempt to create additional references will raise errors. Fortunately, in newer versions, when using
View defintion option to define a view there's the possibility to provide the column names that the view's definition command may create. This will help to better visualize views when they are constructed from a single definition expression. Note that those custom columns can't be referenced in any way in the view or from outside it.
Additionally to custom columns the user is able to provide the referenced tables which will make pgModeler create relationships between the view and the tables. This is another mechanism which helps the user to understand the view's construction as well how it links to other tables on the model. Either custom columns and referenced tables are more useful when reverse engineering a view from a database, in this case, pgModeler will be able to correctly identify the columns and referenced tables by reading the system catalogs and placing these objects at the related sections in the view's reference.
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.
|table.column AS col_alias||expression AS expr_alias|
|table AS tab_alias||expression|
** View DDL **
** Materialized view DDL **