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>
- <literal>row_to_json(record [, pretty_bool])</literal>
+ <literal>row_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 <parameter>pretty_bool</parameter> is true.
+ level-1 elements if <parameter>pretty_bool</parameter> is true. Ignore
+ NULL when <parameter>ignore_nulls</parameter> is true.
</entry>
<entry><literal>row_to_json(row(1,'foo'))</literal></entry>
<entry><literal>{"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, 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..c0d749a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4203,10 +4203,8 @@ DATA(insert OID = 3153 ( array_to_json PGNSP PGUID 12 1 0 0 0 f f f f t f s
DESCR("map array to json");
DATA(insert OID = 3154 ( array_to_json PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 114 "2277 16" _null_ _null_ _null_ _null_ array_to_json_pretty _null_ _null_ _null_ ));
DESCR("map array to json with optional pretty printing");
-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_ ));
DESCR("map row to json");
-DATA(insert OID = 3156 ( row_to_json PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 114 "2249 16" _null_ _null_ _null_ _null_ row_to_json_pretty _null_ _null_ _null_ ));
-DESCR("map row to json with optional pretty printing");
DATA(insert OID = 3173 ( json_agg_transfn PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2281 "2281 2283" _null_ _null_ _null_ _null_ json_agg_transfn _null_ _null_ _null_ ));
DESCR("json aggregate transition function");
DATA(insert OID = 3174 ( json_agg_finalfn PGNSP PGUID 12 1 0 0 0 f f f f f f i 1 0 114 "2281" _null_ _null_ _null_ _null_ json_agg_finalfn _null_ _null_ _null_ ));
diff --git a/src/include/utils/json.h b/src/include/utils/json.h
index 82cc48b..6e43dec 100644
--- a/src/include/utils/json.h
+++ b/src/include/utils/json.h
@@ -26,6 +26,7 @@ extern Datum array_to_json(PG_FUNCTION_ARGS);
extern Datum array_to_json_pretty(PG_FUNCTION_ARGS);
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);
extern Datum json_agg_transfn(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out
index bb4d9ed..fd40117 100644
--- a/src/test/regress/expected/json.out
+++ b/src/test/regress/expected/json.out
@@ -403,6 +403,28 @@ SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),
{"f1":[5,6,7,8,9,10]}
(1 row)
+WITH x AS (SELECT a,b,c FROM (VALUES(10,20,30),
+ (10,NULL, NULL),
+ (NULL, NULL, NULL)) g(a,b,c))
+ SELECT row_to_json(x, false, false) FROM x;
+ row_to_json
+------------------------------
+ {"a":10,"b":20,"c":30}
+ {"a":10,"b":null,"c":null}
+ {"a":null,"b":null,"c":null}
+(3 rows)
+
+WITH x AS (SELECT a,b,c FROM (VALUES(10,20,30),
+ (10,NULL, NULL),
+ (NULL, NULL, NULL)) g(a,b,c))
+ SELECT row_to_json(x, false, true) FROM x;
+ row_to_json
+------------------------
+ {"a":10,"b":20,"c":30}
+ {"a":10}
+ {}
+(3 rows)
+
-- to_json, timestamps
select to_json(timestamp '2014-05-28 12:22:35.614298');
to_json
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index c980132..48c3e37 100644
--- a/src/test/regress/sql/json.sql
+++ b/src/test/regress/sql/json.sql
@@ -100,6 +100,16 @@ FROM rows q;
SELECT row_to_json(row((select array_agg(x) as d from generate_series(5,10) x)),false);
+WITH x AS (SELECT a,b,c FROM (VALUES(10,20,30),
+ (10,NULL, NULL),
+ (NULL, NULL, NULL)) g(a,b,c))
+ SELECT row_to_json(x, false, false) FROM x;
+
+WITH x AS (SELECT a,b,c FROM (VALUES(10,20,30),
+ (10,NULL, NULL),
+ (NULL, NULL, NULL)) g(a,b,c))
+ SELECT row_to_json(x, false, true) FROM x;
+
-- to_json, timestamps
select to_json(timestamp '2014-05-28 12:22:35.614298');
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
