On 09/29/2012 11:47 AM, Hannu Krosing wrote:
On 09/26/2012 06:46 PM, Tom Lane wrote:
Andrew Dunstan <and...@dunslane.net> writes:
Drawing together various discussions both here and elsewhere (e.g. the
PostgresOpen hallway track) I propose to work on the following:
1. make datum_to_json() honor a type's cast to json if it exists. The
fallback is to use the type's string representation, as now.
2. add a cast hstore -> json (any others needed for core / contrib
types ?)
3. add a to_json(anyelement) function
4. add a new aggregate function json_agg(anyrecord) -> json to simplify
and make more effecient turning a resultset into json.
Comments welcome.
ISTM the notion of to_json(anyelement) was already heavily discussed and
had spec-compliance issues ... in fact, weren't you one of the people
complaining? What exactly does #3 mean that is different from the
previous thread?
Also, on reflection I'm not sure about commandeering cast-to-json for
this --- aren't we really casting to "json member" or something like
that? The distinction between a container and its contents seems
important here. With a container type as source, it might be important
to do something different if we're coercing it to a complete JSON
value versus something that will be just one member. I'm handwaving
here because I don't feel like going back to re-read the RFC, but
it seems like something that should be considered carefully before
we lock down an assumption that there can never be a difference.
regards, tom lane
Reflecting over the dual possible interpretation of what it does mean
to convert between "text" and "json" data types it has dawned to me
that the confusion may come mainly from wanting json to be two things
at once:
1. - a serialisation of of a subset of javascript objects to a string.
2. - a dynamic type represented by the above serialisation.
case 1
------
If we stick with interpretation 1. then json datatype is really no
more than a domain based on "text" type and having a CHECK
is_valid_json() constraint.
For this interpretation it makes complete sense to interpret any text
as already being serialised and no casts (other than casts to a text
type) have place here.
a few datatypes - like hstore - could have their "to_json_text()"
serialiser functions if there is a better serialisation to text than
the types defaul one, but other than that the "serialise to text and
quote if not null, boolean or numeric type" should be needed.
if there is strong aversion to relying on function names for getting
the right serialisation function, we could invent a new "cast-like"
feature for serialising types so we could define a serialiser for
hstore to json using
CREATE SERIALISATION (hstore AS json)
WITH FUNCTION hstore_as_json(hstore);
this probably will not be any safer than just using the name for
lookup directly unless we place some restrictions on who is allowed to
create the serialisation;
case 2
------
My suggestions on using typecasts for convert-to-json were result of
this interpretation of json-as-dynamic-type.
Having thought more of this I now think that we probably should leave
JSON alone and develop an separate dynamic type here.
I have started work on doing this based on ideas from BSON data
format, except using postgreSQL datatypes.
It will still have to solve similar problems we have had here with
JSON, but being both a new type and a binary type there will probably
be no expectation of 1-to-1 conversion from to-text.
Will post here soon for more discussion on what this ned type does and
how it should be used.
I am not opposed to making a new type, but I really don't think that
means we need to do nothing for the existing data type. The suggested
SERIALIZATION mechanism seems to be fairly intrusive and heavy handed,
as opposed to the very lightweight mechanism that is Tom's option 3.
Personally I don't have a strong feeling about a general to_json
function, but it's something other people have asked for. The things I
do care about are the json_agg function (to which nobody has objected)
and finding a mechanism for reasonably converting structured types,
particularly hstore, to json. I still think Tom's suggestion is the best
and simplest way to do that.
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