How to return a jsonb object from a PostgreSQL c extension function?

Question

How can I return a simple jsonb object in a PostgreSQL function written in C?

I don't know enough about postgres server side programming. And below is my attempt to return a simple json/jsonb object based on the C source code for hstore_to_jsonb_loose, which is the closest example I can find. I am trying to return {"hi": -101} from the C function, but got an error:

=> ERROR: unexpected jsonb type as object key

Can anyone help exaplain how to get this right?

My C code is:

PG_FUNCTION_INFO_V1(test_return_jsonb);
Datum
test_return_jsonb( PG_FUNCTION_ARGS) {

    JsonbParseState *state = NULL;
    JsonbValue *res;
    StringInfoData tmp;
    initStringInfo(&tmp);
    (void) pushJsonbValue(&state, WJB_BEGIN_OBJECT, NULL);

    JsonbValue key, val;
    //key
    key.type = jbvString;
    key.val.string.len = 2;
    key.val.string.val = "hi";

    Datum numd;
    //value
    val.type = jbvNumeric;
    numd = DirectFunctionCall3(numeric_in, CStringGetDatum("-101"), //!tmp.data),
    ObjectIdGetDatum(InvalidOid), Int32GetDatum(-1));
    val.val.numeric = DatumGetNumeric(numd);

    (void) pushJsonbValue(&state, WJB_VALUE, &val);

    res = pushJsonbValue(&state, WJB_END_OBJECT, NULL);

    PG_RETURN_POINTER(JsonbValueToJsonb(res));
}

And the SQL interface code is:

CREATE OR REPLACE FUNCTION test_return_jsonb()
RETURNS jsonb
AS '$libdir/pgtest', 'test_return_jsonb'
LANGUAGE 'c' IMMUTABLE STRICT COST 100; -- Guessed cost

This is with PostgreSQL 12 and Ubuntu 18.04 LTS.

Answer 1

I'm learning too currently and encountered the same issue. I solved it like following (not sure if this is the right way, but it works for now):

// Defined in "/src/backend/utils/adt/numeric.c"
extern Datum int8_numeric(PG_FUNCTION_ARGS);
extern Datum float8_numeric(PG_FUNCTION_ARGS);
extern Datum numeric_int8(PG_FUNCTION_ARGS);
extern Datum numeric_float8(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(test_return_jsonb);
Datum test_return_jsonb(PG_FUNCTION_ARGS) {
  JsonbPair *pair = palloc(sizeof(JsonbPair));
  pair->key.type = jbvString;
  pair->key.val.string.len = 3;
  pair->key.val.string.val = "foo";
  pair->value.type = jbvNumeric;
  pair->value.val.numeric = DatumGetNumeric(DirectFunctionCall1(int8_numeric, (int64_t)100));

  JsonbValue *object = palloc(sizeof(JsonbValue));
  object->type = jbvObject;
  object->val.object.nPairs = 1;
  object->val.object.pairs = pair;

  PG_RETURN_POINTER(JsonbValueToJsonb(object));
}