Castable Domains for different JSON representations - Mailing list pgsql-hackers

From Steve Chavez
Subject Castable Domains for different JSON representations
Date
Msg-id CAGRrpzZKa+Gu91j1SOvN3tM1f-7Gh_w441c5nAX1QqdH3Q31Lg@mail.gmail.com
Whole thread Raw
Responses Re: Castable Domains for different JSON representations
List pgsql-hackers
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

pgsql-hackers by date:

Previous
From: Steve Chavez
Date:
Subject: Re: 'converts internal representation to "..."' comment is confusing
Next
From: Ahmed Ibrahim
Date:
Subject: Inquiry/Help with pg_adviser (problem in index_create function for creating indexes)