User Defined Functions

User defined functions

To define new functions for SQL simply add it to alasql.fn variable, like below:

alasql.fn.cube = function(x) { return x*x*x; }
alasql(‘SELECT cube(x) FROM ?’,[data]);

alasql.fn.double = function(x){return x*2};        
alasql.fn.sum10 = function(x,y) { return x+y*10; }
alasql('SELECT a, double(a) AS b, sum10(a,b) FROM test1');

You can use alasql inside alasql functions, like below:

alasql.fn.myfilter = function(phase) {
	return alasql('SELECT VALUE COUNT(*) FROM ? WHERE Phase = ?',[data,phase]) == 2;
};

var res = alasql('SELECT * FROM ? WHERE myfilter(Phase)',[data]);

See the working example in jsFiddle

Aggregators

To make your own user defined aggregators please follow this example:

// How to implement the SUM() aggregator
alasql.aggr.MYAGGR = function(value, accumulator, stage) {
	if(stage == 1) {

		// first call of aggregator - for first line
		var newAccumulator =  value;
		return newAccumulator;
	
	} else if(stage == 2) {

		// for every line in the group
		accumulator = accumulator + value;
		return accumulator;
	
	} else if(stage == 3) {

		// Post production
		return accumulator;  
	}
}

See more examples here:

• 50functions.js
• test266.js