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

25.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 22.1.

Execute the following command: 

  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. Verifies input parameters and user privileges.

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

  3. Sets the end_snapshot value 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 25.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
25.3. Creating a Postgres Pro View For an Analytical Table (metastore.create_view) Home Chapter 26. Columns
epub pdf