ALTER VIEW

Modifies the metadata of an existing view. The changes are auto-committed.

Syntax

ALTER VIEW [[database.]schema.]view { 
    | OWNER TO owner
    | SET SCHEMA schema 
    | { INCLUDE | EXCLUDE | MATERIALIZE } [ SCHEMA ] PRIVILEGES 
}
ALTER VIEW [[database.]schema.]view[,…] RENAME TO new-view-name[,…]

Parameters

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.
view

The view to alter.
SET SCHEMA schema Moves the view from one schema to another.
OWNER TO owner

Changes the view owner.

The new view owner should also have SELECT privileges on the objects that the view references; otherwise the view is inaccessible to that user.
{ INCLUDE
| EXCLUDE 
| MATERIALIZE }
[SCHEMA] PRIVILEGES

Specifies default inheritance of schema privileges for this view:

  • EXCLUDE [SCHEMA] PRIVILEGES (default) disables inheritance of privileges from the schema.
  • INCLUDE [SCHEMA] PRIVILEGES grants the view the same privileges granted to its schema. 

  • MATERIALIZE: Copies grants to the view and creates a GRANT object on the view. This disables the inherited privileges flag on the view, so you can:

    • Grant more specific privileges at the view level
    • Use schema-level privileges as a template
    • Move the view to a different schema
    • Change schema privileges without affecting the view

    • If inherited privileges are disabled at the database level, schema privileges can still be materialized.

See also Setting Privileges Inheritance on Tables and Views in the Administrator's Guide.
RENAME TO

Renames one or more views:

RENAME TO new‑view‑name[,…]

The following requirements apply:

  • The new view name conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
  • If you specify multiple views to rename, the source and target lists must have the same number of names.
  • Renaming a view requires USAGE and CREATE privileges on the schema that contains the view.

Privileges

Non-superuser: USAGE on the schema and one of the following:

  • View owner
  • ALTER privilege on the view

For certain operations, non-superusers must have the following schema privileges:

Schema privileges required… For these operations…
CREATE, USAGE Rename view

CREATE: destination schema
USAGE: current schema

 Move view to another schema

Example

Rename view view1 to view2:

=> CREATE VIEW view1 AS SELECT * FROM t;
CREATE VIEW
=> ALTER VIEW view1 RENAME TO view2;
ALTER VIEW