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

Hi, Steve!

On Tue, Sep 19, 2023 at 8:36 PM Steve Chavez <steve@supabase.io> wrote:
>
> 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,
likepostgREST 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.

I think this is a good idea.  It's nice to have a simple (and fast)
built-in function to call instead of investing complex queries over
the system catalog.

The one thing triggering my perfectionism is that the patch does two
syscache lookups instead of one.  In order to fit into one syscache
lookup we could add "bool missing_ok" argument to
getBaseTypeAndTypmod().  However, getBaseTypeAndTypmod() is heavily
used in our codebase.  So, changing its signature would be invasive.
Could we invent getBaseTypeAndTypmodExtended() (ideas for a better
name?) that does all the job and supports "bool missing_ok" argument,
and have getBaseTypeAndTypmod() as a wrapper with the same signature?

------
Regards,
Alexander Korotkov