The Vertica Database Forums

Posted: **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

Posted: **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...

The Vertica Database Forums

Vertica User defined SQL function

Vertica User defined SQL function

Re: Vertica User defined SQL function