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)

Read more ...

History Of Vertica

Hello All,

This is my first blog with Vertica-database blogs.
I think starting with the history of Vertica database could be the best option at this time.

For people new, Hope you all find this blog interesting.

Vertica was founded in 2005 by database researcher Michael Stonebraker and Andrew Palmer.
Vertica is the only pure Columnar Database existing till date.

Did you know Vertica before commercializing was called as  C- Store database.
Yes  C- Store ( Column Store )
The C-Store project was a collaboration between MIT, Yale, Brandeis University. Brown University, and UMass Boston .


A Short note to C -Store

C-Store is a read-optimized relational DBMS that contrasts sharply with most current systems, which are write-optimized. Among the many differences in its design are: storage of data by column rather than by row, careful coding and packing of objects into storage including main memory during query processing, storing an overlapping collection of column-oriented projections, rather than the current fare of tables and indexes, a non-traditional implementation of transactions which includes high availability and snapshot isolation for read-only transactions, and the extensive use of bitmap indexes to complement B-tree structures. 



More detail about this project can be found here

C-Store project

The last release for C-Store project was C-Store version 0.2 is now publicly available. Last released in October 2006, It is built with open source tools and runs on Linux x86 computers.


There is also a White paper release by Michael Stonebreaker and team

C-Store whitepaper

Enjoy reading.
Appreciate your comments.

Read more ...