Creating a PostgreSQL procedural language – Part 5 – Returning Results

This example will just be focusing on returning data from user defined functions, specifically returning a value as opposed to using OUT parameters.  The full code is on Github.

To quickly review, PL/Julia calls jl_eval_string() to execute Julia code and captures the returning jl_value_t data structure, which contains the result of the Julia code executed.  The result needs to be extracted from the jl_value_t data structure and then handled before returning the value to the calling SQL statement.

First we will create a helper function to convert a C string into the data type specified by the RETURN clause of the CREATE FUNCTION statement.

static Datum
cstring_to_type(char * input, Oid typeoid)
{
    HeapTuple typetuple;
    Form_pg_type pg_type_entry;
    Datum ret;

    typetuple = SearchSysCache(TYPEOID, ObjectIdGetDatum(typeoid), 0, 0, 0);
    pg_type_entry = (Form_pg_type) GETSTRUCT(typetuple);
    ret = OidFunctionCall3(pg_type_entry->typinput,
            CStringGetDatum(input), 0, -1);
    ReleaseSysCache(typetuple);
    PG_RETURN_DATUM(ret);
}

Let’s modify the user defined function test_julia() from before to now return a FLOAT4 value:

CREATE OR REPLACE FUNCTION test_julia(val INTEGER)
RETURNS FLOAT4 AS $$
    sqrt(val);
$$ LANGUAGE pljulia;

Now we will add some new code after calling jl_eval_string(). The results of the Julia code is captured and the jl_value_t structure needs to be examined to determine what kind of data is in it.  In this example, I only demonstrate how to check if the return type is floating point number.  If the returned data is verified to be a floating point (jl_float64_type), then convert it to a C string called buffer using a snprintf() call.

jl_value_t *ret;
ret = jl_eval_string(compiled_code);
if (jl_typeis(ret, jl_float64_type))
{
    double ret_unboxed = jl_unbox_float64(ret);

    buffer = (char *) palloc0((DOUBLE_LEN + 1) * sizeof(char));
    snprintf(buffer, DOUBLE_LEN, "%f", ret_unboxed);
}

We can now call the cstring_to_type() function with the results of jl_eval_string() copied into buffer and finally return from the user defined function.

PG_RETURN_DATUM(cstring_to_type(buffer, procedure_struct->prorettype));

Try executing the latest test_julia() user defined function again:

julia=# select test_julia(8);

test_julia
------------
2.828427
(1 row)

This is where I’ll stop blogging about PL/Julia development for the time being.  There are still a number of things left to do to make PL/Julia more robust and fully functional.  You may have also noticed various WARNING messages if you’ve been trying the code.  Please take a look at the issues list and feel free to try lending a hand.