Creating New Function in Apache AGE

We can add new functions in Apache age. To do this we will have to modify a few files and compile the Apache AGE code.
To demonstrate this we'll create a gcd function which is not available in apache age.

age--1.1.0.sql

CREATE FUNCTION ag_catalog.age_gcd(agtype, agtype)
RETURNS agtype
LANGUAGE c
PARALLEL SAFE
AS 'MODULE_PATHNAME';

This declares the function we are creating. Here we have two arguments of type agetype because apache age only supports agetype as arguments.

src/backend/utils/adt/agtype.c

We'll add our function in the above file. Just add the given code anywhere in the file. This is syntax of adding a function to apache age.

PG_FUNCTION_INFO_V1(age_gcd);

Datum age_gcd(PG_FUNCTION_ARGS)
{
    agtype *agt_arg0 = AG_GET_ARG_AGTYPE_P(0);
    agtype *agt_arg1 = AG_GET_ARG_AGTYPE_P(1);
    agtype_value *arg0;
    agtype_value *arg1;
    agtype_value agtv_result;

    /* gcd supports agtype integer as input */
    arg0 = get_ith_agtype_value_from_container(&agt_arg0->root, 0);
    arg1 = get_ith_agtype_value_from_container(&agt_arg1->root, 0);

    /* only integers are allowed */
    if (arg0->type != AGTV_INTEGER || arg1->type != AGTV_INTEGER )
        ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                        errmsg("arguments must resolve to an integer")));

    /* check for agtype null */
    if (arg0->type == AGTV_NULL)
        PG_RETURN_POINTER(agt_arg1);
    if (arg1->type == AGTV_NULL)
        PG_RETURN_POINTER(agt_arg0);

    agtv_result.val.numeric = DatumGetNumeric(
                DirectFunctionCall2(numeric_gcd,
                                    DirectFunctionCall1(int8_numeric,
                                                        Int64GetDatum(arg0->val.int_value)),
                                    DirectFunctionCall1(int8_numeric,
                                                        Int64GetDatum(arg1->val.int_value))));
    agtv_result.type = AGTV_NUMERIC;

    PG_RETURN_POINTER(agtype_value_to_agtype(&agtv_result));
}

Here to calculate gcd, numeric_gcd is passed in the DirectFunctionCall2. It is a postgress function used to calculate gcd. Here we can directly utilize that to make our own gcd function of Apache AGE.

Compilation

make uninstall
make clean
make PG_CONFIG=/home/abhis/age_installation/postgresql-14.7/bin/pg_config install

Loading age extension

Start Server:

bin/pg_ctl -D demo -l logfile start

Run psql

bin/psql <dbname>

After the code is successfully compiled and psql command line is started we need to load the age extension.

CREATE EXTENSION age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

This sometimes might not work. In that case, run the command given below and rerun the command to load extension.

DROP EXTENSION age CASCADE;

Query

We can use any of the below syntax to run the query.

SELECT * FROM age_gcd('12', '9');

SELECT * FROM cypher('expr', $$
    RETURN gcd(15, 25)
$$) as (result agtype);