CREATE FUNCTION (SQL)

Stores SQL expressions as functions for use in queries. User-defined SQL functions are useful for executing complex queries and combining Vertica built-in functions. You simply call the function in a given query. If multiple SQL functions with the same name and argument types are in the search path, Vertica calls the first match that it finds.

SQL functions are flattened in all cases, including DDL.

Syntax

CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ]
    [[database.]schema.]function( [ arg_list ] )
    RETURN return_type
    AS 
    BEGIN
       RETURN expression;
    END;

Arguments

OR REPLACE

If a function of the same name and arguments exists, replace it. If you only change the function arguments, Vertica ignores this option and maintains both functions under the same name.

OR REPLACE and IF NOT EXISTS are mutually exclusive.
IF NOT EXISTS

If a function with the same name and arguments exists, return without creating the function.

OR REPLACE and IF NOT EXISTS are mutually exclusive.
[database.]schema

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

SQL function to create, where function conforms to conventions described in Identifiers.

arg_list

A comma-delimited list of argument names and their data types, specified in this format:

argname argtype[,...]

where:

  • argname is the name of an argument passed to function.
  • argtype is argname's data type.
return_type

The data type that this function returns.
RETURN expression

The SQL function body, where expression can contain built-in functions, operators, and argument names specified in the CREATE FUNCTION statement.

A semicolon at the end of the expression is required.

CREATE FUNCTION allows only one RETURN expression. Return expressions do not support the following:

  • FROM, WHERE, GROUP BY, ORDER BY, and LIMIT clauses
  • Aggregation, analytics, and meta-functions

Privileges

Non-superuser:

  • CREATE privilege on the function's schema
  • USAGE privilege on the function's library

Strictness and Volatility

Vertica infers the strictness and volatility (stable, immutable, or volatile) of a SQL function from its definition. Vertica then determines the correctness of usage, such as where an immutable function is expected but a volatile function is provided.

SQL Functions and Views

You can create views on the queries that use SQL functions and then query the views. When you create a view, a SQL function replaces a call to the user-defined function with the function body in a view definition. Therefore, when the body of the user-defined function is replaced, the view should also be replaced.

Examples

See Creating User-Defined SQL Functions

See Also