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 same name and argument type 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
    [[database.]schema.]function‑name( [ arg‑list ] )
    RETURN return‑type
    AS 
    BEGIN
       RETURN expression;
    END;

Parameters

OR REPLACE

Specifies to overwrite an existing function of the same name and arguments. If you only change the function arguments, Vertica ignores this option and maintains both functions under the same name.

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.
function‑name

Names the SQL function to create, where function‑name 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‑name.
  • argtype is argname's data type.
return‑type

The data type that this function returns.
RETURN expression

Specifies the SQL function (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.

The CREATE FUNCTION definition 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 an 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, an 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