MIME-Version: 1.0
References: <CAGRrpzZSX8j=MQcbCSEisFA=ic=K3bknVfnFjAv1diVJxFHJvg@mail.gmail.com>
In-Reply-To: <CAGRrpzZSX8j=MQcbCSEisFA=ic=K3bknVfnFjAv1diVJxFHJvg@mail.gmail.com>
From: Steve Chavez <steve@supabase.io>
Date: Tue, 19 Sep 2023 11:20:24 -0300
Message-ID: <CAGRrpzbr6b7VP=DvGZ2W5t-NyczzBUZfd4qCVm7pVPepJOLaUw@mail.gmail.com>
Subject: Re: Add pg_basetype() function to obtain a DOMAIN base type
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000057e6790605b6f633"
Archived-At: <https://www.postgresql.org/message-id/CAGRrpzbr6b7VP%3DDvGZ2W5t-NyczzBUZfd4qCVm7pVPepJOLaUw%40mail.gmail.com>
Precedence: bulk

--00000000000057e6790605b6f633
Content-Type: text/plain; charset="UTF-8"

Just to give a data point for the need of this function:

https://dba.stackexchange.com/questions/231879/how-to-get-the-basetype-of-a-domain-in-pg-type

This is also a common use case for services/extensions that require
postgres metadata for their correct functioning, like postgREST or
pg_graphql.

Here's a query for getting domain base types, taken from the postgREST
codebase:
https://github.com/PostgREST/postgrest/blob/531a183b44b36614224fda432335cdaa356b4a0a/src/PostgREST/SchemaCache.hs#L342-L364

So having `pg_basetype` would be really helpful in those cases.

Looking forward to hearing any feedback. Or if this would be a bad idea.

Best regards,
Steve Chavez

On Sat, 9 Sept 2023 at 01:17, Steve Chavez <steve@supabase.io> wrote:

> Hello hackers,
>
> Currently obtaining the base type of a domain involves a somewhat long
> recursive query. Consider:
>
> ```
> create domain mytext as text;
> create domain mytext_child_1 as mytext;
> create domain mytext_child_2 as mytext_child_1;
> ```
>
> To get `mytext_child_2` base type we can do:
>
> ```
> WITH RECURSIVE
> recurse AS (
>   SELECT
>     oid,
>     typbasetype,
>     COALESCE(NULLIF(typbasetype, 0), oid) AS base
>   FROM pg_type
>   UNION
>   SELECT
>     t.oid,
>     b.typbasetype,
>     COALESCE(NULLIF(b.typbasetype, 0), b.oid) AS base
>   FROM recurse t
>   JOIN pg_type b ON t.typbasetype = b.oid
> )
> SELECT
>   oid::regtype,
>   base::regtype
> FROM recurse
> WHERE typbasetype = 0 and oid = 'mytext_child_2'::regtype;
>
>       oid       | base
> ----------------+------
>  mytext_child_2 | text
> ```
>
> Core has the `getBaseType` function, which already gets a domain base type
> recursively.
>
> I've attached a patch that exposes a `pg_basetype` SQL function that uses
> `getBaseType`, so the long query above just becomes:
>
> ```
> select pg_basetype('mytext_child_2'::regtype);
>  pg_basetype
> -------------
>  text
> (1 row)
> ```
>
> Tests and docs are added.
>
> Best regards,
> Steve Chavez
>

--00000000000057e6790605b6f633
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Just to give a data point for the need of this functi=
on:</div><div><br></div><div><a href=3D"https://dba.stackexchange.com/quest=
ions/231879/how-to-get-the-basetype-of-a-domain-in-pg-type">https://dba.sta=
ckexchange.com/questions/231879/how-to-get-the-basetype-of-a-domain-in-pg-t=
ype</a><br></div><div><br></div><div>This is also a common use case for ser=
vices/extensions that require postgres metadata for their correct functioni=
ng, like postgREST or pg_graphql.</div><div><br></div><div>Here&#39;s a que=
ry for getting domain base types, taken from the postgREST codebase:</div><=
div><a href=3D"https://github.com/PostgREST/postgrest/blob/531a183b44b36614=
224fda432335cdaa356b4a0a/src/PostgREST/SchemaCache.hs#L342-L364">https://gi=
thub.com/PostgREST/postgrest/blob/531a183b44b36614224fda432335cdaa356b4a0a/=
src/PostgREST/SchemaCache.hs#L342-L364</a></div><div><br></div><div>So havi=
ng `pg_basetype` would be really helpful in those cases.</div><div><br></di=
v><div>Looking forward to hearing any feedback. Or if this would be a bad i=
dea.</div><br><div>Best regards,</div><div>Steve Chavez</div></div><br><div=
 class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, 9 Sept=
 2023 at 01:17, Steve Chavez &lt;<a href=3D"mailto:steve@supabase.io">steve=
@supabase.io</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex"><div dir=3D"ltr">Hello hackers,<div><br></div><div>Currently ob=
taining the base type of a domain involves a somewhat long recursive query.=
 Consider:</div><div><br></div><div>```</div><div>create domain mytext as t=
ext;<br>create domain mytext_child_1 as mytext;<br>create domain mytext_chi=
ld_2 as mytext_child_1;<br></div><div>```</div><div><br></div><div>To get `=
mytext_child_2` base type we can do:</div><div><br></div><div>```</div><div=
>WITH RECURSIVE<br>recurse AS (<br>=C2=A0 SELECT<br>=C2=A0 =C2=A0 oid,<br>=
=C2=A0 =C2=A0 typbasetype,<br>=C2=A0 =C2=A0 COALESCE(NULLIF(typbasetype, 0)=
, oid) AS base<br>=C2=A0 FROM pg_type<br>=C2=A0 UNION<br>=C2=A0 SELECT<br>=
=C2=A0 =C2=A0 t.oid,<br>=C2=A0 =C2=A0 b.typbasetype,<br>=C2=A0 =C2=A0 COALE=
SCE(NULLIF(b.typbasetype, 0), b.oid) AS base<br>=C2=A0 FROM recurse t<br>=
=C2=A0 JOIN pg_type b ON t.typbasetype =3D b.oid<br>)<br>SELECT<br>=C2=A0 o=
id::regtype,<br>=C2=A0 base::regtype<br>FROM recurse<br>WHERE typbasetype =
=3D 0 and oid =3D &#39;mytext_child_2&#39;::regtype;<br><br></div><div>=C2=
=A0 =C2=A0 =C2=A0 oid =C2=A0 =C2=A0 =C2=A0 | base <br>----------------+----=
--<br>=C2=A0mytext_child_2 | text<br></div><div>```</div><div><br></div><di=
v>Core has the `getBaseType` function, which already gets a domain base typ=
e recursively.</div><div><br></div><div>I&#39;ve attached a patch that expo=
ses a `pg_basetype` SQL function that uses `getBaseType`, so the long query=
 above just becomes:</div><div><br></div><div>```</div><div>select pg_baset=
ype(&#39;mytext_child_2&#39;::regtype);<br>=C2=A0pg_basetype <br>----------=
---<br>=C2=A0text<br>(1 row)<br></div><div>```</div><div><br></div><div>Tes=
ts and docs are added.</div><div><br></div><div>Best regards,</div><div>Ste=
ve Chavez</div></div>
</blockquote></div>

--00000000000057e6790605b6f633--