From: | Steve Chavez <steve(at)supabase(dot)io> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Add pg_basetype() function to obtain a DOMAIN base type |
Date: | 2023-09-09 04:17:02 |
Message-ID: | CAGRrpzZSX8j=MQcbCSEisFA=ic=K3bknVfnFjAv1diVJxFHJvg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
Attachment | Content-Type | Size |
---|---|---|
0001-Add-pg_basetype-regtype.patch | text/x-patch | 5.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Gabriele Bartolini | 2023-09-09 06:37:47 | Re: Possibility to disable `ALTER SYSTEM` |
Previous Message | Amit Kapila | 2023-09-09 03:24:18 | Re: [PoC] pg_upgrade: allow to upgrade publisher node |