I wrote:
> OK. Nobody has spoken against the 0001 patch (replace errors with
> return-a-null), so I think I'll go ahead and commit that one.
> Then I'll return to this thread with a fleshed-out patch for 0002.
0001 is pushed, and as promised, here's a version of 0002 extended
to cover all the string-category types.
However ... the more I play with 0002, the less enchanted I get.
The problem is that there are two different use-cases to serve:
1. General conversion of any jsonb value to string form.
2. Conversion of a jsonb scalar string to a SQL string.
For use-case #1, it makes sense that we do
regression=# select '"hello"'::jsonb::text;
text
---------
"hello"
(1 row)
but for use-case #2, you'd probably prefer that the quotes
weren't included. (We can't do that in use-case #1 because
the string contents might look too much like some other
sort of JSON value.) So it seems like two separate conversion
functions are needed to serve these two use-cases, and for
better or worse we've already decided that casting jsonb to text
is meant for use-case #1. (It was sort of a decision by default,
I suspect, but not deciding is still a decision.)
What I am realizing is that "JSON null becomes SQL NULL" is a rule
that is adapted to use-case #2 but not so much to use-case #1.
For example, the existing behavior
regression=# select null::jsonb::text;
text
------
(1 row)
regression=# select 'null'::jsonb::text;
text
------
null
(1 row)
actually makes plenty of sense if you hope to be able to round-trip
the result. It's only after rejecting non-scalar JSON values that
it makes sense to special-case a JSON null.
So here's the patch, just because I promised it, but I'm now
thinking about withdrawing it.
What would make more sense for use-case #2 is something that
produces NULL for JSON null, a de-quoted string for a JSON
string value, and an error otherwise. The ->> operator is
about halfway there (it won't throw an error for non-scalar
input), but of course that only works when the value you want
to extract is in a JSON object field. I guess what would
be wanted is a new function f(jsonb) returns text, but I'm
unsure about a good name.
regards, tom lane
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 8394a20e0e..e38aebd848 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -2034,6 +2034,10 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype)
elog(ERROR, "unknown jsonb type: %d", (int) type);
}
+/*
+ * Assorted jsonb-to-scalar-type conversion functions.
+ */
+
Datum
jsonb_bool(PG_FUNCTION_ARGS)
{
@@ -2222,6 +2226,74 @@ jsonb_float8(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(retValue);
}
+/* Note: this is also used for jsonb-to-varchar */
+Datum
+jsonb_text(PG_FUNCTION_ARGS)
+{
+ Jsonb *in = PG_GETARG_JSONB_P(0);
+ JsonbValue v;
+ StringInfoData jtext;
+
+ /* Convert scalar null to SQL null */
+ if (JsonbExtractScalar(&in->root, &v) && v.type == jbvNull)
+ {
+ PG_FREE_IF_COPY(in, 0);
+ PG_RETURN_NULL();
+ }
+
+ /* Every other case acts like jsonb_out() */
+ initStringInfo(&jtext);
+ (void) JsonbToCString(&jtext, &in->root, VARSIZE(in));
+
+ PG_FREE_IF_COPY(in, 0);
+
+ PG_RETURN_TEXT_P(cstring_to_text_with_len(jtext.data, jtext.len));
+}
+
+Datum
+jsonb_bpchar(PG_FUNCTION_ARGS)
+{
+ /*
+ * This is really equivalent to jsonb_text, but it must be a separate C
+ * function to keep opr_sanity.sql from complaining.
+ */
+ return jsonb_text(fcinfo);
+}
+
+Datum
+jsonb_name(PG_FUNCTION_ARGS)
+{
+ Jsonb *in = PG_GETARG_JSONB_P(0);
+ Name result;
+ JsonbValue v;
+ StringInfoData jtext;
+ int len;
+
+ /* Convert scalar null to SQL null */
+ if (JsonbExtractScalar(&in->root, &v) && v.type == jbvNull)
+ {
+ PG_FREE_IF_COPY(in, 0);
+ PG_RETURN_NULL();
+ }
+
+ /* Every other case acts like jsonb_out() */
+ initStringInfo(&jtext);
+ (void) JsonbToCString(&jtext, &in->root, VARSIZE(in));
+
+ PG_FREE_IF_COPY(in, 0);
+
+ /* Truncate oversize input */
+ len = jtext.len;
+ if (len >= NAMEDATALEN)
+ len = pg_mbcliplen(jtext.data, len, NAMEDATALEN - 1);
+
+ /* We use palloc0 here to ensure result is zero-padded */
+ result = (Name) palloc0(NAMEDATALEN);
+ memcpy(NameStr(*result), jtext.data, len);
+
+ PG_RETURN_NAME(result);
+}
+
/*
* Convert jsonb to a C-string stripping quotes from scalar strings.
*/
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index a26ba34e86..1bd40d8fac 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -512,7 +512,7 @@
{ castsource => 'jsonb', casttarget => 'json', castfunc => '0',
castcontext => 'a', castmethod => 'i' },
-# jsonb to numeric and bool types
+# jsonb to various scalar types
{ castsource => 'jsonb', casttarget => 'bool', castfunc => 'bool(jsonb)',
castcontext => 'e', castmethod => 'f' },
{ castsource => 'jsonb', casttarget => 'numeric', castfunc => 'numeric(jsonb)',
@@ -527,6 +527,15 @@
castcontext => 'e', castmethod => 'f' },
{ castsource => 'jsonb', casttarget => 'float8', castfunc => 'float8(jsonb)',
castcontext => 'e', castmethod => 'f' },
+# these casts replace implicit COERCEVIAIO casts, so must be assignment-level:
+{ castsource => 'jsonb', casttarget => 'text', castfunc => 'text(jsonb)',
+ castcontext => 'a', castmethod => 'f' },
+{ castsource => 'jsonb', casttarget => 'varchar', castfunc => 'varchar(jsonb)',
+ castcontext => 'a', castmethod => 'f' },
+{ castsource => 'jsonb', casttarget => 'bpchar', castfunc => 'bpchar(jsonb)',
+ castcontext => 'a', castmethod => 'f' },
+{ castsource => 'jsonb', casttarget => 'name', castfunc => 'name(jsonb)',
+ castcontext => 'a', castmethod => 'f' },
# range to multirange
{ castsource => 'int4range', casttarget => 'int4multirange',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 18560755d2..350ba1f800 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4705,6 +4705,18 @@
{ oid => '2580', descr => 'convert jsonb to float8',
proname => 'float8', prorettype => 'float8', proargtypes => 'jsonb',
prosrc => 'jsonb_float8' },
+{ oid => '8079', descr => 'convert jsonb to text',
+ proname => 'text', prorettype => 'text', proargtypes => 'jsonb',
+ prosrc => 'jsonb_text' },
+{ oid => '8080', descr => 'convert jsonb to varchar',
+ proname => 'varchar', prorettype => 'varchar', proargtypes => 'jsonb',
+ prosrc => 'jsonb_text' },
+{ oid => '8081', descr => 'convert jsonb to char(n)',
+ proname => 'bpchar', prorettype => 'bpchar', proargtypes => 'jsonb',
+ prosrc => 'jsonb_bpchar' },
+{ oid => '8082', descr => 'convert jsonb to name',
+ proname => 'name', prorettype => 'name', proargtypes => 'jsonb',
+ prosrc => 'jsonb_name' },
# formatting
{ oid => '1770', descr => 'format timestamp with time zone to text',
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 2baff931bf..d7a3bc299c 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5781,3 +5781,75 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
12345
(1 row)
+select 'true'::jsonb::text;
+ text
+------
+ true
+(1 row)
+
+select '1.0'::jsonb::text;
+ text
+------
+ 1.0
+(1 row)
+
+select '"hello"'::jsonb::text;
+ text
+---------
+ "hello"
+(1 row)
+
+select 'null'::jsonb::text;
+ text
+------
+
+(1 row)
+
+select '[1.0]'::jsonb::text;
+ text
+-------
+ [1.0]
+(1 row)
+
+select '{"a": "b"}'::jsonb::text;
+ text
+------------
+ {"a": "b"}
+(1 row)
+
+select 'true'::jsonb::varchar;
+ varchar
+---------
+ true
+(1 row)
+
+select '1.0'::jsonb::varchar;
+ varchar
+---------
+ 1.0
+(1 row)
+
+select '"hello"'::jsonb::varchar;
+ varchar
+---------
+ "hello"
+(1 row)
+
+select 'null'::jsonb::varchar;
+ varchar
+---------
+
+(1 row)
+
+select '[1.0]'::jsonb::varchar;
+ varchar
+---------
+ [1.0]
+(1 row)
+
+select '{"a": "b"}'::jsonb::varchar;
+ varchar
+------------
+ {"a": "b"}
+(1 row)
+
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 544bb610e2..b391b1555a 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1572,3 +1572,15 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+select 'true'::jsonb::text;
+select '1.0'::jsonb::text;
+select '"hello"'::jsonb::text;
+select 'null'::jsonb::text;
+select '[1.0]'::jsonb::text;
+select '{"a": "b"}'::jsonb::text;
+select 'true'::jsonb::varchar;
+select '1.0'::jsonb::varchar;
+select '"hello"'::jsonb::varchar;
+select 'null'::jsonb::varchar;
+select '[1.0]'::jsonb::varchar;
+select '{"a": "b"}'::jsonb::varchar;
