Hello
We already have function pg_get_userbyid(oid) with lookup in pg_authid catalog.
My collegue ask me can we add similar function pg_get_databasebyid(oid) with
lookup in pg_databases.
It is simple function to get a database name by oid and fallback to 'unknown
(OID=n)' if missing.
The proposed patch is attached. Currently I missed the tests - I doubt which
file in src/test/regress/sql/ is the most suitable. pg_get_userbyid is called
from privileges.sql only.
regards, Sergei
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c878a0ba4d..5ed5b3ac39 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18333,6 +18333,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
<primary>pg_get_userbyid</primary>
</indexterm>
+ <indexterm>
+ <primary>pg_get_databasebyid</primary>
+ </indexterm>
+
<indexterm>
<primary>pg_get_viewdef</primary>
</indexterm>
@@ -18513,6 +18517,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
<entry><type>name</type></entry>
<entry>get role name with given OID</entry>
</row>
+ <row>
+ <entry><literal><function>pg_get_databasebyid(<parameter>db_oid</parameter>)</function></literal></entry>
+ <entry><type>name</type></entry>
+ <entry>get database name with given OID</entry>
+ </row>
<row>
<entry><literal><function>pg_get_viewdef(<parameter>view_name</parameter>)</function></literal></entry>
<entry><type>text</type></entry>
@@ -18703,8 +18712,9 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
</para>
<para>
- <function>pg_get_userbyid</function> extracts a role's name given
- its OID.
+ <function>pg_get_userbyid</function> and
+ <function>pg_get_databasebyid</function> extracts respectively a
+ role's and database's name given its OID.
</para>
<para>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 3e64390d81..214a081555 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -31,6 +31,7 @@
#include "catalog/pg_authid.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_constraint.h"
+#include "catalog/pg_database.h"
#include "catalog/pg_depend.h"
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
@@ -2474,6 +2475,41 @@ pg_get_userbyid(PG_FUNCTION_ARGS)
PG_RETURN_NAME(result);
}
+/* ----------
+ * get_databasebyid - Get a database name by oid and
+ * fallback to 'unknown (OID=n)'
+ * ----------
+ */
+Datum
+pg_get_databasebyid(PG_FUNCTION_ARGS)
+{
+ Oid dbid = PG_GETARG_OID(0);
+ Name result;
+ HeapTuple dbtup;
+ Form_pg_database dbrec;
+
+ /*
+ * Allocate space for the result
+ */
+ result = (Name) palloc(NAMEDATALEN);
+ memset(NameStr(*result), 0, NAMEDATALEN);
+
+ /*
+ * Get the pg_database entry and print the result
+ */
+ dbtup = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbid));
+ if (HeapTupleIsValid(dbtup))
+ {
+ dbrec = (Form_pg_database) GETSTRUCT(dbtup);
+ StrNCpy(NameStr(*result), NameStr(dbrec->datname), NAMEDATALEN);
+ ReleaseSysCache(dbtup);
+ }
+ else
+ sprintf(NameStr(*result), "unknown (OID=%u)", dbid);
+
+ PG_RETURN_NAME(result);
+}
+
/*
* pg_get_serial_sequence
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index cf1f409351..4f1c55c3c7 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3573,6 +3573,9 @@
{ oid => '1642', descr => 'role name by OID (with fallback)',
proname => 'pg_get_userbyid', provolatile => 's', prorettype => 'name',
proargtypes => 'oid', prosrc => 'pg_get_userbyid' },
+{ oid => '9978', descr => 'database name by OID (with fallback)',
+ proname => 'pg_get_databasebyid', provolatile => 's', prorettype => 'name',
+ proargtypes => 'oid', prosrc => 'pg_get_databasebyid' },
{ oid => '1643', descr => 'index description',
proname => 'pg_get_indexdef', provolatile => 's', prorettype => 'text',
proargtypes => 'oid', prosrc => 'pg_get_indexdef' },
