ALTER VIEW

The ALTER VIEW applies a schema change to a .

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

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 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.
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 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)

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 :

ALTER VIEW user_accounts SET (security_invoker = true);

To restore the default behavior:

ALTER VIEW user_accounts RESET (security_invoker);

See also