Custom, Cypher Based Procedures and Functions

I wanted for a long time to be able to register Cypher statements as proper procedures and functions, so that they become callable in a standalone way.

You can achieve that with the apoc.custom.declareProcedure and apoc.custom.declareFunction procedure calls. Those register a given Cypher statement, prefixed with the custom.* namespace, overriding potentially existing ones, so you can redefine them as needed.

The first parameter of the apoc.custom.declareProcedure and apoc.custom.declareFunction procedures, is the signature of the procedure/function you want to create. This looks similar to the signature results returned by the SHOW PROCEDURES YIELD signature, SHOW FUNCTIONS YIELD signature cypher commands, or by the CALL apoc.help('<fun_or_procedure_name>') YIELD signature procedure, just without the ?. That is: - for a procedure: nameProcedure(firstParam = defaultValue :: typeParam , secondParam = defaultValue :: typeParam, …​.) :: (firstResult :: typeResult, secondResult :: typeResult, …​ ) - for a function: nameFunction(firstParam = defaultValue :: typeParam , secondParam = defaultValue :: typeParam, …​.) :: typeResult

Note that, for both procedures and functions, the = defaultValue are optionals. The default values are parsed as JSON.

If you want to create a procedure/function with a default String parameter with whitespaces, quotes (for example: "my text ' with ' quote") or "null" (as a string), you have to quote the result, e.g CALL apoc.custom.declareProcedure("procWithNullString(param='null'::STRING)::(output::STRING)", 'return $param as output')
Type Names

The typeParam and typeResult in the signature parameter can be one of the following values: * FLOAT, DOUBLE, INT, INTEGER, NUMBER, LONG * TEXT, STRING * BOOL, BOOLEAN * POINT, GEO, GEOMETRY * DATE, DATETIME, LOCALDATETIME, TIME, LOCALTIME, DURATION * NODE, REL, RELATIONSHIP, EDGE, PATH * MAP * LIST TYPE, LIST OF TYPE (where TYPE can be one of the previous values) * ANY

If you override procedures or functions you might need to call call db.clearQueryCaches() as lookups to internal id’s are kept in compiled query plans.

Starting from version 5.11, if we execute a CALL apoc.custom.declareFunction('nameFun(…​.)'), or a CALL apoc.custom.declareProcedure('nameProc(…​.)'), we cannot execute respectively a RETURN custom.nameFun(..) or a CALL custom.nameProc() within the same transaction, an error will be thrown.

So we must necessarily open a new transaction to execute the custom procedures / functions declared.

Custom Procedures with apoc.custom.declareProcedure

Here is a simple example:

CALL apoc.custom.declareProcedure('answerInteger() :: (row::INT)', 'RETURN 42 as answer')

This registers the statement as procedure custom.answer that you then can call.

CALL custom.answerInteger
Table 1. Results
answer

42

Or you can also write in this way:

CALL apoc.custom.declareProcedure('answer() :: (row::MAP)', 'RETURN 42 as answer')

In this case the result is wrapped in a stream of maps called row. Therefore, you can do:

CALL custom.answer() YIELD row
RETURN row.answer
Table 2. Results
answer

42

We can create the function custom.powers that returns a stream of the powers of the first parameter, up to and including the power provided by the second parameter:

CALL apoc.custom.declareProcedure(
  'powers(input::INT, power::INT) :: (answer::INT)',
  'UNWIND range(0, $power) AS power
   RETURN $input ^ power AS answer'
);
Procedure, input and output names must have at least 2 characters.

We can use this function, to return 4°, 4¹, 4², and 4³, as shown in the query below:

call custom.powers(4,3);
Table 3. Results
answer

1.0

4.0

16.0

64.0

Furthermore, we can pass as the 3rd parameter a string to specify the procedure mode (default "WRITE"). It can be: - "READ" - if the procedure will only perform read operations against the graph - "WRITE" - if it may perform both read and write operations against the graph - "SCHEMA" - if it will perform operations against the schema - "DBMS" - if it will perform system operations - i.e. not against the graph

Moreover, we can pass a description parameter as the 4th parameter, which will be returned by the call apoc.custom.list and SHOW PROCEDURES.

Custom Functions with apoc.custom.declareFunction

Here is a simple example:

CALL apoc.custom.declareFunction('answerFun() :: INT', 'RETURN 42 as answer')

This registers the statement as procedure custom.answer that you then can call.

RETURN custom.answerFun()
Table 4. Results
answer

42

Or you can also write in this way:

CALL apoc.custom.declareFunction('answerFunMap() :: MAP', 'RETURN 42 as answer')

In this case the result is wrapped in a stream of maps called row. Therefore, you can do:

WITH custom.answerFunMap() YIELD row
RETURN row.answer
Table 5. Results
answer

42

We can create the function custom.powers that returns a stream of the powers of the first parameter, up to and including the power provided by the second parameter:

CALL apoc.custom.declareProcedure(
  'powers(input::INT, power::INT) :: (answer::INT)',
  'UNWIND range(0, $power) AS power
   RETURN $input ^ power AS answer'
);

We can create the function custom.double, that doubles the provided value, by running the following function:

CALL apoc.custom.declareFunction(
  'double(input::INT) :: INT',
  'RETURN $input*2 as answer'
);
Function, input and output names must have at least 2 characters.

We can use this function, as shown in the query below:

RETURN custom.double(83) AS value;
Table 6. Results
value

166

Furthermore, we can pass as a 3rd parameter a boolean (with default false) which, if true, in case the function returns a list of a single element, it will return only the single element itself and not the list.

For example:

CALL apoc.custom.declareFunction('forceSingleTrue(input::ANY) :: LIST OF INT',
  'RETURN 1',
  true
);
Table 7. Results
value

1

otherwise with false the result will be a singleton list:

CALL apoc.custom.declareFunction('forceSingleFalse(input::ANY) :: LIST OF INT',
  'RETURN 1',
  false
);
Table 8. Results
value

[1]

Moreover, we can pass a description parameter as the 4th parameter, which will be returned by the call apoc.custom.list and SHOW FUNCTIONS.

List of registered procedures/function with apoc.custom.list

The procedure apoc.custom.list provide a list of all registered procedures/function via apoc.custom.declareProcedure and apoc.custom.declareFunction

Given the this call:

CALL apoc.custom.list

The output will look like the following table:

type name description mode statement inputs outputs forceSingle

"function"

"answer"

<null>

<null>

"RETURN $input as answer"

[["input","number"]]

"long"

false

"procedure"

"answer"

"Procedure that answer to the Ultimate Question of Life, the Universe, and Everything"

"read"

"RETURN $input as answer"

[["input","int","42"]]

[["answer","number"]]

<null>

Remove a procedure apoc.custom.removeProcedure

The procedure apoc.custom.removeProcedure allows to delete the targeted custom procedure.

Given the this call:

CALL apoc.custom.removeProcedure(<name>)

Fields:

argument description

name

the procedure name

Remove a procedure apoc.custom.removeFunction

The procedure apoc.custom.removeFunction allows to delete the targeted custom function.

Given the this call:

CALL apoc.custom.removeFunction(<name>)

Fields:

argument description

name

the function name

How to manage procedure/function replication in a Causal Cluster

In order to replicate the procedure/function in a cluster environment you can tune the following parameters:

name type description

apoc.custom.procedures.refresh

long (default 60000)

the refresh time that allows replicating the procedure/function changes to each cluster member

Export metadata

To import custom procedures in another database (for example after a ./neo4j-admin backup and /neo4j-admin restore), please see the apoc.systemdb.export.metadata procedure.