ALTER VIEW

Redefines a view.

---

Syntax

ALTER VIEW [ IF EXISTS ] <name> ALTER [ COLUMN ] <column_name> SET DEFAULT <expression>

ALTER VIEW [ IF EXISTS ] <name> ALTER [ COLUMN ] <column_name> DROP DEFAULT

ALTER VIEW [ IF EXISTS ] <name> OWNER TO <new_owner>

ALTER VIEW [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER VIEW [ IF EXISTS ] <name> SET SCHEMA <new_schema>

ALTER VIEW [ IF EXISTS ] <name> SET ( <view_option_name> [= <view_option_value>] [, ... ] )

ALTER VIEW [ IF EXISTS ] <name> RESET ( <view_option_name> [, ... ] )

---

Description

You can run ALTER VIEW to redefine the view that you own.

This command cannot be used to modify the query that defines the view. If you want to achieve this, execute CREATE OR REPLACE VIEW.

When you use ALTER VIEW to change the schema of a view, you must be the view owner and have the CREATE privilege on the new schema.

When you use ALTER VIEW to alter the owner of a view, you must have the direct or indirect membership in the new owning role. In addition, the new owning role must have the CREATE privilege on the schema of the view.

---

Parameters

• IF EXISTS

  If you include IF EXISTS in your command and the specified view does not exist, no error will be reported. A notice will be issued instead.

• <name>

  The name of the view. You can specify the view name with the schema qualification.

• SET DEFAULT

  Sets the default value for the column.

• DROP DEFAULT

  Drops the default value for the column.

• <new_owner>

  The new owner of the view.

• <new_name>

  The new name of the view.

• <new_schema>

  The new schema to whch the view belongs.

• SET | RESET

  Specifies to set or reset a view option. Supported view options include:

  • check_option specifies the check option of the view, which can be set to local or cascaded.
  • security_barrier specifies the security-barrier property of the view, which can be set to a Boolean value.

---

Examples

Rename view myview to my_view:

ALTER VIEW myview RENAME TO my_view;

Attach a default column value to an updatable view:

1. Create a table named main_table:

   CREATE TABLE main_table (product_id varchar, product_date date);

2. Create a view named products_view:

   CREATE VIEW products_view AS SELECT * FROM main_table;

3. Add a default value for column product_date in products_view:

   CREATE OR REPLACE VIEW products_view AS 
   SELECT product_id, COALESCE(product_date, CURRENT_DATE) AS product_date
   FROM main_table;

4. Insert a new row into products_view without specifying a product_date:

   INSERT INTO products_view(product_id) VALUES('PROD003');

   In this example, the current date will be used.

---

SQL standard compatibility

ALTER VIEW is a Relyt extension to the SQL standard.