The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Custom multi-argument aggregate functions

I have a custom aggregate metric that is computed based on two different columns. It can be computed in SQL, but it is moderately complex. Something similar to: 100 * SUM(CASE WHEN c1 IN (0,1,2) THEN 1 ELSE 0) / SUM(CASE WHEN c1 = 0 AND c2 = 1 THEN 1 ELSE 0) I would like to abstract this into a UDF so that we can tweak the formula periodically and not have to update every query separately. Is there a clean way to do this, given that UDAFs can only take a single argument?


  • Hi, What you need is a User-Define SQL Function ( and you can pass more than 1 argument. Look this simple example. dbadmin=> CREATE FUNCTION add2int(x int, y int) return int dbadmin-> as begin dbadmin-> return (x + y ) ; dbadmin-> end; CREATE FUNCTION dbadmin=> create table test(c1 int, c2 int) ; CREATE TABLE dbadmin=> copy test from stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1|2 >> 3|5 >> \. dbadmin=> select add2int(c1,c2) from test; add2int -------------- 3 8 (2 rows) Hope this helps, Eugenia
  • Hi Eugenia! I don't believe user-defined SQL functions can use aggregate functions: dbadmin=> create function mysum(x int) return int dbadmin-> as begin dbadmin-> return sum(x); dbadmin-> end; ROLLBACK 4257: Only simple "RETURN expression" is allowed
  • Hi all, Eugenia may have more ideas; I don't mean to interrupt. But, Aniket, you could also take a look at our User-Defined Transform API. It is in some sense our most-general SDK API; it requires a more-cumbersome SQL syntax, but it lets you be very expressive with the types of functions that you write. It's our generic catch-all API; with enough effort you can implement just about anything :-) Also, the User-Defined Analytics API is almost as powerful, and might be a better fit for your particular use case here. Adam

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file