CREATE/REPLACE RECURSIVE VIEW Statement | Teradata Vantage - CREATE RECURSIVE VIEW and REPLACE RECURSIVE VIEW - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Creates or replaces a recursive view definition.

Any recursive view defined on a row-level security-protected table must include the row-level security constraint columns in the view definition.

ANSI Compliance

CREATE RECURSIVE VIEW is compliant with the ANSI SQL:2011 standard.

REPLACE RECURSIVE VIEW is a Teradata extension to the ANSI SQL:2011 standard.

Required Privileges

You must have the CREATE VIEW privilege to create a new recursive view using the CREATE RECURSIVE VIEW syntax or to create a new recursive view using the REPLACE RECURSIVE VIEW syntax. You can only use the REPLACE syntax to create a recursive view if that view does not already exist.

To replace an existing recursive view, you must have the DROP VIEW privilege on the view or its containing database.

The creator receives the DROP VIEW and SELECT privileges on the newly created recursive view WITH GRANT OPTION.

If a user other than the owner needs to access a recursive view, then all of the relevant privileges required by the immediate owner of the recursive view to access underlying tables and views must also be held by the user of the recursive view WITH GRANT OPTION.

You cannot update a base table through a recursive view. You cannot reference a recursive view using any of the following SQL DML statements:

  • DELETE
  • INSERT
  • MERGE
  • UPDATE

Because of this, there are no privileges associated with update operations on a recursive view.

Privileges Granted Automatically

When you create a new view, the following privileges are automatically granted to the view:
  • DELETE
  • DROP VIEW
  • GRANT
  • INSERT
  • SELECT
  • UPDATE