This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

ALTER FUNCTION statements

Vertica provides ALTER statements for each type of user-defined extension.

Vertica provides ALTER statements for each type of user-defined extension. Each ALTER statement modifies the metadata of a user-defined function in the Vertica catalog:

ALTER statement Extension
ALTER FUNCTION (scalar) User-defined scalar functions (UDSFs)
ALTER AGGREGATE FUNCTION User-defined aggregate functions (UDAFs)
ALTER ANALYTIC FUNCTION User-defined analytic functions (UDAnF)
ALTER TRANSFORM FUNCTION User-defined transform functions (UDTFs)
ALTER statements for user-defined load:
• ALTER SOURCE Load source functions
• ALTER FILTER Load filter functions
• ALTER PARSER Load parser functions

Vertica also provides ALTER FUNCTION (SQL), which modifies the metadata of a user-defined SQL function.

1 - ALTER AGGREGATE FUNCTION

Alters a user-defined aggregate function.

Alters a user-defined aggregate function.

Syntax

ALTER AGGREGATE FUNCTION [[db-name.]schema.]function-name( [ parameter-list ] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function-name``
Name of the SQL function to alter.
arg-list
Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
OWNER TO new-owner
Transfers function ownership to another user.
RENAME TO new-name
Renames this function.
SET SCHEMA new-schema
Moves the function to another schema.

Privileges

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

  • Function owner

  • ALTER privilege on the function

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

For these operations... Schema privileges required...
RENAME TO (rename function) CREATE, USAGE
SET SCHEMA (move function to another schema) CREATE: destination schema
USAGE: current schema

See also

CREATE AGGREGATE FUNCTION

2 - ALTER ANALYTIC FUNCTION

Alters a user-defined analytic function.

Alters a user-defined analytic function.

Syntax

ALTER ANALYTIC FUNCTION [[db-name.]schema.]function-name( [ parameter-list ] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET FENCED boolean-expr
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema
Database and schema. The default schema is public. If you specify a database, it must be the current database.
function-name
Name of the function to alter.
parameter-list
Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
OWNER TO new-owner
Transfers function ownership to another user.
RENAME TO new-name
Renames this function.
SET FENCED { true | false }
Specifies whether to enable fenced mode for this function.
SET SCHEMA new-schema
Moves the function to another schema.

Privileges

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

  • Function owner

  • ALTER privilege on the function

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

Operation Schema privileges required
RENAME TO (rename function) CREATE, USAGE
SET SCHEMA (move function to another schema)

  • CREATE: destination schema

  • USAGE: current schema

See also

CREATE ANALYTIC FUNCTION

3 - ALTER FILTER

Alters a user-defined filter.

Alters a user-defined filter.

Syntax

ALTER FILTER [[db-name.]schema.]function-name( [ parameter-list ] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET FENCED boolean-expr
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema
Database and schema. The default schema is public. If you specify a database, it must be the current database.
function-name
Name of the function to alter.
parameter-list
Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
OWNER TO new-owner
Transfers function ownership to another user.
RENAME TO new-name
Renames this function.
SET FENCED { true | false }
Specifies whether to enable fenced mode for this function.
SET SCHEMA new-schema
Moves the function to another schema.

Privileges

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

  • Function owner

  • ALTER privilege on the function

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

Operation Schema privileges required
RENAME TO (rename function) CREATE, USAGE
SET SCHEMA (move function to another schema)

  • CREATE: destination schema

  • USAGE: current schema

See also

CREATE FILTER

4 - ALTER FUNCTION (scalar)

Alters a user-defined scalar function.

Alters a user-defined scalar function.

Syntax

ALTER FUNCTION [[db-name.]schema.]function-name( [ parameter-list] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET FENCED boolean-expr
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema
Database and schema. The default schema is public. If you specify a database, it must be the current database.
function-name
Name of the function to alter.
parameter-list
Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
OWNER TO new-owner
Transfers function ownership to another user.
RENAME TO new-name
Renames this function.
SET FENCED { true | false }
Specifies whether to enable fenced mode for this function.
SET SCHEMA new-schema
Moves the function to another schema.

Privileges

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

  • Function owner

  • ALTER privilege on the function

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

Operation Schema privileges required
RENAME TO (rename function) CREATE, USAGE
SET SCHEMA (move function to another schema)

  • CREATE: destination schema

  • USAGE: current schema

Examples

Rename function UDF_one to UDF_two:

=> ALTER FUNCTION UDF_one (int, int) RENAME TO UDF_two;

Move function UDF_two to schema macros:

=> ALTER FUNCTION UDF_two (int, int) SET SCHEMA macros;

Disable fenced mode for function UDF_two:

=> ALTER FUNCTION UDF_two (int, int) SET FENCED false;

See also

CREATE FUNCTION (scalar)

5 - ALTER FUNCTION (SQL)

Alters a user-defined SQL function.

Alters a user-defined SQL function.

Syntax

ALTER FUNCTION [[db-name.]schema.]function-name( [arg-list] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function-name
The name of the SQL function to alter.
arg-list
A comma-delimited list of function argument names. If none, specify an empty list.
OWNER TO new-owner
Transfers function ownership to another user.
RENAME TO new-name
Renames this function.
SET SCHEMA new-schema
Moves the function to another schema.

Privileges

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

  • Function owner

  • ALTER privilege on the function

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

For these operations... Schema privileges required...
RENAME TO (rename function) CREATE, USAGE
SET SCHEMA (move function to another schema) CREATE: destination schema
USAGE: current schema

Examples

Rename function SQL_one to SQL_two:

=> ALTER FUNCTION SQL_one (int, int) RENAME TO SQL_two;

Move function SQL_two to schema macros:

=> ALTER FUNCTION SQL_two (int, int) SET SCHEMA macros;

Reassign ownership of SQL_two:

=> ALTER FUNCTION SQL_two (int, int) OWNER TO user1;

See also

6 - ALTER PARSER

Alters a user-defined parser.

Alters a user-defined parser.

Syntax

ALTER PARSER [[db-name.]schema.]function-name( [ parameter-list ] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET FENCED boolean-expr
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema
Database and schema. The default schema is public. If you specify a database, it must be the current database.
function-name
Name of the function to alter.
parameter-list
Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
OWNER TO new-owner
Transfers function ownership to another user.
RENAME TO new-name
Renames this function.
SET FENCED { true | false }
Specifies whether to enable fenced mode for this function.
SET SCHEMA new-schema
Moves the function to another schema.

Privileges

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

  • Function owner

  • ALTER privilege on the function

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

Operation Schema privileges required
RENAME TO (rename function) CREATE, USAGE
SET SCHEMA (move function to another schema)

  • CREATE: destination schema

  • USAGE: current schema

See also

CREATE PARSER

7 - ALTER SOURCE

Alters a user-defined load source function.

Alters a user-defined load source function.

Syntax

ALTER SOURCE [[db-name.]schema.]function-name( [ parameter-list ] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET FENCED boolean-expr
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema
Database and schema. The default schema is public. If you specify a database, it must be the current database.
function-name
Name of the function to alter.
parameter-list
Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
OWNER TO new-owner
Transfers function ownership to another user.
RENAME TO new-name
Renames this function.
SET FENCED { true | false }
Specifies whether to enable fenced mode for this function.
SET SCHEMA new-schema
Moves the function to another schema.

Privileges

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

  • Function owner

  • ALTER privilege on the function

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

Operation Schema privileges required
RENAME TO (rename function) CREATE, USAGE
SET SCHEMA (move function to another schema)

  • CREATE: destination schema

  • USAGE: current schema

See also

CREATE SOURCE

8 - ALTER TRANSFORM FUNCTION

Alters a user-defined transform function.

Alters a user-defined transform function.

Syntax

ALTER TRANSFORM FUNCTION [[db-name.]schema.]function-name( [ parameter-list ] ) {
    OWNER TO new-owner
    | RENAME TO new-name
    | SET FENCED { true | false }
    | SET SCHEMA new-schema
}

Parameters

[db-name.]schema
Database and schema. The default schema is public. If you specify a database, it must be the current database.
function-name
Name of the function to alter.
parameter-list
Comma-delimited list of parameters that are defined for this function. If none, specify an empty list.
OWNER TO new-owner
Transfers function ownership to another user.
RENAME TO new-name
Renames this function.
SET FENCED { true | false }
Specifies whether to enable fenced mode for this function.
SET SCHEMA new-schema
Moves the function to another schema.

Privileges

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

  • Function owner

  • ALTER privilege on the function

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

Operation Schema privileges required
RENAME TO (rename function) CREATE, USAGE
SET SCHEMA (move function to another schema)

  • CREATE: destination schema

  • USAGE: current schema

See also

CREATE TRANSFORM FUNCTION