sqlite_create_aggregate
Register an user defined aggregate.
|
Syntax
$sqlite_create_aggregate ( conn, func_name, step_alias, finalize_alias [, num_args [, step_prop [, finalize_prop ] ] ] )
/sqlite_create_aggregate conn func_name step_alias finalize_alias [ num_args [ step_prop [ finalize_prop ] ] ] |
Parameters
conn
The connection identifier.
func_name
The SQL function name to register.
step_alias
The mIRC alias to register as step function.
finalize_alias
The mIRC alias to register as finalize function.
num_args
The number of arguments the function accepts. Optional.
step_prop
A custom property you want to use for step alias. Optional.
finalize_prop
A custom property you want to use for finalize alias. Optional.
Return Value
1 if the function was registered successfully, or $null if there was an error.
Remarks
$sqlite_create_aggregate allows users to register their own aggregates in SQL.You can also use $sqlite_create_aggregate to override default functionality of SQLite's core aggregates.
Creating aggregate functions is similiar to creating ordinary functions.
The key difference is that $sqlite_create_aggregate requires two mIRC aliases to be registered:
step_alias is called for each row in a group, it is used to accumulate data.
finalize_alias is called after all the rows are processed, it is used to return the result to the query.
step_alias and finalize_alias always receive an aggregate context as first parameter.
The step_alias can use the aggregate context to accumulate data. It should return the aggregate context at end.
When finalize_alias is called it can use the aggregate context to determine the accumuluted result.
Parameters passed to the function in the SQL query are passed after the aggregate context.
The optional argument num_args can be used to hint SQLite if there's a predetermined amount of arguments.
If an user defined function is used with different parameter count as what was instructed with num_args, the query will raise an SQL error.
The default, -1, means that an arbitrary number of parameters can be passed to the function.
It is usually ok to ignore the return value of $sqlite_create_function if you know that the parameters are valid.
For more information about user defined aggregates, see User Defined Functions and User Defined Aggregates.
|
Example
; Creates an aggregate to return the max length of a string
alias max_len_step { var %context = $1, %len = $len($2) if (%context !isnum || %len > %context) { %context = %len } return %context } alias max_len_finalize { var %context = $1 if (%context !isnum) { %context = 0 } return %context } ; Registers the user defined function in an existing connection %db sqlite_create_aggregate %db max_len max_len_step max_len_finalize ; Example queries ; SELECT max_len(value) FROM strings ; SELECT max_len(first_name) FROM contacts GROUP BY last_name |