CREATE FUNCTION (SQL Functions)
Lets you store SQL expressions as functions in Vertica for use in queries. These functions are useful for executing complex queries or combining Vertica built-in functions. You simply call the function name you assigned.
CREATE [ OR REPLACE ] FUNCTION ... [[database.]schema.]function( [ argname argtype[,…] ] ) ... RETURN return‑type ... AS ... BEGIN ...... RETURN expression; ... END;
|OR REPLACE||Specifies to replace an existing function with a new definition if you modify the body of a SQL function. If you change only an argument name or argument type, the system maintains both versions under the same function name. For an example, see Viewing Information About User-Defined SQL Functions.|
Specifies a schema, by default
If you specify a database, it must be the current database.
Specifies a name for the SQL function to create, where function conforms to conventions described in Identifiers. When using more than one schema, specify the schema that contains the function, as noted above.
Specifies the name of the argument.
Specifies the data type for argument that is passed to the function. Argument types must match Vertica type names. See SQL Data Types.
Specifies the data type to be returned by the function.
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
- SQL Macros are flattened in all cases, including DDL.
- 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.
- If multiple SQL functions with same name and argument type are in the search path, the first match is used when the function is called.
- The strictness and volatility (stable, immutable, or volatile) of an SQL Macro are automatically inferred from the function's definition. Vertica then determines the correctness of usage, such as where an immutable function is expected but a volatile function is provided.