Hello all. Recently, when working with the hstore and json formats, I came across the fact that PostgreSQL has a cast of hstore to json, but there is no reverse cast. I thought it might make it more difficult to work with these formats. And I decided to make a cast json in the hstore. I used the built-in jsonb structure to create it and may have introduced methods to increase efficiency by 25% than converting the form jsonb->text->hstore. Which of course is a good fact. I also wrote regression tests to check the performance. I think this extension will improve the work with jsonb and hstore in PostgreSQL. If you've read this far, thank you for your interest, and I hope you enjoy this extension! ---- Antoine
From 334bf26cbea8d3ccd1821a7ecd5bd1134d9b1641 Mon Sep 17 00:00:00 2001 From: Antoine Violin <a.violin@g.nsu.ru> Date: Mon, 25 Mar 2024 17:34:23 +0700 Subject: [PATCH v1] Add cast jsonb to hstore
--- contrib/cast_jsonb_to_hstore/Makefile | 18 +++ .../cast_jsonb_to_hstore--1.0.sql | 16 +++ .../cast_jsonb_to_hstore.c | 119 ++++++++++++++++++ .../cast_jsonb_to_hstore.control | 5 + .../expected/cast_jsonb_to_hstore.out | 71 +++++++++++ .../sql/cast_jsonb_to_hstore.sql | 27 ++++ 6 files changed, 256 insertions(+) create mode 100644 contrib/cast_jsonb_to_hstore/Makefile create mode 100644 contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore--1.0.sql create mode 100644 contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore.c create mode 100644 contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore.control create mode 100644 contrib/cast_jsonb_to_hstore/expected/cast_jsonb_to_hstore.out create mode 100644 contrib/cast_jsonb_to_hstore/sql/cast_jsonb_to_hstore.sql diff --git a/contrib/cast_jsonb_to_hstore/Makefile b/contrib/cast_jsonb_to_hstore/Makefile new file mode 100644 index 0000000000..96db73215a --- /dev/null +++ b/contrib/cast_jsonb_to_hstore/Makefile @@ -0,0 +1,18 @@ +MODULES = cast_jsonb_to_hstore +EXTENSION = cast_jsonb_to_hstore +DATA = cast_jsonb_to_hstore--1.0.sql +PGFILEDESC = "Convert data between different character sets" +REGRESS = cast_jsonb_to_hstore +EXTRA_INSTALL = contrib/hstore + +ifdef USE_PGXS +PG_CONFIG = PG_CONFIG +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +PG_CPPFLAGS = -I$(top_srcdir)/contrib +subdir = contrib/cast_jsonb_to_hstore +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif \ No newline at end of file diff --git a/contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore--1.0.sql b/contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore--1.0.sql new file mode 100644 index 0000000000..db31fedf48 --- /dev/null +++ b/contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore--1.0.sql @@ -0,0 +1,16 @@ +\echo Use "CREATE EXTENSION cast_jsonb_to_hstore" to load this file. \quit +CREATE OR REPLACE FUNCTION jsonb_to_hstore(j0 jsonb) +RETURNS hstore +AS '$libdir/cast_jsonb_to_hstore', 'jsonb_to_hstore' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OR REPLACE FUNCTION json_to_hstore(j0 json) +RETURNS hstore AS +$BODY$ + SELECT hstore(array_agg(key), array_agg(value)) + FROM json_each_text(j0) +$BODY$ +LANGUAGE 'sql' IMMUTABLE; + +CREATE CAST (jsonb AS hstore) WITH FUNCTION jsonb_to_hstore(jsonb) AS IMPLICIT; +CREATE CAST (json AS hstore) WITH FUNCTION json_to_hstore(json) AS IMPLICIT; \ No newline at end of file diff --git a/contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore.c b/contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore.c new file mode 100644 index 0000000000..c174414896 --- /dev/null +++ b/contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore.c @@ -0,0 +1,119 @@ +#include "postgres.h" +#include "hstore/hstore.h" +#include "utils/jsonb.h" + +PG_MODULE_MAGIC; + +typedef int (*hstoreUniquePairs_t) (Pairs *a, int32 l, int32 *buflen); +static hstoreUniquePairs_t hstoreUniquePairs_p; +typedef HStore *(*hstorePairs_t) (Pairs *pairs, int32 pcount, int32 buflen); +static hstorePairs_t hstorePairs_p; +typedef size_t (*hstoreCheckKeyLen_t) (size_t len); +static hstoreCheckKeyLen_t hstoreCheckKeyLen_p; +typedef size_t (*hstoreCheckValLen_t) (size_t len); +static hstoreCheckValLen_t hstoreCheckValLen_p; + +void +_PG_init(void) +{ + AssertVariableIsOfType(&hstoreUniquePairs, hstoreUniquePairs_t); + hstoreUniquePairs_p = (hstoreUniquePairs_t) + load_external_function("$libdir/hstore", "hstoreUniquePairs", + true, NULL); + AssertVariableIsOfType(&hstorePairs, hstorePairs_t); + hstorePairs_p = (hstorePairs_t) + load_external_function("$libdir/hstore", "hstorePairs", + true, NULL); + AssertVariableIsOfType(&hstoreCheckKeyLen, hstoreCheckKeyLen_t); + hstoreCheckKeyLen_p = (hstoreCheckKeyLen_t) + load_external_function("$libdir/hstore", "hstoreCheckKeyLen", + true, NULL); + AssertVariableIsOfType(&hstoreCheckValLen, hstoreCheckValLen_t); + hstoreCheckValLen_p = (hstoreCheckValLen_t) + load_external_function("$libdir/hstore", "hstoreCheckValLen", + true, NULL); +} + +#define hstoreUniquePairs hstoreUniquePairs_p +#define hstorePairs hstorePairs_p +#define hstoreCheckKeyLen hstoreCheckKeyLen_p +#define hstoreCheckValLen hstoreCheckValLen_p + +PG_FUNCTION_INFO_V1(jsonb_to_hstore); + +Datum +jsonb_to_hstore(PG_FUNCTION_ARGS) +{ + int32 buflen; + int32 i; + int32 pcount; + HStore *out; + Pairs *pairs; + + Jsonb *in = PG_GETARG_JSONB_P(0); + JsonbContainer *jsonb = &in->root; + + JsonbValue v; + JsonbIterator *it; + JsonbIteratorToken r; + + it = JsonbIteratorInit(jsonb); + r = JsonbIteratorNext(&it, &v, true); + + i = 0; + pcount = v.val.object.nPairs; + pairs = palloc(pcount * sizeof(Pairs)); + + if (r == WJB_BEGIN_OBJECT) + { + while ((r = JsonbIteratorNext(&it, &v, true)) != WJB_DONE) + { + if (r == WJB_KEY) + { + //key- v, value- val + JsonbValue val; + if (JsonbIteratorNext(&it, &val, true) == WJB_VALUE) + { + pairs[i].key = pstrdup(v.val.string.val); + pairs[i].keylen = hstoreCheckKeyLen(v.val.string.len); + pairs[i].needfree = true; + + switch (val.type) + { + case jbvNumeric: + pairs[i].val = pstrdup((numeric_normalize(val.val.numeric))); + pairs[i].vallen = hstoreCheckValLen(strlen(pairs[i].val)); + pairs[i].isnull = false; + break; + case jbvString: + pairs[i].val = strdup((val.val.string.val)); + pairs[i].vallen = hstoreCheckValLen(val.val.string.len); + pairs[i].isnull = false; + break; + case jbvNull: + pairs[i].isnull = true; + break; + case jbvBool: + if (val.val.boolean) + { + pairs[i].val = "true"; + pairs[i].vallen = hstoreCheckValLen(strlen("true")); + } + else + { + pairs[i].val = "false"; + pairs[i].vallen = hstoreCheckValLen(strlen("false")); + } + pairs[i].isnull = false; + default: + break; + } + } + } + ++i; + } + } + pcount = hstoreUniquePairs(pairs, pcount, &buflen); + out = hstorePairs(pairs, pcount, buflen); + PG_RETURN_POINTER(out); +} diff --git a/contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore.control b/contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore.control new file mode 100644 index 0000000000..fb302a0f6a --- /dev/null +++ b/contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore.control @@ -0,0 +1,5 @@ +comment = 'function for convert json hstore' +default_version = '1.0' +module_pathname = '$libdir/cast_jsonb_to_hstore' +relocatable = true +requires = 'hstore' \ No newline at end of file diff --git a/contrib/cast_jsonb_to_hstore/expected/cast_jsonb_to_hstore.out b/contrib/cast_jsonb_to_hstore/expected/cast_jsonb_to_hstore.out new file mode 100644 index 0000000000..9b609285bb --- /dev/null +++ b/contrib/cast_jsonb_to_hstore/expected/cast_jsonb_to_hstore.out @@ -0,0 +1,71 @@ +/* + * The file is used to test cast_jsonb_to_hstore.sql +*/ +CREATE EXTENSION hstore; +CREATE EXTENSION cast_jsonb_to_hstore; +SELECT '{"aaa":"absr"}'::jsonb::hstore; + hstore +--------------- + "aaa"=>"absr" +(1 row) + +SELECT '{"aaa":"absr"}'::jsonb::hstore->'aaa'; + ?column? +---------- + absr +(1 row) + +SELECT '{"aaa":1234}'::jsonb::hstore; + hstore +--------------- + "aaa"=>"1234" +(1 row) + +SELECT '{"aaa":1234}'::jsonb::hstore->'aaa'; + ?column? +---------- + 1234 +(1 row) + +SELECT '{"aaa":true}'::jsonb::hstore; + hstore +--------------- + "aaa"=>"true" +(1 row) + +SELECT '{"aaa":true}'::jsonb::hstore->'aaa'; + ?column? +---------- + true +(1 row) + +SELECT '{"aaa":null}'::jsonb::hstore; + hstore +------------- + "aaa"=>NULL +(1 row) + +SELECT '{"aaa":null}'::jsonb::hstore->'aaa'; + ?column? +---------- + +(1 row) + +SELECT '{"1234":"absr"}'::jsonb::hstore; + hstore +---------------- + "1234"=>"absr" +(1 row) + +SELECT E'{"a": "\'ght"}'::jsonb::hstore; + hstore +------------- + "a"=>"'ght" +(1 row) + +SELECT E'{"a": "\'ght"}'::jsonb::hstore->'a'; + ?column? +---------- + 'ght +(1 row) + diff --git a/contrib/cast_jsonb_to_hstore/sql/cast_jsonb_to_hstore.sql b/contrib/cast_jsonb_to_hstore/sql/cast_jsonb_to_hstore.sql new file mode 100644 index 0000000000..e04ed8056a --- /dev/null +++ b/contrib/cast_jsonb_to_hstore/sql/cast_jsonb_to_hstore.sql @@ -0,0 +1,27 @@ +/* + * The file is used to test cast_jsonb_to_hstore.sql +*/ +CREATE EXTENSION hstore; +CREATE EXTENSION cast_jsonb_to_hstore; + +SELECT '{"aaa":"absr"}'::jsonb::hstore; + +SELECT '{"aaa":"absr"}'::jsonb::hstore->'aaa'; + +SELECT '{"aaa":1234}'::jsonb::hstore; + +SELECT '{"aaa":1234}'::jsonb::hstore->'aaa'; + +SELECT '{"aaa":true}'::jsonb::hstore; + +SELECT '{"aaa":true}'::jsonb::hstore->'aaa'; + +SELECT '{"aaa":null}'::jsonb::hstore; + +SELECT '{"aaa":null}'::jsonb::hstore->'aaa'; + +SELECT '{"1234":"absr"}'::jsonb::hstore; + +SELECT E'{"a": "\'ght"}'::jsonb::hstore; + +SELECT E'{"a": "\'ght"}'::jsonb::hstore->'a'; \ No newline at end of file -- 2.34.1