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

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

Required privileges: Postgres Pro AXE administrator only. For a full list of stored procedures and privileges, refer to Section 12.1.

Execute the following command on the Postgres Pro AXE server: 

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

Where:

  • schema_name.table_name: The name of the analytical schema containing the analytical table that must be deleted, 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 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 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).

    force

    cascade

    Action

    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, returns an SQL error

    true

    true

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

Example 16.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
16.3. Creating a Postgres Pro View for an Analytical Table (metastore.create_view) Home Chapter 17. Columns
epub pdf