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
... [schema.]function-name ( [ argname argtype  [, ...] ] )
... RETURN rettype
... AS 
... BEGIN
...... RETURN expression;
... END;

Parameters

myschema.thisDbObject

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.