Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-03-30 Thread Tom Lane 
I wrote:
> A fairly common solution to that is to return NULL for bad input,
> but in this case we could just have it return the OID unchanged.

After sleeping on it, I concluded that was a bad idea and we'd
be best off returning NULL for invalid type OIDs.  So this is
just about back to Steve's original proposal, except for being
a bit more bulletproof against races with DROP TYPE.
Pushed that way.

regards, tom lane

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-03-28 Thread Tom Lane 
jian he  writes:
> 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

Yeah, that's not really OK.  You could say it's fine for bogus input,
but we've found over the years that it's better for catalog inspection
functions like this to be forgiving of bad input.  Otherwise,
your query can blow up in unexpected ways due to race conditions
(ie somebody just dropped the type you are interested in).

A fairly common solution to that is to return NULL for bad input,
but in this case we could just have it return the OID unchanged.

Either way though, we can't use getBaseType as-is.  We could imagine
extending that function to support a "noerror"-like flag, but I
believe it's already a hot-spot and I'd rather not complicate it
further.  So what I suggest doing is just duplicating the code;
there's not very much of it.

I did a little polishing of the docs and test cases too, ending
with the v7 attached.  I think this is about ready to go unless
there are objections to the definition.

Not sure what I think about your 0002 proposal to extend \dD
with this.  Aside from the server-version-compatibility problem,
I think it's about 90% redundant because \dD already shows
the immediate base type.  The new column would only be
different in the case of nested domains, which I think are
not common.  \dD's output is already pretty wide, so on the
whole I'm inclined to leave it alone.

regards, tom lane

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 93b0bc2bc6..b3687b3645 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25129,6 +25129,29 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);

   
 
+  
+   
+
+ pg_basetype
+
+pg_basetype ( regtype )
+regtype
+   
+   
+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,
+returns the argument as-is.  If there's a chain of domain
+dependencies, it will recurse until finding the base type.
+   
+   
+Assuming CREATE DOMAIN mytext AS text:
+   
+   
+pg_basetype('mytext'::regtype)
+text
+   
+  
+
   

 
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index d4a92d0b3f..d2b4ba8a72 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -44,6 +44,7 @@
 #include "utils/fmgroids.h"
 #include "utils/lsyscache.h"
 #include "utils/ruleutils.h"
+#include "utils/syscache.h"
 #include "utils/timestamp.h"
 
 
@@ -566,6 +567,48 @@ pg_typeof(PG_FUNCTION_ARGS)
 }
 
 
