JSON in 9.2 - Could we have just one to_json() function instead of two separate versions ? - Mailing list pgsql-hackers

Hi hackers

After playing around with array_to_json() and row_to_json() functions a
bit it I have a question - why do we even have 2 variants *_to_json()

Collapsing array_to_json() and row_to_json() into just to_json()
----------------------------------------------------------------

As the functionality is not yet release maybe we could still rethink the
interface and have just one which can deal with all types :

to_json(any) returns json 

the current two versions have to call the "any" variant internally
anyhow, to convert individual field values, so why not just expose the
full functionality as a single to_json() function 

You almost can get the "any" functionality now by wrapping the type in
an array and afterwards strip outermost [] from the result.

Is there any good reason why not expose any_to_json() it directly ? 


CREATE OR REPLACE FUNCTION to_json(obj anyelement) 
RETURNS json AS $$
BEGIN
RETURN substring(array_to_json(array[obj])::text FROM '^\[(.*)\]$');
END;
$$ LANGUAGE plpgsql;

hannu=# select to_json('1'::text) ;to_json 
---------"1"
(1 row)

hannu=# select to_json(test) from test limit 2;                             to_json                              
-------------------------------------------------------------------{"id":9,"data":"testdata","tstamp":"2012-05-01
09:44:50.175189"}{"id":10,"data":"testdata","tstamp":"2012-05-0109:45:50.260276"}
 
(2 rows)


Maybe we can remove the *_to_json(functions completely :)
---------------------------------------------------------

As a separate note, could we go even further and fold all this
functionality into an universal cast, so that attaching ::json to any
object will automagically work ?


Removing current limitation of PL/pgSQL 
----------------------------------------

The above plpgsql definition of to_json() does not currently work with
anonymous records defined inline or arrays of such records, but only
because Pl/PgSQL functions currently don't accept these types

hannu=# select to_json(s) from (select 1::int as i, 't'::text as t union
select 2,'x')s;
ERROR:  PL/pgSQL functions cannot accept type record
CONTEXT:  compilation of PL/pgSQL function "to_json" near line 1


I think PL/pgSQL could now start accepting such records as the wrinkles
which made it hard to (recursively) get the needed info for anonymous
records were ironed out when developing the *_to_json() functions so all
of it just works. 

IIRC some of this needed improving data available in core, and was not
just extra surgery done directly inside the *_to_json() functions.


hannu=# select row_to_json(s) from (select 1::int as i, 't'::text as t
union select 2,'x')s;  row_to_json   
-----------------{"i":1,"t":"t"}{"i":2,"t":"x"}
(2 rows)

and even

hannu=# select row_to_json(s) from (select 1::int as i, (select z
from(select 2::int as j, 'x'::text as x)z) as t union select 2,null)s;        row_to_json         
-----------------------------{"i":1,"t":{"j":2,"x":"x"}}{"i":2,"t":null}
(2 rows)

-- 
-------
Hannu Krosing
PostgreSQL Unlimited Scalability and Performance Consultant
2ndQuadrant Nordic
PG Admin Book: http://www.2ndQuadrant.com/books/