UDFs vs ORACLE Procedures

Can someone please help me on this!!!!

Hi RajatGupta,

The Forums are informal, and subject to the ability of people to respond to them. If you have an important issue and you're on support you can always reach out to us via formal channels.

In the meantime I've contacted someone who may be able to help. I'm not clear on their availability to respond to your inquiry, and let's see where it goes.

Hi,

There a few different questions in here that I'll try to answer. First, the Vertica docs have a lot of information about developing UDxs [1]. Information specific to developing Java UDxs can be found in the link in [2]. I also found an older blog post about developing Java UDxs from within Eclipse, but I don't know whether or not the information is up to date [3].

Now, to give a high level overview of our UDx framework:

There are a few different types of UDxs. UD scalar functions return a single value per row, e.g. if user_defined_sum is a scalar function that computes the sum of its inputs,

SELECT a, b, user_defined_sum(a, b)
FROM foo;

would return columns a, b, and the sum of a and b for every row in foo.

UD transform functions can return an arbitrary number of columns and rows per row, and use an OVER() clause to partition the data however you want into its input.

UD aggregate functions let you define an aggregate function like the built-in aggregates, e.g. sum, avg, etc.

There's also UD analytic functions, which are similar to UDTFs.

In summary, UDFs in Vertica allow you to write functions that can then be called in the same manner as Vertica built-in functions within the SQL syntax.

External procedures are effectively scripts that you can have Vertica execute for you, external to the database process. If these scripts happen to connect back to Vertica with a client and execute queries, then that's fine - but other than that there's nothing special about them as far as accessing data in Vertica.

I can't comment on the comparison to Oracle as I'm not familiar with their functionality, but hopefully this overview provided some clarity.

[1] https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/ExtendingVertica/ExtendingVertica.htm?TocPath=Extending%20Vertica|_____0
[2] https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/ExtendingVertica/Java/DevelopingInJava.htm?TocPath=Extending%20Vertica|Developing%20User-Defined%20Extensions%20(UDxs)|Developing%20with%20the%20Java%20SDK|_____0
[3] https://my.vertica.com/blog/updating-udx-projects-syncing-the-vertica-plug-in-for-eclipse-with-new-vertica-versions/

Nice job, saltzm. Thanks!

Thanks a lot Tom and saltzm for help. I will go through these links.

Cheers!!