On Mon, Dec 4, 2023 at 5:11 PM John Naylor <[email protected]> wrote:
>
> On Thu, Sep 28, 2023 at 12:22 AM Alexander Korotkov
> <[email protected]> wrote:
> > The one thing triggering my perfectionism is that the patch does two
> > syscache lookups instead of one.
>
> For an admin function used interactively, I'm not sure why that
> matters? Or do you see another use case?
I did a minor refactor based on v1-0001.
I think pg_basetype should stay at "9.26.4. System Catalog Information
Functions".
So I placed it before pg_char_to_encoding.
Now functions listed on "Table 9.73. System Catalog Information
Functions" will look like alphabetical ordering.
I slightly changed the src/include/catalog/pg_proc.dat.
now it looks like very similar to pg_typeof
src6=# \df pg_typeof
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-----------+------------------+---------------------+------
pg_catalog | pg_typeof | regtype | "any" | func
(1 row)
src6=# \df pg_basetype
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-------------+------------------+---------------------+------
pg_catalog | pg_basetype | regtype | "any" | func
(1 row)
v2-0001 is as is in the first email thread, 0002 is my changes based on v2-0001.
From a3a180b7074c9196434381d46c636f417089659f Mon Sep 17 00:00:00 2001
From: steve-chavez <[email protected]>
Date: Sat, 9 Sep 2023 00:58:44 -0300
Subject: [PATCH v2 1/2] Add pg_basetype(regtype)
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, use the already available getBaseType() function,
and expose it as the pg_basetype SQL function.
---
doc/src/sgml/func.sgml | 25 +++++++++++++++++++
src/backend/utils/adt/misc.c | 14 +++++++++++
src/include/catalog/pg_proc.dat | 3 +++
src/test/regress/expected/domain.out | 36 ++++++++++++++++++++++++++++
src/test/regress/sql/domain.sql | 17 +++++++++++++
5 files changed, 95 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0a4f8520..7b14c87c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24698,6 +24698,31 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
</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 a basetype it returns the same type.
+ 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_typeof
+-----------
+ text
+</programlisting>
+ </para></entry>
+ </row>
+
<row>
<entry 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..c0c3c9e9 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -43,6 +43,7 @@
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
+#include "utils/syscache.h"
#include "utils/lsyscache.h"
#include "utils/ruleutils.h"
#include "utils/timestamp.h"
@@ -566,6 +567,19 @@ 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);
+
+ if (!SearchSysCacheExists1(TYPEOID, ObjectIdGetDatum(oid)))
+ PG_RETURN_NULL();
+
+ 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 9052f526..f84f106b 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 => '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/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
From 8d28abd4f69064317b8733c74de3284c3fbc85ad Mon Sep 17 00:00:00 2001
From: pgaddict <[email protected]>
Date: Mon, 1 Jan 2024 13:14:12 +0800
Subject: [PATCH v2 2/2] minor refactor based on v1-0001
doc: place function pg_basetype under
"System Catalog Information Function" section.
Other miscellaneous changes,
make pg_basetype functions looks like similar to pg_typeof.
---
doc/src/sgml/func.sgml | 50 ++++++++++++++++-----------------
src/backend/utils/adt/misc.c | 2 +-
src/include/catalog/pg_proc.dat | 2 +-
3 files changed, 27 insertions(+), 27 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7b14c87c..8f66de3b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24698,31 +24698,6 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
</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 a basetype it returns the same type.
- 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_typeof
------------
- text
-</programlisting>
- </para></entry>
- </row>
-
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -25021,6 +24996,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> ( <type>"any"</type> )
+ <returnvalue>regtype</returnvalue>
+ </para>
+ <para>
+ Returns the OID of the base type of a domain or if the argument is a basetype it returns the same type.
+ 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 c0c3c9e9..692621c2 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -43,9 +43,9 @@
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
-#include "utils/syscache.h"
#include "utils/lsyscache.h"
#include "utils/ruleutils.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f84f106b..db037455 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3879,7 +3879,7 @@
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 => 'oid', prosrc => 'pg_basetype' },
+ 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',
--
2.34.1
