Request a Demo
16.3. Creating a Postgres Pro View for an Analytical Table (metastore.create_view)
Prev UpChapter 16. Analytical TablesHome Next

16.3. Creating a Postgres Pro View for an Analytical Table (metastore.create_view) #

You can create a view based on Parquet files of an analytical table to work with this table as with a standard relational table using SELECT commands.

Important

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 12.1.

Execute the following command on the Postgres Pro AXE server: 

  SELECT metastore.create_view('schema_name.table_name', ['Postgres_Pro_schema_name'], [true_or_false]) ON CONFLICT DO NOTHING;

Where:

  • schema_name.table_name: The name of the analytical schema containing the analytical table for which the 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.

    Important

    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 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:

  1. Checks input parameters and user privileges.

  2. Receives metadata of the analytical table and the path to the storage directory with Parquet files of this table.

  3. Generates the CREATE VIEW command with the data source being the result of calling the read_parquet() function.

  4. Executes the CREATE VIEW command.

  5. Grants privileges on the view to the current user.

Example 16.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') ON CONFLICT DO NOTHING;

If an analytical table is contained in the my_schema analytical schema, the view name is my_schema.table_name: 

  SELECT metastore.create_view('my_schema.table_name') ON CONFLICT DO NOTHING;

Example 16.5. How the Third Parameter Is Processed

Suppose you successfully created a view by executing the following command: 

  SELECT metastore.create_view('my_view') ON CONFLICT DO NOTHING;

You did not delete the view by executing the following command: 

  DROP VIEW my_view;

Then, you attempted to create a view with the same name: 

  SELECT metastore.create_view('my_view') ON CONFLICT DO NOTHING;

You receive an error indicating that a view with the same name already exists.

However, if you execute the following command: 

  SELECT metastore.create_view('my_view', '', true) ON CONFLICT DO NOTHING;

There is no error, and the metastore.create_view stored procedure checks whether a view with the same name already exists:

  • If the view does not exist, creates a new view.

  • If the 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.


Prev Up Next
16.2. Renaming an Analytical Table (metastore.rename_table) Home 16.4. Deleting an Analytical Table (metastore.remove_table)
epub pdf