Custom multi-argument aggregate functions

Comments

• [Deleted User] ✭

  September 2013

  Hi, What you need is a User-Define SQL Function (https://my.vertica.com/docs/6.1.x/HTML/index.htm#15038.htm) 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

  0
• Aniket_Schneide

  September 2013

  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

  0
• [Deleted User]

  September 2013

  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

  0