On Thu, Mar 21, 2024 at 10:34 AM jian he <[email protected]> wrote:
>
> On Mon, Mar 18, 2024 at 11:43 PM Tom Lane <[email protected]> wrote:
> >
> > Alexander Korotkov <[email protected]> writes:
> > > On Mon, Mar 18, 2024 at 2:01 AM jian he <[email protected]>
> > > wrote:
> > >> `
> > >> Datum
> > >> pg_basetype(PG_FUNCTION_ARGS)
> > >> {
> > >> Oid oid;
> > >>
> > >> oid = get_fn_expr_argtype(fcinfo->flinfo, 0);
> > >> PG_RETURN_OID(getBaseType(oid));
> > >> }
> > >> `
> >
> > > Looks good to me. But should it be named pg_basetypeof()?
> >
> > I still don't like this approach. It forces the function to be
> > used in a particular way that's highly redundant with pg_typeof.
> > I think we'd be better off with
> >
> > pg_basetype(PG_FUNCTION_ARGS)
> > {
> > Oid typid = PG_GETARG_OID(0);
> >
> > PG_RETURN_OID(getBaseType(typid));
> > }
> >
> > The use-case that the other definition handles would be implemented
> > like
> >
> > pg_basetype(pg_typeof(expression))
> >
>
> trying to do it this way.
> not sure the following error message is expected.
>
> SELECT pg_basetype(-1);
> ERROR: cache lookup failed for type 4294967295
I think the error message should be fine.
even though
`select '-1'::oid::regtype;` return 4294967295.
I noticed psql \dD didn't return the basetype of a domain.
one of the usage of this feature would be in psql \dD.
now we can:
\dD mytext_child_2
List of domains
Schema | Name | Type | Basetype | Collation |
Nullable | Default | Check
--------+----------------+----------------+----------+-----------+----------+---------+-------
public | mytext_child_2 | mytext_child_1 | text | |
| |
(1 row)
From 5e60c542c52059cdcdb8a7a2b1cec561f43f7a66 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 28 Mar 2024 10:45:15 +0800
Subject: [PATCH v6 2/2] make psql \dD displays the domain's basetype.
previously psql \dD only shows the type that the domain is based on.
now add a column to display the primitive basetye (that's not a domain)
of a domain.
---
src/bin/psql/describe.c | 2 ++
1 file changed, 2 insertions(+)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 6433497b..34fcaef8 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4444,6 +4444,7 @@ listDomains(const char *pattern, bool verbose, bool showSystem)
"SELECT n.nspname as \"%s\",\n"
" t.typname as \"%s\",\n"
" pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
+ " pg_catalog.pg_basetype(t.typbasetype) as \"%s\",\n"
" (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n"
" WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation) as \"%s\",\n"
" CASE WHEN t.typnotnull THEN 'not null' END as \"%s\",\n"
@@ -4454,6 +4455,7 @@ listDomains(const char *pattern, bool verbose, bool showSystem)
gettext_noop("Schema"),
gettext_noop("Name"),
gettext_noop("Type"),
+ gettext_noop("Basetype"),
gettext_noop("Collation"),
gettext_noop("Nullable"),
gettext_noop("Default"),
--
2.34.1
From aad43e327e4f3b3cbc5bd5d4d1944e70446dc865 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 21 Mar 2024 10:23:04 +0800
Subject: [PATCH v6 1/2] Add pg_basetype(regtype) function to return the
basetype of a domain
Currently obtaining the base type of a domain involves a complex SQL query,
this specially in the case of recursive domain types.
To solve this, use the already available getBaseType() function,
and expose it as the pg_basetype SQL function.
if the argument is not a doamin type, return the type of the argument as is.
if the argument is a type of doamin, pg_basetype will recurse the domain dependencies chain
and return the real inner base type of the domain.
discussion: https://postgr.es/m/CAGRrpzZSX8j=MQcbCSEisFA=ic=k3bknvfnfjav1divjxfh...@mail.gmail.com
---
doc/src/sgml/func.sgml | 26 +++++++++++++++++++++++++
src/backend/utils/adt/misc.c | 10 ++++++++++
src/include/catalog/pg_proc.dat | 3 +++
src/test/regress/expected/domain.out | 29 ++++++++++++++++++++++++++++
src/test/regress/sql/domain.sql | 14 ++++++++++++++
5 files changed, 82 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 93b0bc2b..e9db88f5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25129,6 +25129,32 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_basetype</primary>
+ </indexterm>
+ <function>pg_basetype</function> ( <type>regtype</type> )
+ <returnvalue>regtype</returnvalue>
+ </para>
+ <para>
+ Returns the OID of the base type of a domain identified by its type OID.
+ If the argument is not the OID of a domain type, return the argument as is.
+ If there's a chain of domain dependencies, it will recurse until finding the base type.
+ </para>
+ <para>
+ For example:
+<programlisting>
+CREATE DOMAIN mytext as text;
+
+SELECT pg_basetype('mytext'::regtype);
+ pg_basetype
+-----------
+ text
+</programlisting>
+ </para></entry>
+ </row>
+
<row>
<entry id="pg-char-to-encoding" role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index d4a92d0b..87db4ba6 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -565,6 +565,16 @@ pg_typeof(PG_FUNCTION_ARGS)
PG_RETURN_OID(get_fn_expr_argtype(fcinfo->flinfo, 0));
}
+/*
+ * Return the base type of the argument.
+ * iff the argument is not a type of domain, Return the type of the argument as is.
+ */
+Datum
+pg_basetype(PG_FUNCTION_ARGS)
+{
+ Oid typid = PG_GETARG_OID(0);
+ PG_RETURN_OID(getBaseType(typid));
+}
/*
* Implementation of the COLLATE FOR expression; returns the collation
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 07023ee6..5a7db721 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3889,6 +3889,9 @@
{ oid => '1619', descr => 'type of the argument',
proname => 'pg_typeof', proisstrict => 'f', provolatile => 's',
prorettype => 'regtype', proargtypes => 'any', prosrc => 'pg_typeof' },
+{ oid => '6312', descr => 'get the base type of the oid of domain type',
+ proname => 'pg_basetype', proisstrict => 'f', provolatile => 's',
+ prorettype => 'regtype', proargtypes => 'regtype', prosrc => 'pg_basetype' },
{ oid => '3162',
descr => 'collation of the argument; implementation of the COLLATION FOR expression',
proname => 'pg_collation_for', proisstrict => 'f', provolatile => 's',
diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index dc58793e..f79d31e4 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -1292,3 +1292,32 @@ SELECT * FROM information_schema.check_constraints
regression | public | pos_int_not_null | VALUE IS NOT NULL
(4 rows)
+--
+-- Get the base type of a domain
+--
+create domain mytext as text;
+create domain mytext_child_1 as mytext;
+create domain mytext_child_2 as mytext_child_1;
+select pg_basetype(pg_typeof('mytext'::mytext));
+ pg_basetype
+-------------
+ text
+(1 row)
+
+-- gets base types recursively
+select pg_basetype(pg_typeof('mytext_child_1'::mytext_child_1));
+ pg_basetype
+-------------
+ text
+(1 row)
+
+select pg_basetype(pg_typeof('mytext_child_2'::mytext_child_2));
+ pg_basetype
+-------------
+ text
+(1 row)
+
+drop domain mytext cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to type mytext_child_1
+drop cascades to type mytext_child_2
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index ae1b7fbf..0f5ba98a 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -862,3 +862,17 @@ SELECT * FROM information_schema.check_constraints
FROM information_schema.domain_constraints
WHERE domain_name IN ('con', 'dom', 'pos_int', 'things'))
ORDER BY constraint_name;
+
+--
+-- Get the base type of a domain
+--
+create domain mytext as text;
+create domain mytext_child_1 as mytext;
+create domain mytext_child_2 as mytext_child_1;
+
+select pg_basetype(pg_typeof('mytext'::mytext));
+-- gets base types recursively
+select pg_basetype(pg_typeof('mytext_child_1'::mytext_child_1));
+select pg_basetype(pg_typeof('mytext_child_2'::mytext_child_2));
+
+drop domain mytext cascade;
\ No newline at end of file
base-commit: 7188a7806d208430aa5c717a1aefdf4980ed3d4d
--
2.34.1