+/*
+ * Return the base type of the argument.
+ *		If the given type is a domain, return its base type;
+ *		otherwise return the type's own OID.
+ *
+ * This is a SQL-callable version of getBaseType().  Unlike that function,
+ * we don't want to fail for a bogus type OID; this is helpful to keep race
+ * conditions from turning into query failures when scanning the catalogs.
+ * Hence we need our own implementation.
+ */
+Datum
+pg_basetype(PG_FUNCTION_ARGS)
+{
+	Oid			typid = PG_GETARG_OID(0);
+
+	/*
+	 * 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))
+			break;/* return the bogus OID as-is */
+		typTup = (Form_pg_type) GETSTRUCT(tup);
+		if (typTup->typtype != TYPTYPE_DOMAIN)
+		{
+			/* Not a domain, so done */
+			ReleaseSysCache(tup);
+			break;
+		}
+
+		typid = typTup->typbasetype;
+		ReleaseSysCache(tup);
+	}
+
+	PG_RETURN_OID(typid);
+}
+
+
 /*
  * Implementation of the COLLATE FOR expression; returns the collation
  * of the argument.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 07023ee61d..134e3b22fd 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 => '8312', descr => 'base type of a domain type',
+  proname => 'pg_basetype', 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 dc58793e3f..71d9f1952c 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -1292,3 +1292,28 @@ SELECT * FROM information_schema.check_constraints
  regression |

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-03-27 Thread Tom Lane 
jian he  writes:
> I noticed psql \dD didn't return the basetype of a domain.
> one of the usage of this feature would be in psql \dD.

Your 0002 will cause \dD to fail entirely against an older server.
I'm not necessarily against adding this info, but you can't just
ignore the expectations for psql \d commands:

 * Support for the various \d ("describe") commands.  Note that the current
 * expectation is that all functions in this file will succeed when working
 * with servers of versions 9.2 and up.  It's okay to omit irrelevant
 * information for an old server, but not to fail outright.  (But failing
 * against a pre-9.2 server is allowed.)

regards, tom lane

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-03-27 Thread jian he 
On Thu, Mar 21, 2024 at 10:34 AM jian he  wrote:
>
> On Mon, Mar 18, 2024 at 11:43 PM Tom Lane  wrote:
> >
> > Alexander Korotkov  writes:
> > > On Mon, Mar 18, 2024 at 2:01 AM jian he  
> > > 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 
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
@@ -,6 +,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 
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);

   
 
+  
+   
+
+ pg_basetype
+
+pg_basetype ( regtype )
+regtype
+   
+   
+   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.
+   
+   
+For

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-03-20 Thread jian he 
On Mon, Mar 18, 2024 at 11:43 PM Tom Lane  wrote:
>
> Alexander Korotkov  writes:
> > On Mon, Mar 18, 2024 at 2:01 AM jian he  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
From 31791e17893187d2c3c5d3644fac02ddcd4f7de6 Mon Sep 17 00:00:00 2001
From: jian he 
Date: Thu, 21 Mar 2024 10:23:04 +0800
Subject: [PATCH v5 1/1] 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=K3bknVfnFjAv1diVJxFHJvg@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 030ea8af..983460fb 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24886,6 +24886,32 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);

   
 
+  
+   
+
+ pg_basetype
+
+pg_basetype ( regtype )
+regtype
+   
+   
+   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.
+   
+   
+For example:
+
+CREATE DOMAIN mytext as text;
+
+SELECT pg_basetype('mytext'::regtype);
+ pg_basetype
+---
+ text
+
+   
+  
+
   

 
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 042f66f7..5ce8ef46 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 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

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-03-18 Thread Tom Lane 
Alexander Korotkov  writes:
> On Mon, Mar 18, 2024 at 2:01 AM jian he  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))

but there are other use-cases.  For example, if you want to know
the base types of the columns of a table, you could do something
like

select attname, pg_basetype(atttypid) from pg_attribute
  where attrelid = 'foo'::regclass order by attnum;

but that functionality is simply not available with the other
definition.

Perhaps there's an argument for providing both things, but that
feels like overkill to me.  I doubt that pg_basetype(pg_typeof())
is going to be so common as to need a shorthand.

regards, tom lane

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-03-18 Thread Alexander Korotkov 
On Mon, Mar 18, 2024 at 2:01 AM jian he  wrote:
>
> 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:
> pg_basetype ( "any" )
> regtype
>
>
>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  linkend="function-pg-typeof">pg_typeof().
>If there's a chain of domain dependencies, it will recurse
> until finding the base type.
>
>
>
> also, I think this way, we only do one syscache lookup.

Looks good to me.  But should it be named pg_basetypeof()?

--
Regards,
Alexander Korotkov

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-03-17 Thread jian he 
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:
pg_basetype ( "any" )
regtype
   
   
   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 pg_typeof().
   If there's a chain of domain dependencies, it will recurse
until finding the base type.
   


also, I think this way, we only do one syscache lookup.
From d50593e7a25f3e5f05139597d7be14f9dbfe48b9 Mon Sep 17 00:00:00 2001
From: jian he 
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);

   
 
+  
+   
+
+ pg_basetype
+
+pg_basetype ( "any" )
+regtype
+   
+   
+   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 pg_typeof().
+   If there's a chain of domain dependencies, it will recurse until finding the base type.
+   
+   
+For example:
+
+CREATE DOMAIN mytext as text;
+
+SELECT pg_basetype('mytext'::mytext);
+ pg_basetype
+---
+ text
+
+   
+  
+
   

 
@@ -25263,7 +25289,7 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
 
   

-
+
  pg_typeof
 
 pg_typeof ( "any" )
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;

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-02-18 Thread jian he 
On Sun, Feb 18, 2024 at 7:29 AM Tomas Vondra
 wrote:
>
>
> Also, now that I looked at the v2 patch again, I see it only really
> tweaked the pg_proc.dat entry, but the code still does PG_GETARG_OID (so
> the "any" bit is not really correct).
>

PG_GETARG_OID part indeed is wrong. so I change to following:

+Datum
+pg_basetype(PG_FUNCTION_ARGS)
+{
+ Oid oid;
+
+ oid =  get_fn_expr_argtype(fcinfo->flinfo, 0);
+ if (!SearchSysCacheExists1(TYPEOID, ObjectIdGetDatum(oid)))
+ PG_RETURN_NULL();
+
+ PG_RETURN_OID(getBaseType(oid));
+}

I still name the function as pg_basetype, feel free to change it.

+  
+   
+
+ pg_basetype
+
+pg_basetype ( "any" )
+regtype
+   
+   
+   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.
+   
compared with pg_typeof's explanation, I feel like pg_basetype's
explanation doesn't seem accurate.
However, I don't know how to rephrase it.
From a06f2de575da6e5fa45919c792f3dab2470f4927 Mon Sep 17 00:00:00 2001
From: jian he 
Date: Mon, 19 Feb 2024 15:01:19 +0800
Subject: [PATCH v3 1/1] Add pg_basetype("any") function

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   | 25 +++
 src/backend/utils/adt/misc.c | 16 +
 src/include/catalog/pg_proc.dat  |  3 +++
 src/test/regress/expected/domain.out | 36 
 src/test/regress/sql/domain.sql  | 16 +
 5 files changed, 96 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index cf3de803..5af0f4c6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24778,6 +24778,31 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);

   
 
+  
+   
+
+ pg_basetype
+
+pg_basetype ( "any" )
+regtype
+   
+   
+   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.
+   
+   
+For example:
+
+CREATE DOMAIN mytext as text;
+
+SELECT pg_basetype('mytext'::mytext);
+ pg_basetype
+---
+ text
+
+   
+  
+
   

 
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 2d7d7806..30f6a2e5 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,21 @@ 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);
+	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 9c120fc2..bbd6b411 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

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-02-17 Thread jian he 
On Sun, Feb 18, 2024 at 2:49 AM Tomas Vondra
 wrote:
>
> An alternative approach would be modifying pg_typeof() to optionally
> determine the base type, depending on a new argument which would default
> to "false" (i.e. the current behavior).
>
> So you'd do
>
>   SELECT pg_typeof(x);
>
> or
>
>   SELECT pg_typeof(x, false);
>
> to get the current behavior, or and
>
>   SELECT pg_typeof(x, true);
>
> to determine the base type.
>
> Perhaps this would be better than adding a new function doing almost the
> same thing as pg_typeof(). But I haven't tried, maybe it doesn't work
> for some reason, or maybe we don't want to do it this way ...
>

pg_typeof is quite hot.
getting the base type of a domain is niche.

changing pg_typeof requires extra effort to make it compatible with
previous behavior.
bundling it together seems not worth it.

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-02-17 Thread Tom Lane 
Tomas Vondra  writes:
> On 2/17/24 20:20, Tom Lane wrote:
>> I don't have an immediate proposal for exactly what to call such a
>> function, but naming it by analogy to pg_typeof would be questionable.

> Are you objecting to the pg_basetypeof() name, or just to it accepting
> "any" argument? I think pg_basetypeof(regtype) would work ...

I'm not sure.  "pg_basetypeof" seems like it invites confusion with
"pg_typeof", but I don't really have a better idea.  Perhaps
"pg_baseofdomain(regtype)"?  I'm not especially thrilled with that,
either.

Also, just to be clear, we intend this to drill down to the bottom
non-domain type, right?  Do we need a second function that goes
down only one level?  I'm inclined to say "no", mainly because
(1) that would complicate the naming situation even more, and
(2) that use-case is pretty easy to handle with a sub-select.

regards, tom lane

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-02-17 Thread Tomas Vondra 



On 2/17/24 20:20, Tom Lane wrote:
> Tomas Vondra  writes:
>> On 2/17/24 01:57, jian he wrote:
>>> On Sat, Feb 17, 2024 at 2:16 AM Tomas Vondra
>>>  wrote:
 1) We already have pg_typeof() function, so maybe we should use a
 similar naming convention pg_basetypeof()?
> 
>>> I am ok with pg_basetypeof.
> 
>> An alternative approach would be modifying pg_typeof() to optionally
>> determine the base type, depending on a new argument which would default
>> to "false" (i.e. the current behavior).
> 
> Forgive me for not having read the thread, but I wonder why we want
> this to duplicate the functionality of pg_typeof() at all.  My first
> reaction to the requirement given in the thread subject is to write
> a function that takes a type OID and returns another type OID
> (or the same OID, if it's not a domain).  If you want to determine
> the base type of some namable object, you could combine the functions
> like "basetypeof(pg_typeof(x))".  But ISTM there are other use cases
> where you'd have a type OID.  Then having to construct an object to
> apply a pg_typeof-like function to would be difficult.
> 

Yeah, I think you're right - the initial message does actually seem to
indicate it needs to pass type "type OID" to the function, not some
arbitrary expression (and then process a type of it). So modeling it per
pg_typeof(any) would not even work.

Also, now that I looked at the v2 patch again, I see it only really
tweaked the pg_proc.dat entry, but the code still does PG_GETARG_OID (so
the "any" bit is not really correct).

> I don't have an immediate proposal for exactly what to call such a
> function, but naming it by analogy to pg_typeof would be questionable.
> 

Are you objecting to the pg_basetypeof() name, or just to it accepting
"any" argument? I think pg_basetypeof(regtype) would work ...


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-02-17 Thread Tom Lane 
Tomas Vondra  writes:
> On 2/17/24 01:57, jian he wrote:
>> On Sat, Feb 17, 2024 at 2:16 AM Tomas Vondra
>>  wrote:
>>> 1) We already have pg_typeof() function, so maybe we should use a
>>> similar naming convention pg_basetypeof()?

>> I am ok with pg_basetypeof.

> An alternative approach would be modifying pg_typeof() to optionally
> determine the base type, depending on a new argument which would default
> to "false" (i.e. the current behavior).

Forgive me for not having read the thread, but I wonder why we want
this to duplicate the functionality of pg_typeof() at all.  My first
reaction to the requirement given in the thread subject is to write
a function that takes a type OID and returns another type OID
(or the same OID, if it's not a domain).  If you want to determine
the base type of some namable object, you could combine the functions
like "basetypeof(pg_typeof(x))".  But ISTM there are other use cases
where you'd have a type OID.  Then having to construct an object to
apply a pg_typeof-like function to would be difficult.

I don't have an immediate proposal for exactly what to call such a
function, but naming it by analogy to pg_typeof would be questionable.

regards, tom lane

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-02-17 Thread Tomas Vondra 



On 2/17/24 01:57, jian he wrote:
> On Sat, Feb 17, 2024 at 2:16 AM Tomas Vondra
>  wrote:
>>
>> Hi,
>>
>> On 1/2/24 01:00, jian he wrote:
>>> On Mon, Dec 4, 2023 at 5:11 PM John Naylor  wrote:

 On Thu, Sep 28, 2023 at 12:22 AM Alexander Korotkov
  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.
>>
>>
>> I think the patch(es) look reasonable, so just a couple minor comments.
>>
>> 1) We already have pg_typeof() function, so maybe we should use a
>> similar naming convention pg_basetypeof()?
>>
> I am ok with pg_basetypeof.

An alternative approach would be modifying pg_typeof() to optionally
determine the base type, depending on a new argument which would default
to "false" (i.e. the current behavior).

So you'd do

  SELECT pg_typeof(x);

or

  SELECT pg_typeof(x, false);

to get the current behavior, or and

  SELECT pg_typeof(x, true);

to determine the base type.


Perhaps this would be better than adding a new function doing almost the
same thing as pg_typeof(). But I haven't tried, maybe it doesn't work
for some reason, or maybe we don't want to do it this way ...


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-02-16 Thread jian he 
On Sat, Feb 17, 2024 at 2:16 AM Tomas Vondra
 wrote:
>
> Hi,
>
> On 1/2/24 01:00, jian he wrote:
> > On Mon, Dec 4, 2023 at 5:11 PM John Naylor  wrote:
> >>
> >> On Thu, Sep 28, 2023 at 12:22 AM Alexander Korotkov
> >>  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.
>
>
> I think the patch(es) look reasonable, so just a couple minor comments.
>
> 1) We already have pg_typeof() function, so maybe we should use a
> similar naming convention pg_basetypeof()?
>
I am ok with pg_basetypeof.

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-02-16 Thread Tomas Vondra 
Hi,

On 1/2/24 01:00, jian he wrote:
> On Mon, Dec 4, 2023 at 5:11 PM John Naylor  wrote:
>>
>> On Thu, Sep 28, 2023 at 12:22 AM Alexander Korotkov
>>  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.


I think the patch(es) look reasonable, so just a couple minor comments.

1) We already have pg_typeof() function, so maybe we should use a
similar naming convention pg_basetypeof()?

2) I was going to suggest using "any" argument, just like pg_typeof, but
I see 0002 patch already does that. Thanks!

3) I think the docs probably need some formatting - wrapping lines (to
make it consistent with the nearby stuff) and similar stuff.


Other than that it looks fine to me. It's a simple patch, so if we can
agree on the naming I'll get it cleaned up and pushed.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Add pg_basetype() function to obtain a DOMAIN base type

2024-01-01 Thread jian he 
On Mon, Dec 4, 2023 at 5:11 PM John Naylor  wrote:
>
> On Thu, Sep 28, 2023 at 12:22 AM Alexander Korotkov
>  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 
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');

   
 
+  
+   
+
+ pg_basetype
+
+pg_basetype ( type oid )
+regtype
+   
+   
+   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.
+   
+   
+For example:
+
+CREATE DOMAIN mytext as text;
+
+SELECT pg_basetype('mytext'::regtype);
+ pg_typeof
+---
+ text
+
+   
+  
+
   

 
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;

Re: Add pg_basetype() function to obtain a DOMAIN base type

2023-12-04 Thread John Naylor 
On Thu, Sep 28, 2023 at 12:22 AM Alexander Korotkov
 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?

Re: Add pg_basetype() function to obtain a DOMAIN base type

2023-11-17 Thread jian he 
On Thu, Sep 28, 2023 at 11:56 AM Alexander Korotkov
 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 
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);

   
 
+  
+   
+
+ pg_basetype
+
+pg_basetype ( type oid )
+regtype
+   
+   
+   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.
+   
+   
+For example:
+
+CREATE DOMAIN mytext as text;
+
+SELECT pg_basetype('mytext'::regtype);
+ pg_basetype
+---
+ text
+
+   
+  
+
   

 
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, _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 */
+

