From bd09f024a1bbdd7a7e2ca944595a9d4b6c90fb83 Mon Sep 17 00:00:00 2001
From: Nathan Bossart
Date: Mon, 30 Jun 2025 15:38:54 -0500
Subject: Add new OID alias type regdatabase.

This provides a convenient way to look up a database's OID.  For
example, the query

    SELECT * FROM pg_shdepend
    WHERE dbid = (SELECT oid FROM pg_database
                  WHERE datname = current_database());

can now be simplified to

    SELECT * FROM pg_shdepend
    WHERE dbid = current_database()::regdatabase;

Like the regrole type, regdatabase has cluster-wide scope, so we
disallow regdatabase constants from appearing in stored
expressions.

Bumps catversion.

Author: Ian Lawrence Barwick <barwick@gmail.com>
Reviewed-by: Greg Sabino Mullane <htamfids@gmail.com>
Reviewed-by: Jian He <jian.universality@gmail.com>
Reviewed-by: Fabrízio de Royes Mello <fabriziomello@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/aBpjJhyHpM2LYcG0%40nathan
---
 doc/src/sgml/datatype.sgml      | 15 +++++++++++++--
 doc/src/sgml/func.sgml          | 17 +++++++++++++++++
 doc/src/sgml/ref/pgupgrade.sgml |  3 ++-
 3 files changed, 32 insertions(+), 3 deletions(-)

(limited to 'doc/src')

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 09309ba0390..49a7c180a80 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4737,6 +4737,10 @@ INSERT INTO mytable VALUES(-1);  -- fails
     <primary>regconfig</primary>
    </indexterm>
 
+   <indexterm zone="datatype-oid">
+    <primary>regdatabase</primary>
+   </indexterm>
+
    <indexterm zone="datatype-oid">
     <primary>regdictionary</primary>
    </indexterm>
@@ -4878,6 +4882,13 @@ SELECT * FROM pg_attribute
         <entry><literal>english</literal></entry>
        </row>
 
+       <row>
+        <entry><type>regdatabase</type></entry>
+        <entry><structname>pg_database</structname></entry>
+        <entry>database name</entry>
+        <entry><literal>template1</literal></entry>
+       </row>
+
        <row>
         <entry><type>regdictionary</type></entry>
         <entry><structname>pg_ts_dict</structname></entry>
@@ -5049,8 +5060,8 @@ WHERE ...
     be dropped without first removing the default expression.  The
     alternative of <literal>nextval('my_seq'::text)</literal> does not
     create a dependency.
-    (<type>regrole</type> is an exception to this property. Constants of this
-    type are not allowed in stored expressions.)
+    (<type>regdatabase</type> and <type>regrole</type> are exceptions to this
+    property.  Constants of these types are not allowed in stored expressions.)
    </para>
 
    <para>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 298791858be..126b8cfbad8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26750,6 +26750,23 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>to_regdatabase</primary>
+        </indexterm>
+        <function>to_regdatabase</function> ( <type>text</type> )
+        <returnvalue>regdatabase</returnvalue>
+       </para>
+       <para>
+        Translates a textual database name to its OID.  A similar result is
+        obtained by casting the string to type <type>regdatabase</type> (see
+        <xref linkend="datatype-oid"/>); however, this function will return
+        <literal>NULL</literal> rather than throwing an error if the name is
+        not found.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
index aeeed297437..5ddf3a8ae92 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -1110,7 +1110,8 @@ psql --username=postgres --file=script.sql postgres
     <member><type>regproc</type></member>
     <member><type>regprocedure</type></member>
    </simplelist>
-   (<type>regclass</type>, <type>regrole</type>, and <type>regtype</type> can be upgraded.)
+   (<type>regclass</type>, <type>regdatabase</type>, <type>regrole</type>, and
+   <type>regtype</type> can be upgraded.)
   </para>
 
   <para>
-- 
cgit v1.2.3