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
Vertica User defined SQL function
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Vertica User defined SQL function
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:
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...
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.