looking at it again.
I found out we can just simply do
`
Datum
pg_basetype(PG_FUNCTION_ARGS)
{
Oid oid;
oid = get_fn_expr_argtype(fcinfo->flinfo, 0);
PG_RETURN_OID(getBaseType(oid));
}
`
if the type is not a domain, work the same as pg_typeof.
if the type is domain, pg_typeof return as is, pg_basetype return the
base type.
so it only diverges when the argument type is a type of domain.
the doc:
<function>pg_basetype</function> ( <type>"any"</type> )
<returnvalue>regtype</returnvalue>
</para>
<para>
Returns the OID of the base type of a domain. If the argument
is not a type of domain,
return the OID of the data type of the argument just like <link
linkend="function-pg-typeof"><function>pg_typeof()</function></link>.
If there's a chain of domain dependencies, it will recurse
until finding the base type.
</para>
also, I think this way, we only do one syscache lookup.
From d50593e7a25f3e5f05139597d7be14f9dbfe48b9 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sun, 17 Mar 2024 10:35:52 +0800
Subject: [PATCH v4 1/1] Add pg_basetype("any") function for querying 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=K3bknVfnFjAv1diVJxFHJvg@mail.gmail.com
---
doc/src/sgml/func.sgml | 28 +++++++++++++++++++++-
src/backend/utils/adt/misc.c | 12 ++++++++++
src/include/catalog/pg_proc.dat | 3 +++
src/test/regress/expected/domain.out | 36 ++++++++++++++++++++++++++++
src/test/regress/sql/domain.sql | 16 +++++++++++++
5 files changed, 94 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 72c5175e..cf440b92 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24777,6 +24777,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>"any"</type> )
+ <returnvalue>regtype</returnvalue>
+ </para>
+ <para>
+ Returns the OID of the base type of a domain. If the argument is not a type of domain,
+ return the OID of the data type of the argument just like <link linkend="function-pg-typeof"><function>pg_typeof()</function></link>.
+ 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'::mytext);
+ pg_basetype
+-----------
+ text
+</programlisting>
+ </para></entry>
+ </row>
+
<row>
<entry id="pg-char-to-encoding" role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -25263,7 +25289,7 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
<row>
<entry role="func_table_entry"><para role="func_signature">
- <indexterm>
+ <indexterm id="function-pg-typeof">
<primary>pg_typeof</primary>
</indexterm>
<function>pg_typeof</function> ( <type>"any"</type> )
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index d4a92d0b..2272d953 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -565,6 +565,18 @@ 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 argument's type as is.
+ */
+Datum
+pg_basetype(PG_FUNCTION_ARGS)
+{
+ Oid oid;
+
+ oid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+ PG_RETURN_OID(getBaseType(oid));
+}
/*
* 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 700f7daf..b9079be2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3877,6 +3877,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 a domain',
+ proname => 'pg_basetype', proisstrict => 'f', provolatile => 's',
+ prorettype => 'regtype', proargtypes => 'any', 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 6d94e844..13bf7877 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -1207,3 +1207,39 @@ create domain testdomain1 as int constraint unsigned check (value > 0);
alter domain testdomain1 rename constraint unsigned to unsigned_foo;
alter domain testdomain1 drop constraint unsigned_foo;
drop domain testdomain1;
+--
+-- 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('mytext'::mytext);
+ pg_basetype
+-------------
+ text
+(1 row)
+
+-- gets base types recursively
+select pg_basetype('mytext_child_1'::mytext_child_1);
+ pg_basetype
+-------------
+ text
+(1 row)
+
+select pg_basetype('mytext_child_2'::mytext_child_2);
+ pg_basetype
+-------------
+ text
+(1 row)
+
+-- if already a base type, get the same
+select pg_basetype('text'::text);
+ 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 745f5d5f..58540519 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -809,3 +809,19 @@ create domain testdomain1 as int constraint unsigned check (value > 0);
alter domain testdomain1 rename constraint unsigned to unsigned_foo;
alter domain testdomain1 drop constraint unsigned_foo;
drop domain testdomain1;
+
+--
+-- 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('mytext'::mytext);
+-- gets base types recursively
+select pg_basetype('mytext_child_1'::mytext_child_1);
+select pg_basetype('mytext_child_2'::mytext_child_2);
+-- if already a base type, get the same
+select pg_basetype('text'::text);
+
+drop domain mytext cascade;
\ No newline at end of file
base-commit: b7831865159d5fb6f0d263e6023f0986589fe254
--
2.34.1
