Re: pg_get_databasebyid(oid)
Hello Thank you for attention! I marked CF entry as returned with feedback. regards, Sergei
Re: pg_get_databasebyid(oid)
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 signature.asc Description: PGP signature
Re: pg_get_databasebyid(oid)
On Thu, Aug 29, 2019 at 3:16 PM Sergei Kornilov 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
Re: pg_get_databasebyid(oid)
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
Re: pg_get_databasebyid(oid)
On Wed, Aug 28, 2019 at 6:05 PM Sergei Kornilov 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
Re: pg_get_databasebyid(oid)
> Please add that to commitfest. Done: https://commitfest.postgresql.org/24/2261/ regards, Sergei
Re: pg_get_databasebyid(oid)
On Wed, Aug 28, 2019 at 5:38 PM Sergei Kornilov 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
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, Sergeidiff --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);
pg_get_userbyid
+
+pg_get_databasebyid
+
+
pg_get_viewdef
@@ -18513,6 +18517,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
name
get role name with given OID
+
+ pg_get_databasebyid(db_oid)
+ name
+ get database name with given OID
+
pg_get_viewdef(view_name)
text
@@ -18703,8 +18712,9 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
- pg_get_userbyid extracts a role's name given
- its OID.
+ pg_get_userbyid and
+ pg_get_databasebyid extracts respectively a
+ role's and database's name given its OID.
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' },
