Alters the SELECT query of an existing, non-materialized view.

Syntax

ALTER VIEW viewname[(col1x,...)] AS SELECT [* | col1,...] FROM system.database.schema.table [WHERE clauses];
ALTER VIEW viewname [ADD | DROP] PROJECTION viewprojection
AUTHENTICATED AS [user | role]
AS SELECT * FROM system.database.schema.table

Arguments

  • viewname: The name of an existing view.

  • col1,… - Optional list of target columns for view data, renaming those in the SELECT statement.

  • SELECT - Specify source columns if taking a subset of data, and if target columns are specified. See SELECT.

View Projections

  • ADD PROJECTION - create a projection view with additional clauses.

  • DROP PROJECTION - drop an existing projection

  • AUTHENTICATED AS - view uses specified user or role authentication to query database table.

Additional Information

  • To grant access to the view projection use a GRANT SELECT command. For example:
GRANT SELECT ON hub.home.views."viewname::projection" TO [USER | ROLE];

Examples

Alter view and filter data by createDate table values.

ALTER VIEW customer2012 AS SELECT * FROM MYSQL.inventory.customer WHERE createDate > '2012-01-01';

Alter view on single table, choosing subset of columns

alter view doctest_view
AS SELECT id, name, ssn FROM dataxchange.postgres.public.doctest

Alter view, rename selected columns.

alter view doctest_view (myid, firstname, code)
AS SELECT id, name, ssn FROM dataxchange.postgres.public.doctest

Add view projection on doctest_view, selecting specific columns and renaming them.

alter view doctest_view
ADD PROJECTION doctest_projection
(myid, firstname, code)
as select id, name, ssn from dataxchange.postgres.public.doctest;

See Also