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.

Syntax

CREATE [ OR REPLACE ] FUNCTION
... [[database.]schema.]function( [ argname argtype[,…] ] )
... RETURN return‑type
... AS 
... BEGIN
...... RETURN expression;
... END;

Parameters

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

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

Specifies the name of the argument.
argtype

Specifies the data type for argument that is passed to the function. Argument types must match Vertica type names. See SQL Data Types.
return‑type

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.

Privileges

Non-superuser:

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

Notes

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

Example

See Creating User-Defined SQL Functions

See Also