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 <[email protected]>
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
