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.
Leave a Reply
Want to join the discussion?Feel free to contribute!