Re: to_json(now()) result doesn't have 'T' separator

On Mon, Dec 23, 2013 at 5:37 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
> I tried making some cast functions as follows, but it doesn't seem to work
> properly:
>
> create or replace function iso_timestamp(timestamptz) returns json as $$
> select ('"' ||
> substring(xmlelement(name x, $1)::text from 4 for 32) || '"'
> )::json
> $$ language sql immutable;
>
> create cast (timestamptz as json) with function iso_timestamp (timestamptz)
> as implicit;
>
> create function to_json(timestamptz) returns json as $$
> select $1::json
> $$ language sql immutable;
>
> create table t (id serial primary key, created_at timestamptz default
> now());
> insert into t values (default);
> select row_to_json(t) from t;
>
> row_to_json
> -------------------------------------------------------
> {"id":1,"created_at":"2013-12-23 17:37:08.825935-08"}
>
>

Close as I could get using above is:

test=# select to_json(created_at) from t;
to_json
------------------------------------
"2013-12-23T19:09:33.886092-08:00"

Seems row_to_json bypasses casts whereas to_json does not.
--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com