Castable Domains for different JSON representations

From: Steve Chavez <steve(at)supabase(dot)io>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Castable Domains for different JSON representations
Date: 2023-06-24 21:35:38
Message-ID: CAGRrpzZKa+Gu91j1SOvN3tM1f-7Gh_w441c5nAX1QqdH3Q31Lg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ahmed Ibrahim 2023-06-24 23:50:51 Inquiry/Help with pg_adviser (problem in index_create function for creating indexes)
Previous Message Steve Chavez 2023-06-24 20:52:11 Re: 'converts internal representation to "..."' comment is confusing