DEV Community

Abhi-Kmr2046
Abhi-Kmr2046

Posted on

Creating New Function in Apache AGE

#apacheage #postgres

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';
Enter fullscreen mode Exit fullscreen mode

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));
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Loading age extension

Start Server:

bin/pg_ctl -D demo -l logfile start
Enter fullscreen mode Exit fullscreen mode

Run psql

bin/psql <dbname>
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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

DROP EXTENSION age CASCADE;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

pawsql profile image

🚀 Boosting TPC-H Q2 Query Performance by 1000x times: PawSQL Optimization Techniques

Tomas@PawSQL -

mspilari profile image

Fullstack app: Developing the Back-End with Spring Boot and PostgreSQL

Matheus Bernardes Spilari -

franckpachot profile image

YugabyteDB Connection Manager: a Database Resident Connection Pool with Shared Processes

Franck Pachot -

dbvismarketing profile image

PostgreSQL and DbVisualizer for Better Inventory Management

DbVisualizer -