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

From 3.8 functions can be set via a SQL statement with the following syntax:

CREATE FUNCTION cubic AS ``function(x) { return x*x*x; }``;

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 - please nota that value Will be undefined
		return accumulator;  
	}
}

See more examples here:

• 50functions.js
• test266.js

From 3.8 aggretating functions can be set via a SQL statement with the following syntaxes:

CREATE (AGGREATE|AGGREGATOR) MyAggr AS ``function(value, accumulator, stage) { ... }``;