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 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 it's not possible to make references between views, only between a view and other tables.

3.7.1. View references

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:

The elements td.id_a_table_a, ta.* are column identifiers and (SELECT random()) is a simple SQL expression. The next elements, schema_a.table_d and public.table_a are the table identifiers. The elements id, _expr, td, and 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 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 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 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 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: Expression and 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: SELECT, FROM and 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.