Request a Demo
21.4. Deleting an Analytical Table (metastore.remove_table)
Prev UpChapter 21. Analytical TablesHome Next

21.4. Deleting an Analytical Table (metastore.remove_table) #

Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 18.1.

Execute the following command on the Postgres Pro AXE server: 

  SELECT metastore.remove_table('schema_name.table_name', --[force], [cascade]);

Where:

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

  • force: Specifies whether to drop the Postgres Pro view created from the analytical table using the metastore.create_view stored procedure.

    Possible values:

    • true

    • false

    Default value: true.

    Optional parameter.

  • cascade: Specifies whether to drop all views based on the Postgres Pro view created from the analytical table using the metastore.create_view stored procedure.

    Possible values:

    • true

    • false

    Default value: false.

    Optional parameter.

Postgres Pro AXE performs the following actions:

  1. Checks input parameters and user privileges.

  2. Creates a new entry in the pga_snapshot metadata table and sets the end_snapshot field for the analytical table in the pga_table metadata table.

  3. Sets the end_snapshot field for entries associated with the analytical table in other metadata tables.

  4. Performs actions depending on force and cascade values (refer to the table below).

    forcecascadeAction

    false

    false

    		No actions are performed, and views remain unchanged

    false

    true

    		Drops the view and all views based on it. The cascade flag is applied, force is ignored

    true

    false

    		Only drops the view. If there are views based on this view, outputs an SQL error

    true

    true

    		Drops the view and all views based on it. The cascade flag is applied, force is ignored

Example 21.6. Executing the metastore.remove_table Stored Procedure

  SELECT metastore.remove_table('table_example'); --force=true, cascade=false
  SELECT metastore.remove_table('table_example', false); --force=false, cascade=false
  SELECT metastore.remove_table('table_name', false, true); --force=false, cascade=true

Prev Up Next
21.3. Creating a Postgres Pro View for an Analytical Table (metastore.create_view) Home Chapter 22. Columns
epub pdf