Re: Add pg_basetype() function to obtain a DOMAIN base type

2023-09-27 Thread Alexander Korotkov 
Hi, Steve!

On Tue, Sep 19, 2023 at 8:36 PM Steve Chavez  wrote:
>
> Just to give a data point for the need of this function:
>
> https://dba.stackexchange.com/questions/231879/how-to-get-the-basetype-of-a-domain-in-pg-type
>
> This is also a common use case for services/extensions that require postgres 
> metadata for their correct functioning, like postgREST or pg_graphql.
>
> Here's a query for getting domain base types, taken from the postgREST 
> codebase:
> https://github.com/PostgREST/postgrest/blob/531a183b44b36614224fda432335cdaa356b4a0a/src/PostgREST/SchemaCache.hs#L342-L364
>
> So having `pg_basetype` would be really helpful in those cases.
>
> Looking forward to hearing any feedback. Or if this would be a bad idea.

I think this is a good idea.  It's nice to have a simple (and fast)
built-in function to call instead of investing complex queries over
the system catalog.

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?

--
Regards,
Alexander Korotkov

Re: Add pg_basetype() function to obtain a DOMAIN base type

2023-09-19 Thread Steve Chavez 
Just to give a data point for the need of this function:

https://dba.stackexchange.com/questions/231879/how-to-get-the-basetype-of-a-domain-in-pg-type

