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 <st...@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 >
