User-defined functions and aggregate functions

DataStax DevCenter is compatible with DataStax Enterprise (DSE) versions up to 5.0.

The CQL Script editor supports the creation of user-defined functions (UDF) in CQL and their use in INSERT and SELECT statements.

Assuming a table:

CREATE TABLE cycling.cyclist_points (
   id UUID,
   name text,
   race_title text,
   race_points double,
   PRIMARY KEY (id, race_points )
);

A UDF can be created and used as follows:

CREATE OR REPLACE FUNCTION cycling.flog(d double)
CALLED ON NULL INPUT RETURNS double 
LANGUAGE java AS 'return Double.valueOf(Math.log(d.doubleValue()));';

INSERT INTO cycling.cyclist_points
JSON '{ 
         "id" : "93031620-12ae-11e4-9191-0800200c9a66",
         "name" : "Georgina Bronzini", 
         "race_title" : "Tour of Chongming Island World Cup",
         "race_points" : 120
      }';

INSERT INTO cycling.cyclist_points
JSON '{
         "id" : "f6071e72-48ec-4fcb-bf3e-379c8a696488", 
         "name" : "Paolo Tiralongo", 
         "race_title" : "98th Giro d'Italia - Stage 15",
         "race_points" : 2
      }';
            
SELECT id, name, flog(race_points) FROM cycling.cyclist_points;

User-defined aggregate functions 

The CQL Script editor supports the creation of user-defined aggregate functions (UDAF) in CQL and their use in SELECT statements.

Assuming a table:

CREATE TABLE social.numbers (
   id text,
   num int,
   PRIMARY KEY ( id )
);

A UDAF can be created and used as follows:

INSERT INTO social.numbers (id, num) VALUES ('007', 110);
INSERT INTO social.numbers (id, num) VALUES ('123', 34);
INSERT INTO social.numbers (id, num) VALUES ('111', 667);
INSERT INTO social.numbers (id, num) VALUES ('099', 12);

CREATE OR REPLACE FUNCTION social.my_state ( s int, b int ) 
	CALLED ON NULL INPUT
	RETURNS int
	LANGUAGE java
	AS $$
		if ( s == 0 ) return 1; else return s * b;
	$$;

CREATE OR REPLACE AGGREGATE social.my_product ( int )
  	SFUNC social.my_state
  	STYPE int
  	INITCOND 0;

SELECT social.my_product(num) FROM social.numbers;