I was wondering how to address the tsrange problem in postgres, as tsrange('2010-01-01 14:30', '2010-01-01 15:30')::json returns annoying escapes: "[\"2010-01-01 14:30:00\",\"2010-01-01 15:30:00\")". Postgres REST nicely suggest to approach the problem through automatic casts:
create or replace function tsrange_to_json(tsrange) returns json as $$
select json_build_object(
'lower', lower($1)
, 'upper', upper($1)
, 'lower_inc', lower_inc($1)
, 'upper_inc', upper_inc($1)
);
$$ language sql;
create cast (tsrange as json) with function tsrange_to_json(tsrange) as assignment;
which works nicely if I cast tsrange::json but keeps the old formatting if I do to_json(tsrange)
select tsrange('2010-01-01 14:30', '2010-01-01 15:30')::json
-- "{"lower" : "2010-01-01T14:30:00", "upper" : "2010-01-01T15:30:00", "lower_inc" : true, "upper_inc" : false}"
select to_json(tsrange('2010-01-01 14:30', '2010-01-01 15:30'))
-- ""[\"2010-01-01 14:30:00\",\"2010-01-01 15:30:00\")""
Why to_json does not use ::json casts?
