On Thu, Sep 28, 2023 at 11:56 AM Alexander Korotkov
<aekorot...@gmail.com> wrote:
>
> The one thing triggering my perfectionism is that the patch does two
> syscache lookups instead of one. In order to fit into one syscache
> lookup we could add "bool missing_ok" argument to
> getBaseTypeAndTypmod(). However, getBaseTypeAndTypmod() is heavily
> used in our codebase. So, changing its signature would be invasive.
> Could we invent getBaseTypeAndTypmodExtended() (ideas for a better
> name?) that does all the job and supports "bool missing_ok" argument,
> and have getBaseTypeAndTypmod() as a wrapper with the same signature?
>
hi.
attached patch, not 100% confident it's totally correct, but one
syscache lookup.
another function getBaseTypeAndTypmodExtended added.
getBaseTypeAndTypmodExtended function signature:
Oid getBaseTypeAndTypmodExtended(Oid typid, int32 *typmod, bool missing_ok).
based on Steve Chavez's patch, minor doc changes.
From 55a64f371e34ae62928fef57928256456ceda5cd Mon Sep 17 00:00:00 2001
From: pgaddict <jian.universal...@gmail.com>
Date: Sat, 18 Nov 2023 00:08:08 +0800
Subject: [PATCH v2 1/1] get the base type of a domain.
Mainly used for get the base type of a domain.
Currently obtaining the base type of a domain involves
a long SQL query, this specially in the case of recursive domain types.
To solve this, add another function: getBaseTypeAndTypmodExtended,
and expose it to pg_basetype SQL function.
function getBaseTypeAndTypmodExtended the same as getBaseTypeAndTypmod,
except that the third arg is missing_ok (bool).
If missing_ok is true, type syscache search can not found, it will return InvalidOid.
If missing_ok is false, type syscache cannot found, it will return error.
---
doc/src/sgml/func.sgml | 25 ++++++++++++++
src/backend/utils/adt/misc.c | 18 ++++++++++
src/backend/utils/cache/lsyscache.c | 49 ++++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 4 +++
src/include/utils/lsyscache.h | 2 ++
src/test/regress/expected/domain.out | 36 ++++++++++++++++++++
src/test/regress/sql/domain.sql | 17 ++++++++++
7 files changed, 151 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 93f068ed..ad1c2b26 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24361,6 +24361,31 @@ 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> ( <parameter>type</parameter> <type>oid</type> )
+ <returnvalue>regtype</returnvalue>
+ </para>
+ <para>
+ Returns the OID of the base type of a domain or if the argument is not a domain, returns the argument.
+ 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 5d78d6dc..0bb5453b 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -45,6 +45,7 @@
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/ruleutils.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
@@ -566,6 +567,23 @@ pg_typeof(PG_FUNCTION_ARGS)
PG_RETURN_OID(get_fn_expr_argtype(fcinfo->flinfo, 0));
}
+/*
+ * Return the base type of the argument.
+ */
+Datum
+pg_basetype(PG_FUNCTION_ARGS)
+{
+ Oid oid = PG_GETARG_OID(0);
+ int32 base_typmod = -1;
+ Oid result;
+
+ result = getBaseTypeAndTypmodExtended(oid, &base_typmod, true);
+
+ if (!OidIsValid(result))
+ PG_RETURN_NULL();
+
+ PG_RETURN_OID(result);
+}
/*
* Implementation of the COLLATE FOR expression; returns the collation
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index fc6d267e..ce9abcfa 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -2548,6 +2548,55 @@ getBaseTypeAndTypmod(Oid typid, int32 *typmod)
return typid;
}
+/*
+ * getBaseTypeAndTypmodExtended
+ * If the given type is a domain, return its base type and typmod;
+ * otherwise return the type's own OID, and leave *typmod unchanged.
+ * If missing_ok is false, throw an error if base type not found.
+ * If missing_ok is true, return InvalidOid if base type not found.
+ *
+ * Note that the "applied typmod" should be -1 for every domain level
+ * above the bottommost; therefore, if the passed-in typid is indeed
+ * a domain, *typmod should be -1.
+ */
+Oid
+getBaseTypeAndTypmodExtended(Oid typid, int32 *typmod, bool missing_ok)
+{
+ /*
+ * We loop to find the bottom base type in a stack of domains.
+ */
+ for (;;)
+ {
+ HeapTuple tup;
+ Form_pg_type typTup;
+
+ tup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid));
+ if (HeapTupleIsValid(tup))
+ {
+ typTup = (Form_pg_type) GETSTRUCT(tup);
+ if (typTup->typtype != TYPTYPE_DOMAIN)
+ {
+ /* Not a domain, so done */
+ ReleaseSysCache(tup);
+ break;
+ }
+ Assert(*typmod == -1);
+ typid = typTup->typbasetype;
+ *typmod = typTup->typtypmod;
+
+ ReleaseSysCache(tup);
+ }
+ }
+
+ if(missing_ok && !OidIsValid(typid))
+ return InvalidOid;
+
+ if(!missing_ok && !OidIsValid(typid))
+ elog(ERROR, "cache lookup failed for type %u", typid);
+
+ return typid;
+}
+
/*
* get_typavgwidth
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fb58dee3..725267d9 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3877,6 +3877,10 @@
{ 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',
+ proargnames => '{type}',
+ proname => 'pg_basetype', proisstrict => 'f', provolatile => 's',
+ prorettype => 'regtype', proargtypes => 'oid', 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/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index c22cabdf..c003ca7a 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -184,6 +184,8 @@ extern const struct SubscriptRoutines *getSubscriptingRoutines(Oid typid,
Oid *typelemp);
extern Oid getBaseType(Oid typid);
extern Oid getBaseTypeAndTypmod(Oid typid, int32 *typmod);
+extern Oid getBaseTypeAndTypmodExtended(Oid typid, int32 *typmod,
+ bool missing_ok);
extern int32 get_typavgwidth(Oid typid, int32 typmod);
extern int32 get_attavgwidth(Oid relid, AttrNumber attnum);
extern bool get_attstatsslot(AttStatsSlot *sslot, HeapTuple statstuple,
diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index 6d94e844..4f0253cd 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'::regtype);
+ pg_basetype
+-------------
+ text
+(1 row)
+
+-- gets base types recursively
+select pg_basetype('mytext_child_1'::regtype);
+ pg_basetype
+-------------
+ text
+(1 row)
+
+select pg_basetype('mytext_child_2'::regtype);
+ pg_basetype
+-------------
+ text
+(1 row)
+
+-- if already a base type, get the same
+select pg_basetype('text'::regtype);
+ 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..3c824da0 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -809,3 +809,20 @@ 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'::regtype);
+-- gets base types recursively
+select pg_basetype('mytext_child_1'::regtype);
+select pg_basetype('mytext_child_2'::regtype);
+-- if already a base type, get the same
+select pg_basetype('text'::regtype);
+
+drop domain mytext cascade;
--
2.34.1
