Add pg_basetype() function to obtain a DOMAIN base type

Поиск
Список
Период
Сортировка
От Steve Chavez
Тема Add pg_basetype() function to obtain a DOMAIN base type
Дата
Msg-id CAGRrpzZSX8j=MQcbCSEisFA=ic=K3bknVfnFjAv1diVJxFHJvg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Add pg_basetype() function to obtain a DOMAIN base type  (Steve Chavez <steve@supabase.io>)
Список pgsql-hackers
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
Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Amit Kapila
Дата:
Сообщение: Re: [PoC] pg_upgrade: allow to upgrade publisher node
Следующее
От: Gabriele Bartolini
Дата:
Сообщение: Re: Possibility to disable `ALTER SYSTEM`