CREATE/REPLACE MACRO Syntax | Teradata Vantage - CREATE MACRO and REPLACE MACRO - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

Defines a set of statements that are frequently used or that perform a complex operation. The statements in the macro body are submitted when the macro is invoked by a subsequent EXECUTE statement.

REPLACE MACRO redefines an existing macro. If the specified macro does not exist, REPLACE MACRO creates a new macro with that name.

ANSI Compliance

CREATE MACRO and REPLACE MACRO are Teradata extensions to the ANSI SQL:2011 standard.

Required Privileges: CREATE MACRO

You must have the CREATE MACRO privilege on the containing database or user in which the macro is to be created. The creator of a macro is automatically granted the DROP MACRO and EXECUTE privileges WITH GRANT OPTION on the macro.

The user creating a macro must have the privileges for all statements it performs.

Once a macro has been created, its immediate owner is the database or user in which it is contained, not the user who created it. The immediately owning database or user must have all the appropriate privileges for executing the macro, including WITH GRANT OPTION.

Access to data via a macro is controlled by the privileges of its immediate owner, not by the privileges of its creator. This can be a security issue. See Teradata Vantage™ - SQL Data Control Language, B035-1149 for details.

The user who performs a macro need not be aware of the tables affected by its performance.

Required Privileges: REPLACE MACRO

You must have the DROP MACRO privilege on an existing macro or its containing database or user to replace it.

You must have the CREATE MACRO privilege on the macro or its containing database or user if it does not already exist.

The user replacing a macro must have the privileges for all statements it performs.

Once a macro has been replaced, its immediate owner is the database in which it exists, not the user who replaced it. The immediately owning database must have all the appropriate privileges for executing the macro, including WITH GRANT OPTION.

Privileges Granted Automatically

The following privileges are granted automatically to the creator of a macro:
  • DROP MACRO
  • EXECUTE
  • GRANT