CREATE FUNCTION Statement

Print this page
Learn more about Verizon Connect GeoBase.
Get information about the latest release

User-defined functions can be built and called within an Alchemy script using the FUNCTION statement.

The function should be written in C# and placed between the BEGIN-END statements.

Note
The created function will be passed an array named args which contains any parameters that may have been specified.

Syntax - Creating a Function

Alchemy

Copy

CREATE FUNCTION func_name
  [REFERENCES assembly_name;]
  [REFERENCES ... ;]
  [USING name_space, ...]
  BEGIN
  [input_object_list = args;]
  [C# code]
  RETURN value;
END;

Syntax - Executing a Function

Alchemy

Copy

$return_val = func_name([input_data_object, ...]);

Nomenclature

func_name	Specifies the function name. Must begin with a letter, however, the remainder of the name may contain letters (A - Z, a - z), numbers (0 - 9), underscores (_) and full stops (.).
assembly_name	Optional location of assemblies that the function uses.
name_space	Optional library that the function uses.
input_object_list	A local copy of the objects passed to the function.
args[]	An array of object(s) (ie. input_data_object, ...) passed to the function. This array is of type System.Object[]. If the function is passed only one argument, the value of the argument can be retrieved using the code: string firstArg = args[0];
value	The return value of the function. Functions must always return a number or non-null string.
return_val	The return value of the function (value), which can be used as a variable or parameter to call other functions.
input_data_object	Zero or more variables that the function operates on.

Example

The following example is from USGS and operates on their Tiger Data California dataset. The column cfcc is passed to the function getType, which modifies any leading "P"s to "A"s, and then returns a value based on the rest of the data in the cells. The returned value is stored in FUNC_CLASS.

Alchemy

Copy

CREATE FUNCTION getType
BEGIN
  string kind = args[0].ToString();
  if (kind.StartsWith("P"))
    kind = "A" + kind.Substring(1);
  if (kind.StartsWith("A1"))
    return 0;
  else if (kind.StartsWith("A2"))
    return 1;
  else if (kind.StartsWith("A3"))
    return 2;
  else if (kind.StartsWith("A4"))
    return 3;
  else if (kind.StartsWith("A5"))
    return 4;
  else if (kind.StartsWith("A6"))
    return 5;
  return 6;
END;

IMPORT STREETS [
  ... ,
  FUNC_CLASS = getType(%cfcc),
  ... ;
COMMIT;

Note that it is also possible to define a local variable within a pair of braces ({..}). This capability allows more advanced logic than would otherwise be possible within the import statement itself. For example, using the code above:

Alchemy

Copy

IMPORT STREETS [
  ... ,
  FUNC_CLASS = getType(%cfcc),
  ... ;

Can be replaced with the following using a local variable:

Alchemy

Copy

IMPORT STREETS {
  ...;
  $type = getType(%cfcc);
  ...;
}
[
  ... ,
  FUNC_CLASS = $type,
  ... ;
]