This is also a common use case for services/extensions that require
postgres metadata for their correct functioning, like postgREST or
pg_graphql.

Here's a query for getting domain base types, taken from the postgREST
codebase:
https://github.com/PostgREST/postgrest/blob/531a183b44b36614224fda432335cdaa356b4a0a/src/PostgREST/SchemaCache.hs#L342-L364

So having `pg_basetype` would be really helpful in those cases.

Looking forward to hearing any feedback. Or if this would be a bad idea.

Best regards,
Steve Chavez

On Sat, 9 Sept 2023 at 01:17, Steve Chavez  wrote:

> 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
>

Add pg_basetype() function to obtain a DOMAIN base type

2023-09-08 Thread Steve Chavez 
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
From 9be553c2a3896c12d959bc722a808589765f3db0 Mon Sep 17 00:00:00 2001
From: steve-chavez 
Date: Sat, 9 Sep 2023 00:58:44 -0300
Subject: [PATCH] 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 24ad87f910..69393ca557 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24723,6 +24723,31 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33);

   
 
+  
+   
+
+ pg_basetype
+
+pg_basetype ( type oid )
+regtype
+   
+   
+   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.
+   
+   
+For example:
+
+CREATE DOMAIN mytext as text;
+
+SELECT pg_basetype('mytext'::regtype);
+ pg_typeof
+---
+ text
+
+   
+  
+
   

 
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 5d78d6dc06..c0c3c9e98b 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 9805bc6118..f19bf47242 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3874,6 +3874,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 6d94e84414..4f0253cd71 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