Vertica Analytics Platform Version 9.2.x Documentation

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.

This topic describes how to use CREATE FUNCTION to create a SQL function. If you want to create a user-defined scalar function (UDSF), see CREATE FUNCTION (UDF).

In addition, if you want to see how to create a user-defined transform function (UDTF), see CREATE TRANSFORM FUNCTION.

Syntax

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

Parameters

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

rettype

Specifies the data type to be returned by the function.

RETURN expression;

Specifies the SQL function (function body), which must be in the form of ‘RETURN expression.’ 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.

Only one RETURN expression is allowed in the CREATE FUNCTION definition. FROM, WHERE, GROUP BY, ORDER BY, LIMIT, aggregation, analytics, and meta function are not allowed.

Privileges

  • To create a function, the user must have CREATE privilege on the schema to contain the function and USAGE privilege on the library containing the function.
  • To use a function, the user must have USAGE privilege on the schema that contains the function and EXECUTE privileges on the function.
  • To drop a function, the user must either be a superuser, the owner of the function, or the owner of the schema which contains the function.

See GRANT (User Defined Extension) and REVOKE (User Defined Extension).

Notes

  • A SQL function can be used anywhere in a query where an ordinary SQL expression can be used, except in the table partition clause or the projection segmentation clause.
  • 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 you want to change the body of a SQL function, use the CREATE OR REPLACE syntax. The command replaces the function with the new definition. If you change only the argument name or argument type, the system maintains both versions under the same function name. See Examples section below.
  • 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 a 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.
  • You can return a list of all SQL functions by querying the system table V_CATALOG.USER_FUNCTIONS and executing the vsql meta-command \df. Users see only the functions on which they have EXECUTE privileges.

Example

This following statement creates a SQL function called myzeroifnull that accepts an INTEGER argument and returns an INTEGER result.

=> CREATE FUNCTION myzeroifnull(x INT) RETURN INT
   AS BEGIN 
     RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END); 
   END;

You can use the new SQL function (myzeroifnull) anywhere you use an ordinary SQL expression. For example, create a simple table:

=> CREATE TABLE tabwnulls(col1 INT);
=> INSERT INTO tabwnulls VALUES(1);
=> INSERT INTO tabwnulls VALUES(NULL);
=> INSERT INTO tabwnulls VALUES(0);
=> SELECT * FROM tabwnulls;
 a
---
 1
 0
(3 rows)

Use the myzeroifnull function in a SELECT statement, where the function calls col1 from table tabwnulls:

=> SELECT myzeroifnull(col1) FROM tabwnulls;
 myzeroifnull 
--------------
          1
          0
          0
(3 rows)

Use the myzeroifnull function in the GROUP BY clause:

=> SELECT COUNT(*) FROM tabwnulls GROUP BY myzeroifnull(col1);
 count
-------
     2
     1
(2 rows)

If you want to change a SQL function's body, use the CREATE OR REPLACE syntax. The following command modifies the CASE expression:

=> CREATE OR REPLACE FUNCTION zerowhennull(x INT) RETURN INT    AS BEGIN 
     RETURN (CASE WHEN (x IS NULL) THEN 0 ELSE x END); 
   END;

To see how this information is stored in the Vertica catalog, see Viewing Information About SQL Functions in Extending Vertica.