Hi,
We can create really basic user-defined SQL functions and more complex externally defined functions (written in c++).
The user-defined SQL functions are best used when migrating SQL statements from other databases where those SQL statement reference functions that do not exist in Vertica. For instance, there is a SQL function in Oracle named
REVERSE which accepts a string parameter and returns the characters of the string in reverse order. That is, the string "Vertica" would come back "acitreV". The
REVERSE function does not exist in Vertica but we can create our own version to mimic Oracle's functionality.
Example:
Code: Select all
dbadmin=> select reverse('Vertica');
ERROR: function reverse("unknown") does not exist, or permission is denied for reverse("unknown")
HINT: No function matches the given name and argument types. You may need to add explicit type casts.
The
REVERSE function does not exist so let's create our own version:
Code: Select all
dbadmin=> create function reverse(x varchar) return varchar
dbadmin-> as
dbadmin-> begin
dbadmin-> -- Simple reverse function limited to 10 characters
dbadmin-> return (substr(x, 10, 1 ) || substr(x, 9, 1 ) || substr(x, 8, 1 ) || substr(x, 7, 1 ) ||
dbadmin(> substr(x, 6, 1 ) || substr(x, 5, 1 ) || substr(x, 4, 1 ) || substr(x, 3, 1 ) ||
dbadmin(> substr(x, 2, 1 ) || substr(x, 1, 1 ));
dbadmin->
dbadmin->
dbadmin-> end;
CREATE FUNCTION
Now let's try the SELECT statement again:
Code: Select all
dbadmin=> select reverse('Vertica');
reverse
---------
acitreV
(1 row)
As you can see, our function is pretty basic and it'll only work on strings up to 10 characters (we could hard code more subtr functions to handle more characters). Unfortunately there is no support for a programming language (i.e. loops, variables, etc.) in functions at this time. We're limited to only using other Vertica SQL functions. We can't even do a SELECT statement. I do hope that the Vertica developers are working on an internal language that we can use in stored functions and procedures in a future release.
If you need more complex logic you'll have to create an external function.
Have fun!
