C语言如何在PostgreSQL中检索输入JSONB对象的JsonbPair值？

vc9ivgsu 于 2023-06-05 发布在 PostgreSQL

关注(0)|答案(1)|浏览(126)

我正在尝试编写一个PostgreSQL（11.2）服务器端函数来读取输入JSONB对象的键值对。我这样做（在下面的print_kv_pair中）是通过尝试
1.从输入的jsonb对象中提取JsonPair s
1.遍历键和值并打印它们。
例如，对于'{"a":1, "b": 2}'，我希望它打印

k = "a", v = 1
k = "b", v = 2

但是，代码输出的键字符很奇怪，并且值（1和2）不是我期望的numeric类型。请参阅问题末尾的示例输出。

有人能解释一下如何修复代码并正确地遍历键值对吗？*

PG_FUNCTION_INFO_V1(print_kv_pair);
Datum
print_kv_pair(PG_FUNCTION_ARGS)
{
    //1. extracting JsonbValue
    Jsonb *jb1 = PG_GETARG_JSONB_P(0);
    JsonbIterator *it1;
    JsonbValue  v1;
    JsonbIteratorToken r1;
    JsonbParseState *state = NULL;

    if (jb1 == NULL)
        PG_RETURN_JSONB_P(jb1);

    if (!JB_ROOT_IS_OBJECT(jb1))
        ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("Can only take objects")));

    it1 = JsonbIteratorInit(&jb1->root);
    r1 = JsonbIteratorNext(&it1, &v1, false);

    if (r1 != WJB_BEGIN_OBJECT)
        ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("Iterator was not an object")));

    JsonbValue *object = &v1;
    Assert(object->type == jbvObject);

    //2. iterating through key-value pairs
    JsonbPair  *ptr;
    for (ptr = object->val.object.pairs;
            ptr - object->val.object.pairs < object->val.object.nPairs; ptr++)
    {
        //problem lines!!!
        char *buf = pnstrdup(ptr->key.val.string.val, ptr->key.val.string.len);
        elog(NOTICE, "print_kv_pair(): k = %s", buf);  //debug
        if (ptr->value.type != jbvNumeric) {
            ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("value must be numeric")));
        }
        elog(NOTICE, "print_kv_pair(): v = %s", DatumGetCString(DirectFunctionCall1(numeric_out,
                NumericGetDatum(ptr->value.val.numeric))) ); //debug
    }

    elog(NOTICE, "print_kv_pair(): ok4");

    PG_RETURN_BOOL(true);
}

禁用问题行的输出示例：

=> select print_kv_pair('{"a":1.0, "b": 2.0}'::jsonb);
NOTICE:  print_kv_pair(): k = $�K
ERROR:  value must be numeric

似乎部件1. extracting JsonbVaule工作不正常，提取的值指向无效内存。
（我不是很熟悉JSONB或服务器端PostgreSQL编程。

c

来源：https://stackoverflow.com/questions/55214579/how-to-retrieve-jsonbpair-values-of-input-jsonb-object-in-postgresql

1条答案

按热度按时间

j2qf4p5b1#

模仿www.example.com中的PLyObject_FromJsonbContainer函数https://doxygen.postgresql.org/jsonb__plpython_8c.html#ad2dec423eed378f43e9ca14448cbb243

/*
/home/jian/Desktop/regress_pgsql/print_jsonb_key_value_pair.sql

https://stackoverflow.com/questions/55214579/how-to-retrieve-jsonbpair-values-of-input-jsonb-object-in-postgresql

gcc -I/home/jian/postgres/2023_05_25_beta5421/include/server -fPIC -c /home/jian/Desktop/regress_pgsql/print_jsonb_key_value_pair.c
gcc -shared  -o /home/jian/Desktop/regress_pgsql/print_jsonb_key_value_pair.so /home/jian/Desktop/regress_pgsql/print_jsonb_key_value_pair.o
 
*/
#include "postgres.h"

#include "utils/builtins.h"
#include "utils/array.h"
#include "funcapi.h"
#include "utils/lsyscache.h"
#include "utils/fmgrprotos.h"
#include "utils/jsonb.h"
#include "utils/numeric.h"

PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(print_jsonb_key_value_pair);

Datum 
print_jsonb_key_value_pair(PG_FUNCTION_ARGS)
{
    Jsonb *jb1 = PG_GETARG_JSONB_P(0);   
    JsonbIteratorToken r1;
    JsonbValue  v1;
    JsonbIterator *it1;

    if (jb1 == NULL)
        PG_RETURN_JSONB_P(jb1);

    if (!JB_ROOT_IS_OBJECT(jb1))
        ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("Can only take objects")));

    it1 = JsonbIteratorInit(&jb1->root);
    r1 = JsonbIteratorNext(&it1, &v1, true);

    if (r1 != WJB_BEGIN_OBJECT)
        ereport(ERROR,
                    (errcode(ERRCODE_INVALID_PARAMETER_VALUE)
                    ,errmsg("Iterator was not an object")));

    while ((r1 = JsonbIteratorNext(&it1, &v1, true)) != WJB_DONE)
    {
        if (r1 != WJB_KEY)
            continue;
        Assert(v1.type == jbvString);

        elog(NOTICE,"key: %s",(v1.val.string.val));
        if ((r1 = JsonbIteratorNext(&it1, &v1, true)) != WJB_VALUE)
            elog(ERROR, "unexpected jsonb token: %d", r1);
    
        if (v1.type != jbvNumeric)
            elog(ERROR, "value should be numeric data type");
        // Assert(v1.type == jbvNumeric);
        
        char    *values;
        Datum temp  = (Datum) (v1.val.numeric);

        values  = DatumGetCString(DirectFunctionCall1(numeric_out, temp));

        elog(NOTICE,"numeric values: %s", values);
    }
    PG_RETURN_BOOL(true);
}

测试用例：

select print_jsonb_key_value_pair('{"hello":-1.0000000000000001, "world": 2.011111101}'::jsonb);
select print_jsonb_key_value_pair('{"hello":1e6}'::jsonb);
select print_jsonb_key_value_pair('{"hello":1e-6}'::jsonb);
select print_jsonb_key_value_pair('{"hello":"will_fail_since_value_not_numeric"}'::jsonb);

赞(0）回复(0）举报 2023-06-05

我来回答

C语言如何在PostgreSQL中检索输入JSONB对象的JsonbPair值？

1条答案

相关问题

热门标签

最新问答

C语言 如何在PostgreSQL中检索输入JSONB对象的JsonbPair值？

1条答案

相关问题

热门标签

最新问答

C语言如何在PostgreSQL中检索输入JSONB对象的JsonbPair值？