Vertica SQL Functions

Hi all,

Unlike other databases, vertica is not so flexible when it comes to User defined functions.

Function can be defined through two ways in Vertica
1. Vertica SQL Function
2. Vertica UDF ( User Defined Function )

Today, we will be looking at an example of 1 type Vertica SQL Function.

Vertica SQL Function

They can be defined using the CREATE FUNCTION statement and with a return type mandatory

Syntax for SQL Function

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


Now an example to elaborate the above syntax

CREATE FUNCTION ADD_DAYS(x TIMESTAMP, y INT) RETURN TIMESTAMP
AS BEGIN
RETURN ( CAST (x AS TIMESTAMP) + y )
END;

This example will create ADD_DAYS Function which adds the number of days in int to a date / timestamp value.

The arguments defined are of two types TIMSTAMP and INT.

So examples of how this function works

EXAMPLE USAGE :

nnani=> select ADD_DAYS('2013-01-20',1);
      ADD_DAYS
---------------------
 2013-01-21 00:00:00
(1 row)

nnani=> select ADD_DAYS('2013-01-20',60);
      ADD_DAYS
---------------------
 2013-03-21 00:00:00
(1 row)