ALTER VIEW

The ALTER VIEW applies a schema change to a .

The ALTER VIEW statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Required privileges

• To change the schema of a view with ALTER VIEW ... SET SCHEMA, the user must have the DROP on the view and the CREATE privilege on the new schema.
• To rename a view with ALTER VIEW ... RENAME TO, the user must have the DROP on the view and the CREATE privilege on the view’s database.

Syntax

Parameters

Parameter	Description
MATERIALIZED	Rename a .
IF EXISTS	Rename the view only if a view of view\_name exists; if one does not exist, do not return an error.
view\_name	The name of the view to rename. To find existing view names, use: SELECT \* FROM information\_schema.tables WHERE table\_type = 'VIEW';
view\_new\_name	The new name of the view. The name of the view must be unique to its database and follow these . Name changes do not propagate to the table(s) using the view.
schema\_name	The name of the new schema.
role\_spec	The role to set as the owner of the view.

Known limitations

ALTER VIEW does not currently support:

• Changing the statement executed by a view. Instead, you must drop the existing view and create a new view.
• Renaming a view that other views depend on. This feature may be added in the future.

Examples

Rename a view

Suppose you create a new view that you want to rename:

CREATE VIEW money_rides (id, revenue) AS SELECT id, revenue FROM rides WHERE revenue > 50;

WITH x AS (SHOW TABLES) SELECT * FROM x WHERE type = 'view';

  schema_name | table_name  | type | owner | estimated_row_count | locality
--------------+-------------+------+-------+---------------------+-----------
  public      | money_rides | view | demo  |                   0 | NULL
(1 row)

ALTER VIEW money_rides RENAME TO expensive_rides;

RENAME VIEW

WITH x AS (SHOW TABLES) SELECT * FROM x WHERE type = 'view';

  schema_name |   table_name    | type | owner | estimated_row_count | locality
--------------+-----------------+------+-------+---------------------+-----------
  public      | expensive_rides | view | demo  |                   0 | NULL
(1 row)

Note that RENAME TO only changes the name of the view; it cannot move the view to a different database or schema. To change a view’s schema, use the SET SCHEMA clause.

Change the schema of a view

Suppose you want to add the expensive_rides view to a schema called cockroach_labs:

By default, created in the database belong to the public schema:

SHOW CREATE public.expensive_rides;

        table_name       |                                                 create_statement
-------------------------+-------------------------------------------------------------------------------------------------------------------
  public.expensive_rides | CREATE VIEW public.expensive_rides (id, revenue) AS SELECT id, revenue FROM movr.public.rides WHERE revenue > 50
(1 row)

If the new schema does not already exist, :

CREATE SCHEMA IF NOT EXISTS cockroach_labs;

Then, change the view’s schema:

ALTER VIEW expensive_rides SET SCHEMA cockroach_labs;

SHOW CREATE public.expensive_rides;

ERROR: relation "public.expensive_rides" does not exist
SQLSTATE: 42P01

SHOW CREATE cockroach_labs.expensive_rides;

            table_name           |                                                     create_statement
---------------------------------+---------------------------------------------------------------------------------------------------------------------------
  cockroach_labs.expensive_rides | CREATE VIEW cockroach_labs.expensive_rides (id, revenue) AS SELECT id, revenue FROM movr.public.rides WHERE revenue > 50
(1 row)

See also