pg_get_databasebyid(oid)
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
Attachments:
v1_pg_get_databasebyid.patchtext/x-diff; name=v1_pg_get_databasebyid.patchDownload
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' },
On Wed, Aug 28, 2019 at 5:38 PM Sergei Kornilov <sk@zsrv.org> wrote:
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
Please add that to commitfest.
--
Ibrar Ahmed
On Wed, Aug 28, 2019 at 6:05 PM Sergei Kornilov <sk@zsrv.org> wrote:
Please add that to commitfest.
Done: https://commitfest.postgresql.org/24/2261/
regards, Sergei
Hi,
I have checked the code, the function "pg_get_userbyid" is used in many
places in code. I am just curious why we need that "pg_get_databasebyid"
function. Is there a need for this function for the user?
--
Ibrar Ahmed
Hello
Is there a need for this function for the user?
This was feature request from user. I got such comment:
This function is useful when working with pg_stat_statements. For obtaining a databаse name for particular query you need to join pg_database relation, but for obtaining an username you just need pg_get_userbyid(). So it will be useful not to join extra relation and get a database name using the similar function - pg_get_databasebyid().
regards, Sergei
On Thu, Aug 29, 2019 at 3:16 PM Sergei Kornilov <sk@zsrv.org> wrote:
Hello
Is there a need for this function for the user?
This was feature request from user. I got such comment:
This function is useful when working with pg_stat_statements. For
obtaining a databаse name for particular query you need to join pg_database
relation, but for obtaining an username you just need pg_get_userbyid(). So
it will be useful not to join extra relation and get a database name using
the similar function - pg_get_databasebyid().regards, Sergei
Hi,
I think its a user request and don't require to be in the core of
PostgreSQL.
A simple SQL function can fulfill the requirement of the user.
CREATE OR REPLACE FUNCTION pg_get_databasebyid(dboid integer) RETURNS name
AS $$
SELECT datname from pg_database WHERE oid = dboid;
$$ LANGUAGE SQL;
--
Ibrar Ahmed
On Thu, Aug 29, 2019 at 03:47:40PM +0500, Ibrar Ahmed wrote:
I think its a user request and don't require to be in the core of
PostgreSQL.
A simple SQL function can fulfill the requirement of the user.CREATE OR REPLACE FUNCTION pg_get_databasebyid(dboid integer) RETURNS name
AS $$SELECT datname from pg_database WHERE oid = dboid;
$$ LANGUAGE SQL;
Indeed, I think that we can drop the patch. FWIW, I find the
semantics of pg_get_userbyid() horrible when it comes to return a
result for a non-existing user with its own way of defining how this
information should show up. Returning NULL would be more natural, so
I don't think that we should make more functions behave the same way.
--
Michael