Re: json casts

On 05/27/2014 11:00 PM, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> On 05/27/2014 03:57 PM, Heikki Linnakangas wrote:
>>> On 05/27/2014 10:53 PM, Andrew Dunstan wrote:
>>>> I've been on the receiving end of a couple of mumbles about the fact
>>>> that the JSON rendering code ignores casts of builtin types to JSON.
>>>> This was originally done as an optimization to avoid doing cache lookups
>>>> for casts for things we knew quite well how to turn into JSON values
>>>> (unlike, say, hstore). However, there is at least one concrete case
>>>> where this has some possibly undesirable consequences, namely
>>>> timestamps. Many JSON processors, especially JavaScript/ECMAScript
>>>> processors, require timestamp values to be in ISO 8601 format, with a
>>>> 'T' between the date part and the time part, and thus they barf on the
>>>> output we produce for such values.
>>> I don't understand what ignoring casts of builtin types to JSON means.
>>> Can you give an example?
>> See src/backend/utils/adt/json.c:json_categorize_type() lines 1280-1300.
>> When rendering some value as part of a json string, if a cast exists
>> from the data type to json, then the cast function is used to render the
>> json instead of the type's normal output function, but only if it's not
>> a builtin type.
> How exactly would disabling that code have any effect on timestamp
> rendering? There's no cast to json from timestamps (nor any other
> builtin type, except jsonb).
I think Andrews idea was, that if cast were used, one could fix the above
problem by defining a correct cast.

>
> I'd be inclined to think a more useful answer to this issue would be to
> make json.c special-case timestamps, as it already does for numerics.
>
> regards, tom lane
But I agree that special-casing the code to use the de-facto json standard
of using ISO 8601 date representation is a better solution.

Just make sure you get the TZ part right - this is another place where
PostgreSQL often differs from other systems' understanding of ISO
timestamps.

Cheers

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