Add custom SQL operators to MemCP - Revision history

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

2026-08-28T10:13:54Z

Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

← Older revision		Revision as of 12:13, 28 August 2026
Line 9:		Line 9:
	The smallest example adds one to its argument:		The smallest example adds one to its argument:

	<~~syntaxhighlight lang="scheme"~~>		<pre>
	(sql_builtins "ADD_1" (lambda (x) (+ x 1)))		(sql_builtins "ADD_1" (lambda (x) (+ x 1)))
	</~~syntaxhighlight~~>		</pre>

	After the module containing this declaration has been imported, the function is available in SQL:		After the module containing this declaration has been imported, the function is available in SQL:

	<~~syntaxhighlight lang="sql"~~>		<pre>
	SELECT ADD_1(4) AS result;		SELECT ADD_1(4) AS result;
	-- result: 5		-- result: 5
	</~~syntaxhighlight~~>		</pre>

	SQL function names are normalized to upper case before lookup, so registry keys should conventionally be uppercase. The function may still be written as <code>add_1(...)</code>, <code>Add_1(...)</code>, or <code>ADD_1(...)</code> in SQL.		SQL function names are normalized to upper case before lookup, so registry keys should conventionally be uppercase. The function may still be written as <code>add_1(...)</code>, <code>Add_1(...)</code>, or <code>ADD_1(...)</code> in SQL.
