Castable Domains for different JSON representations
Hello hackers,
Currently domain casts are ignored. Yet this would be very useful for
representing data in different formats such as json.
Let's take a tsrange as an example. Its json output by default:
select to_json('(2022-12-31 11:00, 2023-01-01 06:00)'::tsrange);
to_json
-----------------------------------------------------
"(\"2022-12-31 11:00:00\",\"2023-01-01 06:00:00\")"
We can refine its representation in a custom way as:
-- using a custom type for this example
create type mytsrange as range (subtype = timestamp, subtype_diff =
tsrange_subdiff);
create or replace function mytsrange_to_json(mytsrange) 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 (mytsrange as json) with function mytsrange_to_json(mytsrange)
as assignment;
-- now we get the custom representation
select to_json('(2022-12-31 11:00, 2023-01-01 06:00)'::mytsrange);
to_json
--------------------------------------------------------------------------------------------------------------
{"lower" : "2022-12-31T11:00:00", "upper" : "2023-01-01T06:00:00",
"lower_inc" : false, "upper_inc" : false}
(1 row)
Although this works for this example, using a custom type requires
knowledge of the `tsrange` internals. It would be much simpler to do:
create domain mytsrange as range;
But casts on domains are currently ignored:
create cast (mytsrange as json) with function mytsrange_to_json(mytsrange)
as assignment;
WARNING: cast will be ignored because the source data type is a domain
CREATE CAST
Checking the code seems supporting this is a TODO? Or are there any other
concerns of why this shouldn't be done?
I would like to work on this if there is an agreement.
Best regards,
Steve
Steve Chavez <steve@supabase.io> writes:
Currently domain casts are ignored. Yet this would be very useful for
representing data in different formats such as json.
Hm. Usually what people ask for in this space is custom casts
*to* a domain type, which is problematic because it's not clear
how that should interact with the default behavior of promotion
to a domain (namely, applying any relevant domain constraints).
I'd also be suspicious of allowing custom casts from a domain
to any of its base types, because the assumption that that
direction is a no-op is wired into a lot of places. The
particular example you are proposing doesn't fall into either
of those categories; but I wonder if people would find it weird
if we allowed only other cases.
The bigger picture here, though, is what are you really buying
compared to just invoking the special conversion function explicitly?
If you have to write "sometsrangecolumn::mytsrange::json", that's
not shorter and certainly not clearer than writing a function call.
Admittedly, if the column is declared as mytsrange to begin with,
you can save one step --- but we smash domains to their base types
in enough places that I wonder how often you'd end up needing the
extra explicit cast anyway. And I don't think you'd want to tone
down that behavior, because anytime you use a domain column you
are going to be relying on it very heavily to avoid writing lots
of explicit casts to the base type. So I think this might prove
a lot less natural/transparent to use than you're hoping.
regards, tom lane
The bigger picture here, though, is what are you really buying
compared to just invoking the special conversion function explicitly?
If you have to write "sometsrangecolumn::mytsrange::json", that's
not shorter and certainly not clearer than writing a function call.
The main benefit is to be able to call `json_agg` on tables with these
custom json representations. Then the defined json casts work
transparently when doing:
select json_agg(x) from mytbl x;
json_agg
-------------------------------------------------------------------------------------------------------------------------------
[{"id":1,"val":{"lower" : "2022-12-31T11:00:00", "upper" :
"2023-01-01T06:00:00", "lower_inc" : false, "upper_inc" : false}}]
-- example table
create table mytbl(id int, val mytsrange);
insert into mytbl values (1, '(2022-12-31 11:00, 2023-01-01 06:00)');
This output is directly consumable on web applications and as
you can see the expression is pretty short, with no need to use
the explicit casts as `json_agg` already does them internally.
Best regards,
Steve
Import Notes
Reply to msg id not found: CAGRrpzbBS+kKhWPLa67_oWRkzVFFCJLSB2TGGCykDzRu_x26RA@mail.gmail.com