[HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Hi Stephen Can I help with something, it is there some open question? Regards Pavel 2014-09-08 6:27 GMT+02:00 Stephen Frost sfr...@snowman.net: * Pavel Stehule (pavel.steh...@gmail.com) wrote: ignore_nulls in array_to_json_pretty probably is not necessary. On second hand, the cost is zero, and we can have it for API consistency. I'm willing to be persuaded either way on this, really. I do think it would be nice for both array_to_json and row_to_json to be single functions which take default values, but as for if array_to_json has a ignore_nulls option, I'm on the fence and would defer to people who use that function regularly (I don't today). Beyond that, I'm pretty happy moving forward with this patch. Thanks, Stephen
[HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
* Pavel Stehule (pavel.steh...@gmail.com) wrote: Can I help with something, it is there some open question? I had been hoping for a more definitive answer regarding this option for array_to_json, or even a comment about the change to row_to_json. Andrew- any thoughts on this? (that's what the ping on IRC is for :). Thanks, Stephen 2014-09-08 6:27 GMT+02:00 Stephen Frost sfr...@snowman.net: * Pavel Stehule (pavel.steh...@gmail.com) wrote: ignore_nulls in array_to_json_pretty probably is not necessary. On second hand, the cost is zero, and we can have it for API consistency. I'm willing to be persuaded either way on this, really. I do think it would be nice for both array_to_json and row_to_json to be single functions which take default values, but as for if array_to_json has a ignore_nulls option, I'm on the fence and would defer to people who use that function regularly (I don't today). Beyond that, I'm pretty happy moving forward with this patch. signature.asc Description: Digital signature
[HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
On 09/11/2014 08:29 AM, Stephen Frost wrote: * Pavel Stehule (pavel.steh...@gmail.com) wrote: Can I help with something, it is there some open question? I had been hoping for a more definitive answer regarding this option for array_to_json, or even a comment about the change to row_to_json. Andrew- any thoughts on this? (that's what the ping on IRC is for :). The change in row_to_json looks OK, I think. we're replacing an overloading with use of default params, yes? That seems quite reasonable, and users shouldn't notice the difference. There might be a case for optionally suppressing nulls in array_to_json, and it might work reasonably since unlike SQL arrays JSON arrays don't have to be regular (if nested they are arrays of arrays, not multi-dimensional single arrays). OTOH I'm not sure if it's worth doing just for the sake of orthogonality. If someone wants it, then go for it. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Andrew, * Andrew Dunstan (and...@dunslane.net) wrote: On 09/11/2014 08:29 AM, Stephen Frost wrote: * Pavel Stehule (pavel.steh...@gmail.com) wrote: Can I help with something, it is there some open question? I had been hoping for a more definitive answer regarding this option for array_to_json, or even a comment about the change to row_to_json. Andrew- any thoughts on this? (that's what the ping on IRC is for :). The change in row_to_json looks OK, I think. we're replacing an overloading with use of default params, yes? That seems quite reasonable, and users shouldn't notice the difference. Right. Great, thanks. There might be a case for optionally suppressing nulls in array_to_json, and it might work reasonably since unlike SQL arrays JSON arrays don't have to be regular (if nested they are arrays of arrays, not multi-dimensional single arrays). OTOH I'm not sure if it's worth doing just for the sake of orthogonality. If someone wants it, then go for it. Ok. I'll handle updating both of these to remove the overloading and use default params instead, but I'll only add the 'ignore_null' option to row_to_json. Thanks again! Stephen signature.asc Description: Digital signature
[HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
All,
* Stephen Frost (sfr...@snowman.net) wrote:
Ok. I'll handle updating both of these to remove the overloading
and use default params instead, but I'll only add the 'ignore_null'
option to row_to_json.
Barring objections or any issues I find as I go back through it, this is
what I'm planning to push a bit later on tonight.
Thanks!
Stephen
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3a7cfa9..7195df8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10309,11 +10309,13 @@ table2-mapping
/row
row
entry
- literalrow_to_json(record [, pretty_bool])/literal
+ literalrow_to_json(rowval record [, pretty bool [, ignore_nulls bool] ])/literal
/entry
entry
Returns the row as a JSON object. Line feeds will be added between
- level-1 elements if parameterpretty_bool/parameter is true.
+ level-1 elements if parameterpretty_bool/parameter is true. Elements
+ with NULL values will be skipped when parameterignore_nulls/parameter
+ is true.
/entry
entryliteralrow_to_json(row(1,'foo'))/literal/entry
entryliteral{f1:1,f2:foo}/literal/entry
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1bde175..22663c3 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -867,3 +867,17 @@ RETURNS interval
LANGUAGE INTERNAL
STRICT IMMUTABLE
AS 'make_interval';
+
+CREATE OR REPLACE FUNCTION
+ row_to_json(rowval record, pretty boolean DEFAULT false, ignore_nulls boolean DEFAULT false)
+RETURNS json
+LANGUAGE INTERNAL
+STRICT STABLE
+AS 'row_to_json';
+
+CREATE OR REPLACE FUNCTION
+ array_to_json(arrayval anyarray, pretty boolean DEFAULT false)
+RETURNS json
+LANGUAGE INTERNAL
+STRICT STABLE
+AS 'array_to_json';
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 494a028..19d7401 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -79,7 +79,8 @@ static void report_invalid_token(JsonLexContext *lex);
static int report_json_context(JsonLexContext *lex);
static char *extract_mb_char(char *s);
static void composite_to_json(Datum composite, StringInfo result,
- bool use_line_feeds);
+ bool use_line_feeds,
+ bool ignore_nulls);
static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
Datum *vals, bool *nulls, int *valcount,
JsonTypeCategory tcategory, Oid outfuncoid,
@@ -1362,7 +1363,7 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
array_to_json_internal(val, result, false);
break;
case JSONTYPE_COMPOSITE:
- composite_to_json(val, result, false);
+ composite_to_json(val, result, false, false);
break;
case JSONTYPE_BOOL:
outputstr = DatumGetBool(val) ? true : false;
@@ -1591,7 +1592,8 @@ array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds)
* Turn a composite / record into JSON.
*/
static void
-composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
+composite_to_json(Datum composite, StringInfo result, bool use_line_feeds,
+ bool ignore_nulls)
{
HeapTupleHeader td;
Oid tupType;
@@ -1630,6 +1632,12 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
if (tupdesc-attrs[i]-attisdropped)
continue;
+ val = heap_getattr(tuple, i + 1, tupdesc, isnull);
+
+ /* Don't serialize NULL field when we don't want it */
+ if (isnull ignore_nulls)
+ continue;
+
if (needsep)
appendStringInfoString(result, sep);
needsep = true;
@@ -1638,8 +1646,6 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
escape_json(result, attname);
appendStringInfoChar(result, ':');
- val = heap_getattr(tuple, i + 1, tupdesc, isnull);
-
if (isnull)
{
tcategory = JSONTYPE_NULL;
@@ -1688,26 +1694,10 @@ add_json(Datum val, bool is_null, StringInfo result,
}
/*
- * SQL function array_to_json(row)
- */
-extern Datum
-array_to_json(PG_FUNCTION_ARGS)
-{
- Datum array = PG_GETARG_DATUM(0);
- StringInfo result;
-
- result = makeStringInfo();
-
- array_to_json_internal(array, result, false);
-
- PG_RETURN_TEXT_P(cstring_to_text_with_len(result-data, result-len));
-}
-
-/*
* SQL function array_to_json(row, prettybool)
*/
extern Datum
-array_to_json_pretty(PG_FUNCTION_ARGS)
+array_to_json(PG_FUNCTION_ARGS)
{
Datum array = PG_GETARG_DATUM(0);
bool use_line_feeds = PG_GETARG_BOOL(1);
@@ -1721,34 +1711,19 @@ array_to_json_pretty(PG_FUNCTION_ARGS)
}
/*
- * SQL function row_to_json(row)
+ * SQL function row_to_json(rowval record, pretty bool, ignore_nulls bool)
*/
extern Datum
row_to_json(PG_FUNCTION_ARGS)
{
Datum array = PG_GETARG_DATUM(0);
- StringInfo result;
-
- result = makeStringInfo();
-
- composite_to_json(array, result, false);
-
- PG_RETURN_TEXT_P(cstring_to_text_with_len(result-data,
[HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
* Stephen Frost (sfr...@snowman.net) wrote: * Stephen Frost (sfr...@snowman.net) wrote: Ok. I'll handle updating both of these to remove the overloading and use default params instead, but I'll only add the 'ignore_null' option to row_to_json. Barring objections or any issues I find as I go back through it, this is what I'm planning to push a bit later on tonight. Pushed. Thanks! Stephen signature.asc Description: Digital signature
[HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
2014-09-08 6:27 GMT+02:00 Stephen Frost sfr...@snowman.net: * Pavel Stehule (pavel.steh...@gmail.com) wrote: ignore_nulls in array_to_json_pretty probably is not necessary. On second hand, the cost is zero, and we can have it for API consistency. I'm willing to be persuaded either way on this, really. I do think it would be nice for both array_to_json and row_to_json to be single functions which take default values, but as for if array_to_json has a ignore_nulls option, I'm on the fence and would defer to people who use that function regularly (I don't today). Beyond that, I'm pretty happy moving forward with this patch. ok Regards Pavel Thanks, Stephen
[HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Pavel, All,
* Pavel Stehule (pavel.steh...@gmail.com) wrote:
Thank you
I made a few minor adjustments, but the bigger issue (in my view) is
what to do about array_to_json_pretty- seems like we should do the same
there, no? Perhaps you could propose a new patch which incorporates my
minor changes and also removes array_to_json_pretty and makes
array_to_json take an optional argument?
Thoughts?
Thanks,
Stephen
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e50408c..c6c44a9 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10294,11 +10294,13 @@ table2-mapping
/row
row
entry
- literalrow_to_json(record [, pretty_bool])/literal
+ literalrow_to_json(rowval record [, pretty bool [, ignore_nulls bool] ])/literal
/entry
entry
Returns the row as a JSON object. Line feeds will be added between
- level-1 elements if parameterpretty_bool/parameter is true.
+ level-1 elements if parameterpretty_bool/parameter is true. Elements
+ with NULL values will be skipped when parameterignore_nulls/parameter
+ is true.
/entry
entryliteralrow_to_json(row(1,'foo'))/literal/entry
entryliteral{f1:1,f2:foo}/literal/entry
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1bde175..02cf965 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -867,3 +867,10 @@ RETURNS interval
LANGUAGE INTERNAL
STRICT IMMUTABLE
AS 'make_interval';
+
+CREATE OR REPLACE FUNCTION
+ row_to_json(rowval record, pretty boolean DEFAULT false, ignore_nulls boolean DEFAULT false)
+RETURNS json
+LANGUAGE INTERNAL
+STRICT STABLE
+AS 'row_to_json';
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 494a028..ae6028e 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -79,7 +79,8 @@ static void report_invalid_token(JsonLexContext *lex);
static int report_json_context(JsonLexContext *lex);
static char *extract_mb_char(char *s);
static void composite_to_json(Datum composite, StringInfo result,
- bool use_line_feeds);
+ bool use_line_feeds,
+ bool ignore_nulls);
static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
Datum *vals, bool *nulls, int *valcount,
JsonTypeCategory tcategory, Oid outfuncoid,
@@ -1362,7 +1363,7 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
array_to_json_internal(val, result, false);
break;
case JSONTYPE_COMPOSITE:
- composite_to_json(val, result, false);
+ composite_to_json(val, result, false, false);
break;
case JSONTYPE_BOOL:
outputstr = DatumGetBool(val) ? true : false;
@@ -1591,7 +1592,8 @@ array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds)
* Turn a composite / record into JSON.
*/
static void
-composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
+composite_to_json(Datum composite, StringInfo result, bool use_line_feeds,
+ bool ignore_nulls)
{
HeapTupleHeader td;
Oid tupType;
@@ -1630,6 +1632,12 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
if (tupdesc-attrs[i]-attisdropped)
continue;
+ val = heap_getattr(tuple, i + 1, tupdesc, isnull);
+
+ /* Don't serialize NULL field when we don't want it */
+ if (isnull ignore_nulls)
+ continue;
+
if (needsep)
appendStringInfoString(result, sep);
needsep = true;
@@ -1638,8 +1646,6 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
escape_json(result, attname);
appendStringInfoChar(result, ':');
- val = heap_getattr(tuple, i + 1, tupdesc, isnull);
-
if (isnull)
{
tcategory = JSONTYPE_NULL;
@@ -1721,34 +1727,19 @@ array_to_json_pretty(PG_FUNCTION_ARGS)
}
/*
- * SQL function row_to_json(row)
+ * SQL function row_to_json(rowval record, pretty bool, ignore_nulls bool)
*/
extern Datum
row_to_json(PG_FUNCTION_ARGS)
{
Datum array = PG_GETARG_DATUM(0);
- StringInfo result;
-
- result = makeStringInfo();
-
- composite_to_json(array, result, false);
-
- PG_RETURN_TEXT_P(cstring_to_text_with_len(result-data, result-len));
-}
-
-/*
- * SQL function row_to_json(row, prettybool)
- */
-extern Datum
-row_to_json_pretty(PG_FUNCTION_ARGS)
-{
- Datum array = PG_GETARG_DATUM(0);
bool use_line_feeds = PG_GETARG_BOOL(1);
+ bool ignore_nulls = PG_GETARG_BOOL(2);
StringInfo result;
result = makeStringInfo();
- composite_to_json(array, result, use_line_feeds);
+ composite_to_json(array, result, use_line_feeds, ignore_nulls);
PG_RETURN_TEXT_P(cstring_to_text_with_len(result-data, result-len));
}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 5176ed0..5aeadc3 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4203,10 +4203,8 @@ DATA(insert OID =
[HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Hi 2014-09-08 5:48 GMT+02:00 Stephen Frost sfr...@snowman.net: Pavel, All, * Pavel Stehule (pavel.steh...@gmail.com) wrote: Thank you I made a few minor adjustments, but the bigger issue (in my view) is what to do about array_to_json_pretty- seems like we should do the same there, no? Perhaps you could propose a new patch which incorporates my minor changes and also removes array_to_json_pretty and makes array_to_json take an optional argument? I though about it, and I am not sure. If somebody uses arrays as set, then it can be good idea, when it is used as array, then you cannot to throw a nulls from array. I am thinking, so it is not necessary, because we can remove NULLs from array simply via iteration over array (what is impossible for row fields) CREATE OR REPLACE FUNCTION remove_null(anyarray) RETURNS anyarray AS $$ SELECT ARRAY(SELECT a FROM unnest($1) g(a) WHERE a IS NOT NULL) $$ LANGUAGE plpgsql; or this function can be in core for general usage. ignore_nulls in array_to_json_pretty probably is not necessary. On second hand, the cost is zero, and we can have it for API consistency. Regards Pavel Thoughts? Thanks, Stephen
[HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
* Pavel Stehule (pavel.steh...@gmail.com) wrote: ignore_nulls in array_to_json_pretty probably is not necessary. On second hand, the cost is zero, and we can have it for API consistency. I'm willing to be persuaded either way on this, really. I do think it would be nice for both array_to_json and row_to_json to be single functions which take default values, but as for if array_to_json has a ignore_nulls option, I'm on the fence and would defer to people who use that function regularly (I don't today). Beyond that, I'm pretty happy moving forward with this patch. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
On Thu, Sep 4, 2014 at 11:41 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I am sory too much patches :) Patch looks good to me. Marking Ready for Committer. Thanks Regards Pavel -- Jeevan B Chalke Principal Software Engineer, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Thank you Regards Pavel 2014-09-05 8:29 GMT+02:00 Jeevan Chalke jeevan.cha...@enterprisedb.com: On Thu, Sep 4, 2014 at 11:41 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I am sory too much patches :) Patch looks good to me. Marking Ready for Committer. Thanks Regards Pavel -- Jeevan B Chalke Principal Software Engineer, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
I am sory
too much patches
Regards
Pavel
2014-09-04 7:35 GMT+02:00 Jeevan Chalke jeevan.cha...@enterprisedb.com:
Hi Pavel,
You have attached wrong patch.
Thanks
--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
commit eb67c8d3e5e443d9cad1ef08fe2b4747eac933d9
Author: Pavel Stehule pavel.steh...@gooddata.com
Date: Sat Jun 28 17:40:47 2014 +0200
as function with default parameters
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 722640b..0d915c1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10294,11 +10294,12 @@ table2-mapping
/row
row
entry
- literalrow_to_json(record [, pretty_bool])/literal
+ literalrow_to_json(rowval record [, pretty bool [, ignore_nulls bool] ])/literal
/entry
entry
Returns the row as a JSON object. Line feeds will be added between
- level-1 elements if parameterpretty_bool/parameter is true.
+ level-1 elements if parameterpretty_bool/parameter is true. Ignore
+ NULL when parameterignore_nulls/parameter is true.
/entry
entryliteralrow_to_json(row(1,'foo'))/literal/entry
entryliteral{f1:1,f2:foo}/literal/entry
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1bde175..02cf965 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -867,3 +867,10 @@ RETURNS interval
LANGUAGE INTERNAL
STRICT IMMUTABLE
AS 'make_interval';
+
+CREATE OR REPLACE FUNCTION
+ row_to_json(rowval record, pretty boolean DEFAULT false, ignore_nulls boolean DEFAULT false)
+RETURNS json
+LANGUAGE INTERNAL
+STRICT STABLE
+AS 'row_to_json';
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 494a028..9f445ff 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -79,7 +79,8 @@ static void report_invalid_token(JsonLexContext *lex);
static int report_json_context(JsonLexContext *lex);
static char *extract_mb_char(char *s);
static void composite_to_json(Datum composite, StringInfo result,
- bool use_line_feeds);
+ bool use_line_feeds,
+ bool ignore_nulls);
static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
Datum *vals, bool *nulls, int *valcount,
JsonTypeCategory tcategory, Oid outfuncoid,
@@ -1362,7 +1363,7 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
array_to_json_internal(val, result, false);
break;
case JSONTYPE_COMPOSITE:
- composite_to_json(val, result, false);
+ composite_to_json(val, result, false, false);
break;
case JSONTYPE_BOOL:
outputstr = DatumGetBool(val) ? true : false;
@@ -1591,7 +1592,8 @@ array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds)
* Turn a composite / record into JSON.
*/
static void
-composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
+composite_to_json(Datum composite, StringInfo result, bool use_line_feeds,
+bool ignore_nulls)
{
HeapTupleHeader td;
Oid tupType;
@@ -1630,6 +1632,12 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
if (tupdesc-attrs[i]-attisdropped)
continue;
+ val = heap_getattr(tuple, i + 1, tupdesc, isnull);
+
+ /* Don't serialize NULL field when we don't want it */
+ if (isnull ignore_nulls)
+ continue;
+
if (needsep)
appendStringInfoString(result, sep);
needsep = true;
@@ -1638,8 +1646,6 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
escape_json(result, attname);
appendStringInfoChar(result, ':');
- val = heap_getattr(tuple, i + 1, tupdesc, isnull);
-
if (isnull)
{
tcategory = JSONTYPE_NULL;
@@ -1721,34 +1727,19 @@ array_to_json_pretty(PG_FUNCTION_ARGS)
}
/*
- * SQL function row_to_json(row)
+ * SQL function row_to_json(rowval record, pretty bool, ignore_nulls bool)
*/
extern Datum
row_to_json(PG_FUNCTION_ARGS)
{
Datum array = PG_GETARG_DATUM(0);
- StringInfo result;
-
- result = makeStringInfo();
-
- composite_to_json(array, result, false);
-
- PG_RETURN_TEXT_P(cstring_to_text_with_len(result-data, result-len));
-}
-
-/*
- * SQL function row_to_json(row, prettybool)
- */
-extern Datum
-row_to_json_pretty(PG_FUNCTION_ARGS)
-{
- Datum array = PG_GETARG_DATUM(0);
bool use_line_feeds = PG_GETARG_BOOL(1);
+ bool ignore_nulls = PG_GETARG_BOOL(2);
StringInfo result;
result = makeStringInfo();
- composite_to_json(array, result, use_line_feeds);
+ composite_to_json(array, result, use_line_feeds, ignore_nulls);
PG_RETURN_TEXT_P(cstring_to_text_with_len(result-data, result-len));
}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 5176ed0..5aeadc3 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4203,10 +4203,8 @@ DATA(insert
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Hi Pavel,
Here are few more comments on new implementation.
1.
/*
- * SQL function row_to_json(row)
+ * SQL function row_to_json(row record, pretty bool, ignore_nulls bool)
*/
In above comments, parameter name row should changed to rowval.
2.
-DATA(insert OID = 3155 ( row_to_json PGNSP PGUID 12 1 0 0 0 f f f f
t f s 1 0 114 2249 _null_ _null_ _null_ _null_ row_to_json _null_ _null_
_null_ ));
+DATA(insert OID = 3155 ( row_to_json PGNSP PGUID 12 1 0 0 0 f f f f
t f s 1 0 114 2249 16 16 _null_ _null_ {rowval,pretty,ignore_nulls}
_null_ row_to_json _null_ _null_ _null_ ));
Number of arguments (pronargs) should be 3 now. However, when we create it
again with default values it gets updated. But still here we should not have
inconsistency.
3.
extern Datum row_to_json(PG_FUNCTION_ARGS);
extern Datum row_to_json_pretty(PG_FUNCTION_ARGS);
+extern Datum row_to_json_pretty_choosy(PG_FUNCTION_ARGS);
extern Datum to_json(PG_FUNCTION_ARGS);
With this new implementation, we have NOT added row_to_json_pretty_choosy()
function. So need to remove that added line. Also we have only one function
with default arguments and thus removed row_to_json_pretty() function as
well. Thus need to remove extern for that too.
4.
Can we have couple of test cases with named argument along with skipped
pretty parameter test?
Thanks
--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Hi
2014-09-03 9:27 GMT+02:00 Jeevan Chalke jeevan.cha...@enterprisedb.com:
Hi Pavel,
Here are few more comments on new implementation.
1.
/*
- * SQL function row_to_json(row)
+ * SQL function row_to_json(row record, pretty bool, ignore_nulls bool)
*/
In above comments, parameter name row should changed to rowval.
2.
-DATA(insert OID = 3155 ( row_to_json PGNSP PGUID 12 1 0 0 0 f f f
f t f s 1 0 114 2249 _null_ _null_ _null_ _null_ row_to_json _null_
_null_ _null_ ));
+DATA(insert OID = 3155 ( row_to_json PGNSP PGUID 12 1 0 0 0 f f f
f t f s 1 0 114 2249 16 16 _null_ _null_ {rowval,pretty,ignore_nulls}
_null_ row_to_json _null_ _null_ _null_ ));
Number of arguments (pronargs) should be 3 now. However, when we create it
again with default values it gets updated. But still here we should not
have
inconsistency.
3.
extern Datum row_to_json(PG_FUNCTION_ARGS);
extern Datum row_to_json_pretty(PG_FUNCTION_ARGS);
+extern Datum row_to_json_pretty_choosy(PG_FUNCTION_ARGS);
extern Datum to_json(PG_FUNCTION_ARGS);
With this new implementation, we have NOT added row_to_json_pretty_choosy()
function. So need to remove that added line. Also we have only one function
with default arguments and thus removed row_to_json_pretty() function as
well. Thus need to remove extern for that too.
4.
Can we have couple of test cases with named argument along with skipped
pretty parameter test?
done
Regards
Pavel
Thanks
--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
commit 17a0708a0466cc8ff9e8debd0a7e9062eebe3a61
Author: Pavel Stehule pavel.steh...@gooddata.com
Date: Wed Aug 27 22:47:07 2014 +0200
access to help_variables and usage from psql via psql command
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index db314c3..9bb14e9 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -568,6 +568,15 @@ EOF
/listitem
/varlistentry
+varlistentry
+ termoption--help-variables//term
+ listitem
+ para
+ Show help about applicationpsql/application variables,
+ and exit.
+ /para
+ /listitem
+/varlistentry
/variablelist
/refsect1
@@ -2572,10 +2581,12 @@ testdb=gt; userinput\setenv LESS -imx4F/userinput
varlistentry
-termliteral\?/literal/term
+termliteral\? [ options | variables ]/literal/term
listitem
para
-Shows help information about the backslash commands.
+Shows help information about the backslash commands. This command can have a
+option variables or options to take help for psql configuration variables
+or psql command line options.
/para
/listitem
/varlistentry
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index e16b4d5..987a79f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1503,7 +1503,19 @@ exec_command(const char *cmd,
/* \? -- slash command help */
else if (strcmp(cmd, ?) == 0)
- slashUsage(pset.popt.topt.pager);
+ {
+ char *opt0 = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ if (!opt0)
+ slashUsage(pset.popt.topt.pager);
+ else if (strcmp(opt0, variables) == 0)
+ help_variables(pset.popt.topt.pager);
+ else if (strcmp(opt0, options) == 0)
+ usage(pset.popt.topt.pager);
+ else
+ slashUsage(pset.popt.topt.pager);
+ }
#if 0
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index f8f000f..4f29f2a 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -46,11 +46,12 @@
#define ON(var) (var ? _(on) : _(off))
void
-usage(void)
+usage(unsigned short int pager)
{
const char *env;
const char *user;
char *errstr;
+ FILE *output;
/* Find default user, in case we need it. */
user = getenv(PGUSER);
@@ -64,77 +65,82 @@ usage(void)
}
}
- printf(_(psql is the PostgreSQL interactive terminal.\n\n));
- printf(_(Usage:\n));
- printf(_( psql [OPTION]... [DBNAME [USERNAME]]\n\n));
+ output = PageOutput(59, pager);
+
+ fprintf(output, _(psql is the PostgreSQL interactive terminal.\n\n));
+ fprintf(output, _(Usage:\n));
+ fprintf(output, _( psql [OPTION]... [DBNAME [USERNAME]]\n\n));
- printf(_(General options:\n));
+ fprintf(output, _(General options:\n));
/* Display default database */
env = getenv(PGDATABASE);
if (!env)
env = user;
- printf(_( -c, --command=COMMANDrun only single command (SQL or internal) and exit\n));
- printf(_( -d, --dbname=DBNAME database name to connect to (default: \%s\)\n), env);
- printf(_( -f, --file=FILENAME execute commands from file, then exit\n));
- printf(_( -l, --list list available databases, then exit\n));
- printf(_( -v, --set=, --variable=NAME=VALUE\n
- set psql variable NAME to VALUE\n));
- printf(_( -V, --version
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Hi Pavel, You have attached wrong patch. Thanks -- Jeevan B Chalke Principal Software Engineer, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Hi Pavel, it needs a redesign of original implementation, we should to change API to use default values with named parameters but it doesn't help too much (although it can be readable little bit more) instead row_to_json(x, false, true) be row_ro_json(x, ignore_null := true) it is not too much work, but I need a names for parameters I have tried adding dummy names (a, b, c) in pg_proc entry you have added. But that is not sufficient. We need to have default values provided to these arguments to work row_ro_json(x, ignore_null := true) call. It was not trivial. So I have not put much thought on that. For name, I choose (row, pretty, ignore_nulls) or similar. However it was my thought. If it is too complex of not so useful then we can ignore it. Thanks -- Jeevan B Chalke Principal Software Engineer, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Hello
2014-09-02 13:54 GMT+02:00 Jeevan Chalke jeevan.cha...@enterprisedb.com:
Hi Pavel,
it needs a redesign of original implementation, we should to change API
to use default values with named parameters
but it doesn't help too much (although it can be readable little bit more)
instead row_to_json(x, false, true)
be
row_ro_json(x, ignore_null := true)
it is not too much work, but I need a names for parameters
I have tried adding dummy names (a, b, c) in pg_proc entry you have added.
But that is not sufficient. We need to have default values provided to
these
arguments to work row_ro_json(x, ignore_null := true) call.
It was not trivial. So I have not put much thought on that.
For name, I choose (row, pretty, ignore_nulls) or similar.
However it was my thought.
If it is too complex of not so useful then we can ignore it.
here is patch
Regards
Pavel
Thanks
--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
commit 5e9a1a2401d2abae3c100dbe3b4c8fdde4a73e48
Author: Pavel Stehule pavel.steh...@gooddata.com
Date: Sat Jun 28 17:40:47 2014 +0200
initial
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 722640b..0d915c1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10294,11 +10294,12 @@ table2-mapping
/row
row
entry
- literalrow_to_json(record [, pretty_bool])/literal
+ literalrow_to_json(rowval record [, pretty bool [, ignore_nulls bool] ])/literal
/entry
entry
Returns the row as a JSON object. Line feeds will be added between
- level-1 elements if parameterpretty_bool/parameter is true.
+ level-1 elements if parameterpretty_bool/parameter is true. Ignore
+ NULL when parameterignore_nulls/parameter is true.
/entry
entryliteralrow_to_json(row(1,'foo'))/literal/entry
entryliteral{f1:1,f2:foo}/literal/entry
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1bde175..02cf965 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -867,3 +867,10 @@ RETURNS interval
LANGUAGE INTERNAL
STRICT IMMUTABLE
AS 'make_interval';
+
+CREATE OR REPLACE FUNCTION
+ row_to_json(rowval record, pretty boolean DEFAULT false, ignore_nulls boolean DEFAULT false)
+RETURNS json
+LANGUAGE INTERNAL
+STRICT STABLE
+AS 'row_to_json';
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 494a028..8f5f8a8 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -79,7 +79,8 @@ static void report_invalid_token(JsonLexContext *lex);
static int report_json_context(JsonLexContext *lex);
static char *extract_mb_char(char *s);
static void composite_to_json(Datum composite, StringInfo result,
- bool use_line_feeds);
+ bool use_line_feeds,
+ bool ignore_nulls);
static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
Datum *vals, bool *nulls, int *valcount,
JsonTypeCategory tcategory, Oid outfuncoid,
@@ -1362,7 +1363,7 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
array_to_json_internal(val, result, false);
break;
case JSONTYPE_COMPOSITE:
- composite_to_json(val, result, false);
+ composite_to_json(val, result, false, false);
break;
case JSONTYPE_BOOL:
outputstr = DatumGetBool(val) ? true : false;
@@ -1591,7 +1592,8 @@ array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds)
* Turn a composite / record into JSON.
*/
static void
-composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
+composite_to_json(Datum composite, StringInfo result, bool use_line_feeds,
+bool ignore_nulls)
{
HeapTupleHeader td;
Oid tupType;
@@ -1630,6 +1632,12 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
if (tupdesc-attrs[i]-attisdropped)
continue;
+ val = heap_getattr(tuple, i + 1, tupdesc, isnull);
+
+ /* Don't serialize NULL field when we don't want it */
+ if (isnull ignore_nulls)
+ continue;
+
if (needsep)
appendStringInfoString(result, sep);
needsep = true;
@@ -1638,8 +1646,6 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
escape_json(result, attname);
appendStringInfoChar(result, ':');
- val = heap_getattr(tuple, i + 1, tupdesc, isnull);
-
if (isnull)
{
tcategory = JSONTYPE_NULL;
@@ -1721,34 +1727,19 @@ array_to_json_pretty(PG_FUNCTION_ARGS)
}
/*
- * SQL function row_to_json(row)
+ * SQL function row_to_json(row record, pretty bool, ignore_nulls bool)
*/
extern Datum
row_to_json(PG_FUNCTION_ARGS)
{
Datum array = PG_GETARG_DATUM(0);
- StringInfo result;
-
- result = makeStringInfo();
-
- composite_to_json(array, result, false);
-
- PG_RETURN_TEXT_P(cstring_to_text_with_len(result-data,
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
2014-09-03 7:05 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com: Hello 2014-09-02 13:54 GMT+02:00 Jeevan Chalke jeevan.cha...@enterprisedb.com: Hi Pavel, it needs a redesign of original implementation, we should to change API to use default values with named parameters but it doesn't help too much (although it can be readable little bit more) instead row_to_json(x, false, true) be row_ro_json(x, ignore_null := true) it is not too much work, but I need a names for parameters I have tried adding dummy names (a, b, c) in pg_proc entry you have added. But that is not sufficient. We need to have default values provided to these arguments to work row_ro_json(x, ignore_null := true) call. It was not trivial. So I have not put much thought on that. For name, I choose (row, pretty, ignore_nulls) or similar. I cannot use row because it is keyword - I used rowval Regards Pavel However it was my thought. If it is too complex of not so useful then we can ignore it. here is patch Regards Pavel Thanks -- Jeevan B Chalke Principal Software Engineer, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Hi Pavel, Patch does look good to me. And found no issues as such. However here are my optional suggestions: 1. Frankly, I did not like name of the function row_to_json_pretty_choosy. Something like row_to_json_pretty_ignore_nulls seems better to me. 2. To use ignore nulls feature, I have to always pass pretty flag. Which seems weired. Since we do support named argument, can we avoid that? No idea how much difficult it is. If we have a default arguments to this function then we do not need one and two argument variations for this function as well. And we can use named argument for omitting the required one. Just a thought. Rest looks good to me. Thanks -- Jeevan B Chalke Principal Software Engineer, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
2014-09-01 12:33 GMT+02:00 Jeevan Chalke jeevan.cha...@enterprisedb.com: Hi Pavel, Patch does look good to me. And found no issues as such. However here are my optional suggestions: 1. Frankly, I did not like name of the function row_to_json_pretty_choosy. Something like row_to_json_pretty_ignore_nulls seems better to me. should be - I have no better name 2. To use ignore nulls feature, I have to always pass pretty flag. Which seems weired. Since we do support named argument, can we avoid that? No idea how much difficult it is. If we have a default arguments to this function then we do not need one and two argument variations for this function as well. And we can use named argument for omitting the required one. Just a thought. it needs a redesign of original implementation, we should to change API to use default values with named parameters but it doesn't help too much (although it can be readable little bit more) instead row_to_json(x, false, true) be row_ro_json(x, ignore_null := true) it is not too much work, but I need a names for parameters Regards Pavel Rest looks good to me. Thanks -- Jeevan B Chalke Principal Software Engineer, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Hi Pavel, You have said that XMLFOREST has something which ignores nulls, what's that? Will you please provide an example ? I am NOT sure, but here you are trying to omit entire field from the output when its value is NULL. But that will add an extra efforts at other end which is using output of this. That application need to know all fields and then need to replace NULL values for missing fields. However we have an optional argument for ignoring nulls and thus we are safe. Application will use as per its choice. Well, apart from that, tried reviewing the patch. Patch was applied but make failed with following error. make[3]: Entering directory `/home/jeevan/pg_master/src/backend/catalog' cd ../../../src/include/catalog '/usr/bin/perl' ./duplicate_oids 3255 make[3]: *** [postgres.bki] Error 1 Please run unused_oids script to find unused oid. However, I had a quick look over code changes. At first glance it looks good. But still need to check on SQL level and then code walk-through. Waiting for updated patch. Thanks -- Jeevan B Chalke Principal Software Engineer, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Hello
2014-08-22 12:21 GMT+02:00 Jeevan Chalke jeevan.cha...@enterprisedb.com:
Hi Pavel,
You have said that XMLFOREST has something which ignores nulls, what's
that?
Will you please provide an example ?
I was partially wrong - XMLFOREST ignore null always
postgres=# select xmlforest(10 as a,20 as b,null as c);
xmlforest
a10/ab20/b
(1 row)
so if you would to empty elements, you should to use xmlelement and
xmlconcat
postgres=# select xmlconcat(xmlforest(10 as a,20 as b), xmlelement(name c,
null));
xmlconcat
a10/ab20/bc/
(1 row)
I am NOT sure, but here you are trying to omit entire field from the output
when its value is NULL. But that will add an extra efforts at other end
which is using output of this. That application need to know all fields and
then need to replace NULL values for missing fields. However we have an
optional argument for ignoring nulls and thus we are safe. Application will
use as per its choice.
with my patch, you can do decision - lot of REST services doesn't
distinguishes between empty and missing tag - and some developers prefer
remove empty tags due less size of message.
Well, apart from that, tried reviewing the patch. Patch was applied but
make
failed with following error.
make[3]: Entering directory `/home/jeevan/pg_master/src/backend/catalog'
cd ../../../src/include/catalog '/usr/bin/perl' ./duplicate_oids
3255
make[3]: *** [postgres.bki] Error 1
Please run unused_oids script to find unused oid.
it needs remastering
update in attachemnt
However, I had a quick look over code changes. At first glance it looks
good. But still need to check on SQL level and then code walk-through.
Waiting for updated patch.
thank you for review
Regards
Pavel
Thanks
--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
commit e1dd47ca1f881a2ce41117526a536555c23c2d81
Author: Pavel Stehule pavel.steh...@gooddata.com
Date: Sat Aug 23 07:19:46 2014 +0200
remastering, change used oid in pg_proc
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c715ca2..a27aff4 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10294,11 +10294,12 @@ table2-mapping
/row
row
entry
- literalrow_to_json(record [, pretty_bool])/literal
+ literalrow_to_json(record [, pretty_bool [, ignore_nulls] ])/literal
/entry
entry
Returns the row as a JSON object. Line feeds will be added between
- level-1 elements if parameterpretty_bool/parameter is true.
+ level-1 elements if parameterpretty_bool/parameter is true. Ignore
+ NULL when parameterignore_nulls/parameter is true.
/entry
entryliteralrow_to_json(row(1,'foo'))/literal/entry
entryliteral{f1:1,f2:foo}/literal/entry
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 494a028..f35db04 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -79,7 +79,8 @@ static void report_invalid_token(JsonLexContext *lex);
static int report_json_context(JsonLexContext *lex);
static char *extract_mb_char(char *s);
static void composite_to_json(Datum composite, StringInfo result,
- bool use_line_feeds);
+ bool use_line_feeds,
+ bool ignore_nulls);
static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
Datum *vals, bool *nulls, int *valcount,
JsonTypeCategory tcategory, Oid outfuncoid,
@@ -1362,7 +1363,7 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
array_to_json_internal(val, result, false);
break;
case JSONTYPE_COMPOSITE:
- composite_to_json(val, result, false);
+ composite_to_json(val, result, false, false);
break;
case JSONTYPE_BOOL:
outputstr = DatumGetBool(val) ? true : false;
@@ -1591,7 +1592,8 @@ array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds)
* Turn a composite / record into JSON.
*/
static void
-composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
+composite_to_json(Datum composite, StringInfo result, bool use_line_feeds,
+bool ignore_nulls)
{
HeapTupleHeader td;
Oid tupType;
@@ -1630,6 +1632,12 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
if (tupdesc-attrs[i]-attisdropped)
continue;
+ val = heap_getattr(tuple, i + 1, tupdesc, isnull);
+
+ /* Don't serialize NULL field when we don't want it */
+ if (isnull ignore_nulls)
+ continue;
+
if (needsep)
appendStringInfoString(result, sep);
needsep = true;
@@ -1638,8 +1646,6 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
escape_json(result, attname);
appendStringInfoChar(result, ':');
- val = heap_getattr(tuple, i + 1, tupdesc, isnull);
-
if (isnull)
{
tcategory =
[HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
Hello
I am sending small patch, that allows ignore nulls in row_to_json function.
It allow significant size reduction of generated JSON documents.
Regards
Pavel
2014-05-25 7:53 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com:
Hello
In Czech Postgres mailing list was user request for serialization to json
without null values.
He needs a similar behave like XMLFOREST has - it ignores NULLs
In some situations and conversions, when your table is +/- sparse matrix,
this request is valid
postgres=# select hstore(omega) from omega;
hstore
─
a=10, b=20, c=NULL
a=NULL, b=20, c=30
(2 rows)
Proposed function
postgres=# select hstore(omega, ignore_nulls := true) from omega;
hstore
─
a=10, b=20
b=20, c=30
(2 rows)
What do you thinking about this proposal?
Regards
Pavel
commit c97bb5fd8b6c68a1141430d47964ebc3488f4429
Author: Pavel Stehule pavel.steh...@gooddata.com
Date: Sat Jun 28 17:40:47 2014 +0200
initial
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f475458..d46b90d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10290,11 +10290,12 @@ table2-mapping
/row
row
entry
- literalrow_to_json(record [, pretty_bool])/literal
+ literalrow_to_json(record [, pretty_bool [, ignore_nulls] ])/literal
/entry
entry
Returns the row as a JSON object. Line feeds will be added between
- level-1 elements if parameterpretty_bool/parameter is true.
+ level-1 elements if parameterpretty_bool/parameter is true. Ignore
+ NULL when parameterignore_nulls/parameter is true.
/entry
entryliteralrow_to_json(row(1,'foo'))/literal/entry
entryliteral{f1:1,f2:foo}/literal/entry
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 841dd1a..22e1dd6 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -77,7 +77,8 @@ static void report_invalid_token(JsonLexContext *lex);
static int report_json_context(JsonLexContext *lex);
static char *extract_mb_char(char *s);
static void composite_to_json(Datum composite, StringInfo result,
- bool use_line_feeds);
+ bool use_line_feeds,
+ bool ignore_nulls);
static void array_dim_to_json(StringInfo result, int dim, int ndims, int *dims,
Datum *vals, bool *nulls, int *valcount,
JsonTypeCategory tcategory, Oid outfuncoid,
@@ -1355,7 +1356,7 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
array_to_json_internal(val, result, false);
break;
case JSONTYPE_COMPOSITE:
- composite_to_json(val, result, false);
+ composite_to_json(val, result, false, false);
break;
case JSONTYPE_BOOL:
outputstr = DatumGetBool(val) ? true : false;
@@ -1560,7 +1561,8 @@ array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds)
* Turn a composite / record into JSON.
*/
static void
-composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
+composite_to_json(Datum composite, StringInfo result, bool use_line_feeds,
+bool ignore_nulls)
{
HeapTupleHeader td;
Oid tupType;
@@ -1599,6 +1601,12 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
if (tupdesc-attrs[i]-attisdropped)
continue;
+ val = heap_getattr(tuple, i + 1, tupdesc, isnull);
+
+ /* Don't serialize NULL field when we don't want it */
+ if (isnull ignore_nulls)
+ continue;
+
if (needsep)
appendStringInfoString(result, sep);
needsep = true;
@@ -1607,8 +1615,6 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
escape_json(result, attname);
appendStringInfoChar(result, ':');
- val = heap_getattr(tuple, i + 1, tupdesc, isnull);
-
if (isnull)
{
tcategory = JSONTYPE_NULL;
@@ -1700,7 +1706,7 @@ row_to_json(PG_FUNCTION_ARGS)
result = makeStringInfo();
- composite_to_json(array, result, false);
+ composite_to_json(array, result, false, false);
PG_RETURN_TEXT_P(cstring_to_text_with_len(result-data, result-len));
}
@@ -1717,7 +1723,25 @@ row_to_json_pretty(PG_FUNCTION_ARGS)
result = makeStringInfo();
- composite_to_json(array, result, use_line_feeds);
+ composite_to_json(array, result, use_line_feeds, false);
+
+ PG_RETURN_TEXT_P(cstring_to_text_with_len(result-data, result-len));
+}
+
+/*
+ * SQL function row_to_json(row, prettybool, ignore_nulls)
+ */
+extern Datum
+row_to_json_pretty_choosy(PG_FUNCTION_ARGS)
+{
+ Datum array = PG_GETARG_DATUM(0);
+ bool use_line_feeds = PG_GETARG_BOOL(1);
+ bool ignore_nulls = PG_GETARG_BOOL(2);
+ StringInfo result;
+
+ result = makeStringInfo();
+
+ composite_to_json(array, result, use_line_feeds, ignore_nulls);
PG_RETURN_TEXT_P(cstring_to_text_with_len(result-data, result-len));
}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index
