# SQL Create Function

> The `create function` statement is used to create a User Defined Function.

> The complete syntax is as follows:

>         CREATE FUNCTION name "(" { arg_name data_type  [ "," ... ] ")"
>             RETURNS return_type [VOLATILE]

>         AS library_path ["," c_function_name ] |
>         IN "'"language_name"'" AS "'"function_body"'"

>         data_type:
>             BIT | BOOLEAN | CHAR [ "(" size ")" ] | VARCHAR [ "(" size ")" ] | LOGNVARCHAR [ "(" size ")" ] |
>             STRING [ (" width ")" ] | UNICODE[ (" width ")" ]  -- corresponds to eXtremeDB nstring or nchar
>             | TINYINT    -- signed<1>
>             | SMALLINT -- signed<2>
>             | INT [ "(" n ")" ]      -- where n=1,2,4,8 corresponds to eXtremeDB signed<n>
>             | INTEGER [ "(" n ")" ]      -- where n=1,2,4,8 corresponds to eXtremeDB signed<n>
>             | UNSIGNED [ "(" n ")" ]      -- where n=1,2,4,8 corresponds to eXtremeDB unsigned<n>
>             | BIGINT -- signed<8>
>             | FLOAT
>             | REAL -- double
>             | DOUBLE
>             | NUMERIC [ "(" width [ "," precision ]  ")" ]  -- eXtremeDB numeric< width,precision> type
>             | DECIMAL [ "(" width [ "," precision ]  ")" ]   -- eXtremeDB numeric< width,precision> type
>             | DATE -- eXtremeDB 8-byte datetime type
>             | TIME   -- eXtremeDB 8-byte datetime type
>             | TIMESTAMP  -- eXtremeDB 8-byte datetime type
>             | BIANRY [ "(" size ")" ]  --- array or vector of signed<1>
>             | VARBINARY  [ "(" size ")" ]   --- array or vector of signed<1>
>             | LONGVARBINARY  [ "(" size ")" ]   --- array or vector of signed<1>
>             | REFERENCE
>             | BLOB
>             | ARRAY "(" data_type [ "," length ] ")"
>             | SEQUENCE "(" scalar_data_type [ ASC | DESC ] ")"
>             | user-defined-type  -- declared by "create domain"

> Note that all SQL keywords are case insensitive in eXtremeSQL - i.e. "`CREATE FUNCTION`" and `"create function"` are equivalent.

> ## Examples

> For example, the following SQL statement could be executed for loading the C++ user-defined function `load_cme_trades()` as SQL function `load_trades()`:

>         create function load_trades(fname string, book string) returns integer as 'mcocme', 'load_cme_trades';

> Here `mcocme` refers to the dynamic load file `libmcocme.so` (or `mcocme.dll` on Windows systems) that contains the implementation of UDF `load_cme_trades`.

> When executed this statement creates table Functions, if it is not already created, and inserts the row defining this UDF.

> To create function defined in scripting language, which can be called stored procedure, the second form of the `create` statement can be used. For example, a Lua UDF could be defined as follows:

>         create function twice(x integer) returns integer in 'lua' as 'return x+x end';

> Or, a Python UDF could be defined as follows:

>         create function pyadd(x int, y int) returns int in 'python' as 'return x+y end';

> The UDFs can then be executed in SQL with a statement like the following:

>         XSQL>select twice(3), pyadd(5,7);
>         #1 #2
>         -----------------------------------------
>         6 12

>         Selected records: 1

> When a Python UDF is first called, it loads the python interpreter, compiles the function code and executes it. A reference to the created interpreter object and compiled code is saved in memory, so this loading process occurs only one time in the life of the application and the UDF is compiled only once.

> (Note that, for Windows users, a Python UDF module (DLL) `mcopythonapi.dll` is a plugin module for the eXtremeSQL engine, and has a link dependency to library `mcosql.dll`. This means that to use a UDF (Lua as well as Python UDFs) from a user program, it must load eXtremeSQL and the eXtremeDB core runtime dynamically. See `mco_core_load()` for further details)

> ## Function Caching

> When a function call is performed, the SQL engine will store the function result and use this cached value for subsequent calls of this function if the function arguments are the same; i.e. the arguments are the same syntactic construct in the query.

> So call will be cached if:

> - the argument is a constant expression

> or

> - the argument is physically the same database value; i.e. it is the same column value in the same row in the same table.

> It will not be cached if:

> - a constant expression or column appeared more than one time in a query

> Practically, only two use cases are known to use the function result caching:

> - an evaluation function of a constant expression for many rows in the result
> - when joining two tables A and B in a query like "select * from A,B where sin(A.x) > 0;". Here function `sin(A.x)` will be called only cardinality(A) times, but not cardinality(A) * cardinality(B).

> For some functions this caching is not appropriate, e.g. `random()`. To disable caching for a particular function, the `VOLATILE` flag can be used.

> ### Example

>         create function vmtf(name varchar, dept_no unsigned(2), salary unsigned(4)) returns int VOLATILE in 'lua' as '
>             require("test_udf")
>                 return mtf_impl(name, dept_no, salary)
>             end
>             ';

> (To remove a function a ``drop function`` statement is used.)