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.
CREATE [ OR REPLACE ] FUNCTION [[database.]schema.]function‑name( [ arg‑list ] ) RETURN return‑type AS BEGIN RETURN expression; END;
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.
Specifies a schema, by default
If you specify a database, it must be the current database.
Names the SQL function to create, where function‑name conforms to conventions described in Identifiers.
A comma-delimited list of argument names and their data types, specified in this format:
The data type that this function returns.
Specifies the SQL function (function body), where expression can contain built-in functions, operators, and argument names specified in the
A semicolon at the end of the expression is required.
- 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.