Line 24:		Line 24:
	The declaration must run after <code>lib/sql-parser.scm</code> or <code>lib/sql-builtins.scm</code> has created the shared registry. A custom application module loaded after <code>lib/main.scm</code> is a suitable place:		The declaration must run after <code>lib/sql-parser.scm</code> or <code>lib/sql-builtins.scm</code> has created the shared registry. A custom application module loaded after <code>lib/main.scm</code> is a suitable place:

	<~~syntaxhighlight lang="scheme"~~>		<pre>
	/* apps/my-sql-functions.scm */		/* apps/my-sql-functions.scm */
	(define sql_add_1 (lambda (x)		(define sql_add_1 (lambda (x)
Line 30:		Line 30:

	(sql_builtins "ADD_1" sql_add_1)		(sql_builtins "ADD_1" sql_add_1)
	</~~syntaxhighlight~~>		</pre>

	Load the module when starting MemCP:		Load the module when starting MemCP:

	<~~syntaxhighlight lang="bash"~~>		<pre>
	./memcp --api-port=4321 --mysql-port=3307 \		./memcp --api-port=4321 --mysql-port=3307 \
	lib/main.scm apps/my-sql-functions.scm		lib/main.scm apps/my-sql-functions.scm
	</~~syntaxhighlight~~>		</pre>

	Then test it through the HTTP frontend:		Then test it through the HTTP frontend:

	<~~syntaxhighlight lang="bash"~~>		<pre>
	curl --fail-with-body -u root:strong-password \		curl --fail-with-body -u root:strong-password \
	--data-binary 'SELECT ADD_1(4), ADD_1(NULL)' \		--data-binary 'SELECT ADD_1(4), ADD_1(NULL)' \
	http://localhost:4321/sql/test		http://localhost:4321/sql/test
	</~~syntaxhighlight~~>		</pre>

	== Parameters, NULL, and result types ==		== Parameters, NULL, and result types ==
Line 51:		Line 51:
	The lambda parameter list defines the accepted arity. A three-argument function is registered in exactly the same way:		The lambda parameter list defines the accepted arity. A three-argument function is registered in exactly the same way:

	<~~syntaxhighlight lang="scheme"~~>		<pre>
	(sql_builtins "CLAMP" (lambda (value lower upper)		(sql_builtins "CLAMP" (lambda (value lower upper)
	(if (nil? value) nil		(if (nil? value) nil
	(max lower (min upper value)))))		(max lower (min upper value)))))
	</~~syntaxhighlight~~>		</pre>

	<~~syntaxhighlight lang="sql"~~>		<pre>
	SELECT CLAMP(12, 0, 10); -- 10		SELECT CLAMP(12, 0, 10); -- 10
	</~~syntaxhighlight~~>		</pre>

	A custom function is responsible for its observable SQL behavior:		A custom function is responsible for its observable SQL behavior:
Line 75:		Line 75:
	An existing Scheme function can be exported without wrapping it:		An existing Scheme function can be exported without wrapping it:

	<~~syntaxhighlight lang="scheme"~~>		<pre>
	(sql_builtins "MY_UNIX_TIMESTAMP" unix_timestamp)		(sql_builtins "MY_UNIX_TIMESTAMP" unix_timestamp)
	</~~syntaxhighlight~~>		</pre>

	Aliases in <code>lib/sql-builtins.scm</code> use this pattern extensively. A wrapper lambda is preferable when SQL needs different NULL handling, argument order, defaults, or result normalization.		Aliases in <code>lib/sql-builtins.scm</code> use this pattern extensively. A wrapper lambda is preferable when SQL needs different NULL handling, argument order, defaults, or result normalization.
Line 85:		Line 85:
	For hot functionality that cannot be expressed efficiently in Scheme, first declare a typed Scheme builtin in Go. The reduced pattern below follows the runtime's <code>Declaration</code> and <code>TypeDescriptor</code> API:		For hot functionality that cannot be expressed efficiently in Scheme, first declare a typed Scheme builtin in Go. The reduced pattern below follows the runtime's <code>Declaration</code> and <code>TypeDescriptor</code> API:

	<~~syntaxhighlight lang="go"~~>		<pre>
	DeclareInSection("Arithmetic / Logic", &Globalenv, &Declaration{		DeclareInSection("Arithmetic / Logic", &Globalenv, &Declaration{
	Name: "add_1",		Name: "add_1",
Line 103:		Line 103:
	},		},
	})		})
	</~~syntaxhighlight~~>		</pre>

	Use <code>DeclareInSection</code> to place a function in an existing generated API chapter. A new chapter starts with <code>DeclareTitle</code> and ordinary <code>Declare</code> calls. A new initializer must also be called from the runtime initialization sequence; merely adding a Go function does not register it.		Use <code>DeclareInSection</code> to place a function in an existing generated API chapter. A new chapter starts with <code>DeclareTitle</code> and ordinary <code>Declare</code> calls. A new initializer must also be called from the runtime initialization sequence; merely adding a Go function does not register it.
Line 111:		Line 111:
	After the Scheme builtin exists, expose it to SQL separately:		After the Scheme builtin exists, expose it to SQL separately:

	<~~syntaxhighlight lang="scheme"~~>		<pre>
	(sql_builtins "ADD_1" add_1)		(sql_builtins "ADD_1" add_1)
	</~~syntaxhighlight~~>		</pre>

	This separation is intentional: declaring a Scheme builtin makes it available to Scheme and generated plans, while registering it in <code>sql_builtins</code> gives the SQL parser a function name.		This separation is intentional: declaring a Scheme builtin makes it available to Scheme and generated plans, while registering it in <code>sql_builtins</code> gives the SQL parser a function name.

Wikiservice: Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

2026-08-28T09:59:22Z

Refresh MemCP documentation: accuracy, operational guidance, performance profile and maintained API reference

@@ Line 1: / Line 1: @@
-Some special applications need user defined custom functions.
+<!-- Copyright (C) 2026 Carl-Philip Haensch -->
-You can define them in scheme like:
+Applications can expose their own Scheme functions as scalar SQL functions. The SQL parsers look up function names in the shared <code>sql_builtins</code> registry and place the registered Scheme procedure directly into the generated query plan.
- (sql_builtins "ADD_1" (lambda (x) (+ x 1)))
-Now you can call the function by:
+== Register a Scheme function for SQL ==
- SELECT ADD_1(4) AS result

Carli: Created page with "Some special applications need user defined custom functions. You can define them in scheme like: (sql_builtins "ADD_1" (lambda (x) (+ x 1))) Now you can call the function by: SELECT ADD_1(4) AS result"

2025-06-15T12:59:44Z

Created page with "Some special applications need user defined custom functions. You can define them in scheme like: (sql_builtins "ADD_1" (lambda (x) (+ x 1))) Now you can call the function by: SELECT ADD_1(4) AS result"

New page

Some special applications need user defined custom functions.

You can define them in scheme like:
(sql_builtins "ADD_1" (lambda (x) (+ x 1)))
Now you can call the function by:
SELECT ADD_1(4) AS result