User Defined SQL Function in Vertica

               Today, we will have a look at creating another Vertica SQL Function and the clauses and restrictions while creating them, Function are the a good method of reducing your  SQL codes and using the again and again ( Re usability ). First, we will list down the rules for writing a SQL function in Vertica and then explain it with an example.

BASIC SYNTAX

A basic syntax for writing a USer Defined SQL Function in vertica is:

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

 Creating SQL  Functions in Vertica is a bit different from Oracle. Though it follows the same block structure (BEGIN and END), but it comes with a major restriction.

1. 1.       We cannot use a select inside the Block of Vertica SQL function like we can use in Oracle.
2. 2.       For now, there is no support for Variables, loops with User Defined SQL Functions

This limits the usage of SQL function to a greater extent.

EXAMPLE

A Simple User Defined SQL function would look like this.

CREATE FUNCTION myzeroifnull(x INT) RETURN INT

AS BEGIN

RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END);

END;

Best Use of User Defined SQL Functions : The user-defined SQL functions are best used when migrating SQL statements from other databases where those SQL statement references a functions that do not exist in Vertica.

A sample use case here can be, In Oracle there is a string function “REVERSE” which reverses the string in the same order and case.

Vertica does not have such a Reverse Function in its built in library, So we create a User Defined SQL Function “REVERSE” like this.

CREATE FUNCTION PUBLIC.REVERSE(X VARCHAR) RETURN VARCHAR

AS

BEGIN RETURN CASE WHEN LENGTH(X) <= 15

THEN

(SUBSTR(X, 15, 1 ) || SUBSTR(X, 14, 1 ) || SUBSTR(X, 13, 1 ) || SUBSTR(X, 12, 1 ) ||

SUBSTR(X, 11, 1 ) || SUBSTR(X, 10, 1 ) || SUBSTR(X, 9, 1 ) || SUBSTR(X, 8, 1 ) ||

SUBSTR(X, 7, 1 ) || SUBSTR(X, 6, 1 ) || SUBSTR(X, 5, 1 ) || SUBSTR(X, 4, 1 ) ||

SUBSTR(X, 3, 1 )|| SUBSTR(X, 2, 1 )|| SUBSTR(X, 1, 1 ))

ELSE 'STRING OUT OF RANGE' END;

END;

The above function can be used with strings and is limited to strings with length 15. Now we can use this function in any of our select queries.

TRACKING UDF SQL FUNCTION

You can track your UDF function just by using system table v_catalog.user_function

Hope this helps.