UDA in a SQL query

After you register a UDA, you and other permitted users can call it in the same manner as the SQL aggregates. To use a UDA, users must have Execute privilege for AGGREGATE objects or for the specific UDA.

By default, the admin user account has Execute privilege to all user-defined aggregates. The user who registered a UDA also has execute access to that UDA. Other users can be granted privileges to run specific or all UDAs.

For the sample PenMax aggregate, first create a sample table that contains the data to be processed by the UDA. For example:

CREATE TABLE myints (a int, b int);
INSERT INTO myints VALUES (1,2);
INSERT INTO myints VALUES (1,4);
INSERT INTO myints VALUES (1,6);
INSERT INTO myints VALUES (2,8);
INSERT INTO myints VALUES (2,10);
INSERT INTO myints VALUES (2,12);

Then, you can run the sample PenMax aggregate, as follows:

SELECT penmax(b) FROM myints;

Sample output follows:

PENMAX
--------
10

Another example follows:

SELECT a,penmax(b) FROM myints GROUP BY a;

Sample output follows:

 A | PENMAX
---+--------
 1 | 4
 2 | 10