Functions and Procedures?

Moderator: NorbertKrupa

User avatar
fsalvelt
Intermediate
Intermediate
Posts: 54
Joined: Sun Mar 18, 2012 1:34 am

Functions and Procedures?

Post by fsalvelt » Sun Mar 18, 2012 1:44 am

Can we create functions, procedures and packages in Vertica? Is there a native programming language that we can use like PL/SQL in Oracle?
I'm editing this post - Oops, I realized I should have posted this in the New to Vertica Development Forum (Sorry)
This post was moved here from the "Vertica Database Development" forum by knicely87
Thank, Fred

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

Re: Functions and Procedures?

Post by JimKnicely » Fri Mar 23, 2012 7:06 pm

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!
Jim Knicely

Image

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

User avatar
fsalvelt
Intermediate
Intermediate
Posts: 54
Joined: Sun Mar 18, 2012 1:34 am

Re: Functions and Procedures?

Post by fsalvelt » Fri Mar 23, 2012 8:48 pm

Thanks! So I guess there are no procedures. I also hope that Vertica is working on a programming language...
Thank, Fred

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Functions and Procedures?

Post by id10t » Mon Aug 20, 2012 12:10 pm

Hi!

If anyone need I added function "reverse_string" to my repo: http://github.com/sKwa/vertica/tree/master/UDFCPP

Code: Select all

dbadmin=> select reverse('foo bar baz palindrom egg spam');
            reverse             
--------------------------------
 maps gge mordnilap zab rab oof
(1 row)

jagadeesh
Newbie
Newbie
Posts: 21
Joined: Tue Feb 05, 2013 9:36 am

Re: Functions and Procedures?

Post by jagadeesh » Mon Feb 11, 2013 7:53 am

Hi,

So, If we need to do any complex operation we only have to go to C++ or Shell scripting?

Say, i just want to get a Max(some_Column) based on a logic involving may table.. cant i write a SQL statement in the function and get the return value? or is there any work around for this?

pls help...

Thanks,
Jagadeesh

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

Re: Functions and Procedures?

Post by JimKnicely » Mon Feb 11, 2013 5:39 pm

Unfortunately, as of release 6.1 we can't write SELECT statements in a function.
Jim Knicely

Image

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

jagadeesh
Newbie
Newbie
Posts: 21
Joined: Tue Feb 05, 2013 9:36 am

Re: Functions and Procedures?

Post by jagadeesh » Tue Feb 12, 2013 5:47 am

Thanks Jim...! that helped me to stop scratching my head.. :)

Post Reply

Return to “New to Vertica Database Development”