Extension for PostgreSQL cast jsonb to hstore WIP
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
Attachments:
cast_jsonb_to_hstore.patchapplication/x-patch; name=cast_jsonb_to_hstore.patchDownload
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
On 2024-04-02 Tu 07:07, ShadowGhost wrote:
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!
One reason we don't have such a cast is that hstore has a flat
structure, while json is tree structured, and it's not always an object
/ hash. Thus it's easy to reliably cast hstore to json but far less easy
to cast json to hstore in the general case.
What do you propose to do in the case or json consisting of scalars, or
arrays, or with nested elements?
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
At the moment, this cast supports only these structures, as it was enough
for my tasks:
{str:numeric}
{str:str}
{str:bool}
{str:null}
But it's a great idea and I'll think about implementing it.
вт, 2 апр. 2024 г. в 19:48, Andrew Dunstan <andrew@dunslane.net>:
Show quoted text
On 2024-04-02 Tu 07:07, ShadowGhost wrote:
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!One reason we don't have such a cast is that hstore has a flat
structure, while json is tree structured, and it's not always an object
/ hash. Thus it's easy to reliably cast hstore to json but far less easy
to cast json to hstore in the general case.What do you propose to do in the case or json consisting of scalars, or
arrays, or with nested elements?cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
On 2024-04-02 Tu 11:43, ShadowGhost wrote:
At the moment, this cast supports only these structures, as it was
enough for my tasks:
{str:numeric}
{str:str}
{str:bool}
{str:null}
But it's a great idea and I'll think about implementing it.
Please don't top-post on the PostgreSQL lists. See
<https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics>
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.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
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 Violin
Antoine
On Mon, Jul 15, 2024 at 12:42 PM Andrew Dunstan <andrew@dunslane.net> wrote:
Show quoted text
On 2024-04-02 Tu 11:43, ShadowGhost wrote:
At the moment, this cast supports only these structures, as it was enough
for my tasks:
{str:numeric}
{str:str}
{str:bool}
{str:null}
But it's a great idea and I'll think about implementing it.Please don't top-post on the PostgreSQL lists. See
<https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics>
<https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics>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.cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
On Mon, Jul 15, 2024 at 12:44 PM Антуан Виолин <violin.antuan@gmail.com>
wrote:
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 ViolinAntoine
On Mon, Jul 15, 2024 at 12:42 PM Andrew Dunstan <andrew@dunslane.net>
wrote:On 2024-04-02 Tu 11:43, ShadowGhost wrote:
At the moment, this cast supports only these structures, as it was enough
for my tasks:
{str:numeric}
{str:str}
{str:bool}
{str:null}
But it's a great idea and I'll think about implementing it.Please don't top-post on the PostgreSQL lists. See
<https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics>
<https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics>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.cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Hi! I agree in some cases this cast can be useful.
I Have several comments about the patch:
1)I think we should call pfree on pairs(now we call palloc, but not pfree)
2)I think we should add error handling of load_external_function or maybe
rewrite using of DirectFunctionCall
3)i think we need replace all strdup occurences to pstrdup
4)why such a complex system , you first make global variables there to load
a link to functions there, and then wrap this pointer to a function through
a define?
5) postgres=# SELECT '{"aaa": "first_value", "aaa":
"second_value"}'::jsonb::hstore;
hstore
-----------------------
"aaa"=>"second_value"
(1 row)
is it documented behaviour?