Uncategorized

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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s