Vertica User defined SQL function

Moderator: NorbertKrupa

Post Reply
rsaveetha
Newbie
Newbie
Posts: 5
Joined: Sat Oct 31, 2015 2:53 pm

Vertica User defined SQL function

Post by rsaveetha » Tue Mar 29, 2016 1:31 pm

Hi,

I was creating a user defined SQL function using a create or replace function statement.

My function converts a numerical value to its range ie if my input value is 213 the output should be 200-300.

I have written a logic and created function. When i try to run the function, it gives me compilation error for long.

After a long search, i found in a blog on the restrictions of vertica that "For now, there is no support for Variables, loops with User Defined SQL Functions".

Could anyone confirm that, if this is correct and vertica user defined sql funtions does not support variables and loops.

Thanks,
Saveetha

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Vertica User defined SQL function

Post by JimKnicely » Thu Mar 31, 2016 1:07 am

Hi,

Yes, it is true that Vertica user defined SQL functions do not have programming capabilities like loops and variables. Those features are available with external functions created in C or JAVA. But, most of the time you can be creative and avoid all of that.

Example:

Code: Select all

dbadmin=> create or replace function my_range(x int) return varchar as
dbadmin-> begin
dbadmin-> return rpad(substr(x::varchar,1,1), length(x::varchar), '0') || '-' ||
dbadmin->   rpad(substr(x::varchar,1,1), length(x::varchar), '0')::int + rpad('1', 3, '0')::int;
dbadmin-> end;
CREATE FUNCTION

dbadmin=> select my_range(213);
 my_range
----------
 200-300
(1 row)

dbadmin=> select my_range(0);
 my_range
----------
 0-100
(1 row)

dbadmin=> select my_range(999);
 my_range
----------
 900-1000
(1 row)
There are probably better of ways of doing this, but I just wanted to get you thinking :) Remember, Vertica is an analytic databae built to run against billions of records. It is a good idea to not run a function like the one I created above against billions of records on the fly. You can add a column to a table to store the results, i.e. 200-300, 0-100, etc...
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL Functions”