Vertica Analytics Platform Version 9.2.x Documentation


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.


... [[database.]schema.]function( [ argname argtype[,…] ] )
... RETURN return‑type
... AS 
...... 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 public. If schema is any schema other than public, you must supply the schema name. For example:


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.

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. FROM, WHERE, GROUP BY, ORDER BY, LIMIT, aggregation, analytics, and meta function are not allowed.



  • 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.


See Creating User-Defined SQL Functions