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)

Statements

Syntax

Data Types

Spatial Data

Constraints

Required privileges

Syntax

Parameters

Known limitations

Examples

Rename a view

Change the schema of a view

See also

​Required privileges

​Syntax

​Parameters

​Known limitations

​Examples

​Rename a view

​Change the schema of a view

​See also

Required privileges

Syntax

Parameters

Known limitations

Examples

Rename a view

Change the schema of a view

See also