25.3. Creating a Postgres Pro View For an Analytical Table (metastore.create_view) #
You can create a Postgres Pro view based on Parquet files of an analytical table to work with this table as with a standard relational table using SELECT commands.
Important
Postgres Pro views can be recreated only during periods of zero user activity to prevent potential data loss.
Before creating a Postgres Pro view, ensure that the analytical table contains Parquet files. If it does not, Postgres Pro AXE returns an error.
Required privileges:
SELECTprivilege on the analytical table.CREATEprivilege on the Postgres Pro schema that contains the view.
For more information about stored procedures and privileges, refer to Section 22.1.
Execute the following command:
SELECT metastore.create_view('schema_name.table_name', ['Postgres_Pro_schema_name'], [true_or_false]);
Where:
schema_name.table_name: The name of the analytical schema containing the analytical table for which the Postgres Pro view is created, followed by the table name itself.You can omit the schema name and simply specify the table name (without a dot). In this case, the default
mainanalytical schema is used.Postgres_Pro_schema_name: The name of the Postgres Pro schema where the view is created.Important
This is not the name of an analytical schema from the
pga_schemametadata table.Default value: '' (empty string).
Optional parameter. If you do not specify it, the schema is selected automatically by Postgres Pro. Typically, it is the
publicschema.true_or_false: Specifies whether to drop an existing Postgres Pro view with the same name in the specified schema before creating a new view.Default value:
false.Optional parameter.
Postgres Pro AXE performs the following actions:
Verifies input parameters and user privileges.
Receives metadata of the analytical table and the path to the storage directory with Parquet files of this table.
Generates the
CREATE VIEWcommand with the data source being the result of calling theread_parquet()function.Executes the
CREATE VIEWcommand.Grants privileges on the Postgres Pro view to the current user.
Example 25.4. Executing the metastore.create_view stored procedure
If an analytical table is created without specifying an analytical schema, the table is contained in the default main analytical schema, and the name of the Postgres Pro view is also table_name:
SELECT metastore.create_view('table_name');
If an analytical table is contained in the my_schema analytical schema, the Postgres Pro view name is my_schema.table_name:
SELECT metastore.create_view('my_schema.table_name');
Example 25.5. How the third parameter is processed
Suppose you successfully created a Postgres Pro view by executing the following command:
SELECT metastore.create_view('my_view');
You did not delete the Postgres Pro view by executing the following command:
DROP VIEW my_view;
Then, you attempted to create a Postgres Pro view with the same name:
SELECT metastore.create_view('my_view');
You receive an error indicating that a Postgres Pro view with the same name already exists.
However, if you execute the following command:
SELECT metastore.create_view('my_view', '', true);
There is no error, and the metastore.create_view stored procedure checks whether a Postgres Pro view with the same name already exists:
If the Postgres Pro view does not exist, creates a new view.
If the Postgres Pro view exists, checks whether any command in the DBMS is currently using this view:
If there is no such command, drops the existing view and creates a new one.
If a command is using the view, terminates with a message that the existing view cannot be safely dropped.