On Thu, Mar 21, 2024 at 10:34 AM jian he <jian.universal...@gmail.com> wrote: > > On Mon, Mar 18, 2024 at 11:43 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > > > Alexander Korotkov <aekorot...@gmail.com> writes: > > > On Mon, Mar 18, 2024 at 2:01 AM jian he <jian.universal...@gmail.com> > > > 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 <jian.universal...@gmail.com> 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 <jian.universal...@gmail.com> 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