Re: Add pg_basetype() function to obtain a DOMAIN base type

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(at)supabase(dot)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
>