Re: Extension for PostgreSQL cast jsonb to hstore WIP
Started by Антуан Виолинover 1 year ago1 messages
On 2024-04-03 Wn 04:21, Andrew Dunstan
I don't think a cast that doesn't cater for all the forms json can take
is going to work very well. At the very least you would need to error out
in cases you didn't want to cover, and have tests for all of > > those
errors. But the above is only a tiny fraction of those. If the error cases
are going to be so much more than the cases that work it seems a bit
pointless.
Hi everyone
I changed my mail account to be officially displayed in the correspondence.
I also made an error conclusion if we are given an incorrect value. I
believe that such a cast is needed by PostgreSQL since we already have
several incomplete casts, but they perform their duties well and help in
the right situations.
cheers
Antoine
Attachments:
cast_jsonb_to_hstore2.patchapplication/octet-stream; name=cast_jsonb_to_hstore2.patchDownload
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..5fd0745fa0
--- /dev/null
+++ b/contrib/cast_jsonb_to_hstore/cast_jsonb_to_hstore.c
@@ -0,0 +1,121 @@
+#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;
+ break;
+ default:
+ ereport(ERROR,(errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("Unsupported value type for casting to hstore")));
+ 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