ALTER VIEW

The ALTER VIEW statement applies a schema change to a view.

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

Required privileges

To set or reset security_invoker, the user must own the view.
To change the schema of a view with ALTER VIEW ... SET SCHEMA, the user must have the DROP privilege on the view and the CREATE privilege on the new schema.
New in v26.3: To rename a view with ALTER VIEW ... RENAME TO, the user must have the DROP privilege on the view and the CREATE privilege on the view’s schema.

Syntax

Parameters

Parameter	Description
`MATERIALIZED`	Rename a materialized view.
`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 identifier rules. 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.
`security_invoker`	Sets whether to check privileges on the underlying tables as the querying user (`true`) or the view owner (`false`). `SET (security_invoker)` is equivalent to `SET (security_invoker = true)`. `RESET (security_invoker)` restores the default behavior, where a view checks privileges on the underlying tables as the view owner. This option applies only to non-materialized views.

Known limitations

ALTER VIEW does not currently support:

Changing the SELECT 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, unqualified views 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 it:

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)

Set or reset `security_invoker`

By default, a view checks privileges on the underlying tables as the view owner. To change the view to use the querying user’s privileges instead, include the security_invoker option:

ALTER VIEW user_accounts SET (security_invoker = true);

To restore the default behavior:

ALTER VIEW user_accounts RESET (security_invoker);

Statements

Syntax

Data Types

Spatial Data

Constraints

Required privileges

Syntax

Parameters

Known limitations

Examples

Rename a view

Change the schema of a view

Set or reset `security_invoker`

See also

​Required privileges

​Syntax

​Parameters

​Known limitations

​Examples

​Rename a view

​Change the schema of a view

​Set or reset security_invoker

​See also

Required privileges

Syntax

Parameters

Known limitations

Examples

Rename a view

Change the schema of a view

Set or reset `security_invoker`

See also