Обсуждение: 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\")"
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;
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)
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.
> 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}}]
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