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.
Postgres Pro views can be recreated only during periods of zero user activity to prevent potential data loss.
Before performing this instruction, ensure that the analytical table contains Parquet files. If it does not, Postgres Pro AXE returns an error.
Required privileges:
SELECT privilege on the analytical table.
CREATE privilege 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 main analytical schema is used.
Postgres_Pro_schema_name: The name of the
Postgres Pro schema where the view is created.
This is not the name of an analytical schema from the
pga_schema
metadata 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 public schema.
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 VIEW command with the data
source being the result of calling the
read_parquet() function.
Executes the CREATE VIEW command.
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.