Just for fun I wrote a function that converts row to JSON format.
Source code is here
Example:
dbadmin=> select * from T;
id | flag | date | str
----+------+------------+----------------------------------
1 | t | 1970-01-01 | some string
2 | | 2017-01-01 | Invalid input syntax for integer
3 | f | | Quick reference.
4 | t | 2000-02-28 |
5 | t | 2010-04-01 |
| f | 2012-11-15 | foo bar egg
| | |
(7 rows)
dbadmin=> select jsonify(*) from T;
jsonify
-----------------------------------------------------------------------------------------------
{ "id" : 1, "flag" : true, "date" : "1970-01-01", "str" : "some string"}
{ "id" : 2, "flag" : null, "date" : "2017-01-01", "str" : "Invalid input syntax for integer"}
{ "id" : 3, "flag" : false, "date" : null, "str" : "Quick reference."}
{ "id" : 4, "flag" : true, "date" : "2000-02-28", "str" : ""}
{ "id" : 5, "flag" : true, "date" : "2010-04-01", "str" : null}
{ "id" : null, "flag" : false, "date" : "2012-11-15", "str" : "foo bar egg"}
{ "id" : null, "flag" : null, "date" : null, "str" : null}
(7 rows)
Supported data types:
- Boolean
- All numeric data trypes: INTEGER, FLOAT, NUMERIC
- TIME, DATE, TIMESTAMP
- INTERVALS
- CHAR, VARCHAR, LONG VARCHAR
- JSON row limited to 32Mb length
- So far strings are not escaped (Im working on it)
- No TimeZone support so far (Im working on it)
Feel free to use.
I appreciate for tests and suggestion.