Views are objects generated from queries that 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 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 being 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 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 with 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 in 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 names and table names, pgModeler constructs views in the database model using objects called references. These objects are responsible for generating portions of the DDL that, in the end, will form the whole view's definition. The main advantage of using these references is that users don't need to write the complete command to generate the view, also any reference to columns and tables is automatically updated when the referenced objects have their names changed. Generally, a view's definition is a simple select command like the following:
ta.* are column identifiers and
(SELECT random()) is a simple SQL expression. The next elements,
public.table_a are the table identifiers. The elements
ta are the aliases assigned to their respective identifiers. Finally,
ta.id > 100 is a filtering clause being a SQL expression that returns a boolean result.
Looking carefully at the complete command above we 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 on the image below.
In the form above we use the field
Ref. type to configure a reference to a table, column, or expression. The next field
Used in is where the reference is placed in the view's definition, we will describe this in detail 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 a
SELECT * FROM table 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 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 the `View definition 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.
Besides 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 that helps the user to understand the view's construction as well as how it links to other tables in 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 on the related sections in the view's reference.
Finally, different SQL statements will be generated depending on the place the 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|