Skip to content

Commit bd09f02

Browse files
nathan-bossartnathan-bossart
committed
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
1 parent f20a347 commit bd09f02

File tree

18 files changed

+424
-4
lines changed

18 files changed

+424
-4
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,7 @@
3939
#include "catalog/pg_aggregate.h"
4040
#include "catalog/pg_authid.h"
4141
#include "catalog/pg_collation.h"
42+
#include "catalog/pg_database.h"
4243
#include "catalog/pg_namespace.h"
4344
#include "catalog/pg_operator.h"
4445
#include "catalog/pg_opfamily.h"
@@ -455,6 +456,11 @@ foreign_expr_walker(Node *node,
455456
AuthIdRelationId, fpinfo))
456457
return false;
457458
break;
459+
case REGDATABASEOID:
460+
if (!is_shippable(DatumGetObjectId(c->constvalue),
461+
DatabaseRelationId, fpinfo))
462+
return false;
463+
break;
458464
}
459465
}
460466

‎doc/src/sgml/datatype.sgml

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4737,6 +4737,10 @@ INSERT INTO mytable VALUES(-1); -- fails
47374737
<primary>regconfig</primary>
47384738
</indexterm>
47394739

4740+
<indexterm zone="datatype-oid">
4741+
<primary>regdatabase</primary>
4742+
</indexterm>
4743+
47404744
<indexterm zone="datatype-oid">
47414745
<primary>regdictionary</primary>
47424746
</indexterm>
@@ -4878,6 +4882,13 @@ SELECT * FROM pg_attribute
48784882
<entry><literal>english</literal></entry>
48794883
</row>
48804884

4885+
<row>
4886+
<entry><type>regdatabase</type></entry>
4887+
<entry><structname>pg_database</structname></entry>
4888+
<entry>database name</entry>
4889+
<entry><literal>template1</literal></entry>
4890+
</row>
4891+
48814892
<row>
48824893
<entry><type>regdictionary</type></entry>
48834894
<entry><structname>pg_ts_dict</structname></entry>
@@ -5049,8 +5060,8 @@ WHERE ...
50495060
be dropped without first removing the default expression. The
50505061
alternative of <literal>nextval('my_seq'::text)</literal> does not
50515062
create a dependency.
5052-
(<type>regrole</type> is an exception to this property. Constants of this
5053-
type are not allowed in stored expressions.)
5063+
(<type>regdatabase</type> and <type>regrole</type> are exceptions to this
5064+
property. Constants of these types are not allowed in stored expressions.)
50545065
</para>
50555066

50565067
<para>

‎doc/src/sgml/func.sgml

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26750,6 +26750,23 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
2675026750
</para></entry>
2675126751
</row>
2675226752

26753+
<row>
26754+
<entry role="func_table_entry"><para role="func_signature">
26755+
<indexterm>
26756+
<primary>to_regdatabase</primary>
26757+
</indexterm>
26758+
<function>to_regdatabase</function> ( <type>text</type> )
26759+
<returnvalue>regdatabase</returnvalue>
26760+
</para>
26761+
<para>
26762+
Translates a textual database name to its OID. A similar result is
26763+
obtained by casting the string to type <type>regdatabase</type> (see
26764+
<xref linkend="datatype-oid"/>); however, this function will return
26765+
<literal>NULL</literal> rather than throwing an error if the name is
26766+
not found.
26767+
</para></entry>
26768+
</row>
26769+
2675326770
<row>
2675426771
<entry role="func_table_entry"><para role="func_signature">
2675526772
<indexterm>

‎doc/src/sgml/ref/pgupgrade.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1110,7 +1110,8 @@ psql --username=postgres --file=script.sql postgres
11101110
<member><type>regproc</type></member>
11111111
<member><type>regprocedure</type></member>
11121112
</simplelist>
1113-
(<type>regclass</type>, <type>regrole</type>, and <type>regtype</type> can be upgraded.)
1113+
(<type>regclass</type>, <type>regdatabase</type>, <type>regrole</type>, and
1114+
<type>regtype</type> can be upgraded.)
11141115
</para>
11151116

11161117
<para>

‎src/backend/bootstrap/bootstrap.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -109,6 +109,8 @@ static const struct typinfo TypInfo[] = {
109109
F_REGROLEIN, F_REGROLEOUT},
110110
{"regnamespace", REGNAMESPACEOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
111111
F_REGNAMESPACEIN, F_REGNAMESPACEOUT},
112+
{"regdatabase", REGDATABASEOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,
113+
F_REGDATABASEIN, F_REGDATABASEOUT},
112114
{"text", TEXTOID, 0, -1, false, TYPALIGN_INT, TYPSTORAGE_EXTENDED, DEFAULT_COLLATION_OID,
113115
F_TEXTIN, F_TEXTOUT},
114116
{"oid", OIDOID, 0, 4, true, TYPALIGN_INT, TYPSTORAGE_PLAIN, InvalidOid,

‎src/backend/catalog/dependency.c

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1850,6 +1850,17 @@ find_expr_references_walker(Node *node,
18501850
errmsg("constant of the type %s cannot be used here",
18511851
"regrole")));
18521852
break;
1853+
1854+
/*
1855+
* Dependencies for regdatabase should be shared among all
1856+
* databases, so explicitly inhibit to have dependencies.
1857+
*/
1858+
case REGDATABASEOID:
1859+
ereport(ERROR,
1860+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1861+
errmsg("constant of the type %s cannot be used here",
1862+
"regdatabase")));
1863+
break;
18531864
}
18541865
}
18551866
return false;

‎src/backend/utils/adt/regproc.c

Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@
3030
#include "catalog/pg_ts_config.h"
3131
#include "catalog/pg_ts_dict.h"
3232
#include "catalog/pg_type.h"
33+
#include "commands/dbcommands.h"
3334
#include "lib/stringinfo.h"
3435
#include "mb/pg_wchar.h"
3536
#include "miscadmin.h"
@@ -1763,6 +1764,123 @@ regnamespacesend(PG_FUNCTION_ARGS)
17631764
return oidsend(fcinfo);
17641765
}
17651766

1767+
/*
1768+
* regdatabasein - converts database name to database OID
1769+
*
1770+
* We also accept a numeric OID, for symmetry with the output routine.
1771+
*
1772+
* '-' signifies unknown (OID 0). In all other cases, the input must
1773+
* match an existing pg_database entry.
1774+
*/
1775+
Datum
1776+
regdatabasein(PG_FUNCTION_ARGS)
1777+
{
1778+
char *db_name_or_oid = PG_GETARG_CSTRING(0);
1779+
Node *escontext = fcinfo->context;
1780+
Oid result;
1781+
List *names;
1782+
1783+
/* Handle "-" or numeric OID */
1784+
if (parseDashOrOid(db_name_or_oid, &result, escontext))
1785+
PG_RETURN_OID(result);
1786+
1787+
/* The rest of this wouldn't work in bootstrap mode */
1788+
if (IsBootstrapProcessingMode())
1789+
elog(ERROR, "regdatabase values must be OIDs in bootstrap mode");
1790+
1791+
/* Normal case: see if the name matches any pg_database entry. */
1792+
names = stringToQualifiedNameList(db_name_or_oid, escontext);
1793+
if (names == NIL)
1794+
PG_RETURN_NULL();
1795+
1796+
if (list_length(names) != 1)
1797+
ereturn(escontext, (Datum) 0,
1798+
(errcode(ERRCODE_INVALID_NAME),
1799+
errmsg("invalid name syntax")));
1800+
1801+
result = get_database_oid(strVal(linitial(names)), true);
1802+
1803+
if (!OidIsValid(result))
1804+
ereturn(escontext, (Datum) 0,
1805+
(errcode(ERRCODE_UNDEFINED_OBJECT),
1806+
errmsg("database \"%s\" does not exist",
1807+
strVal(linitial(names)))));
1808+
1809+
PG_RETURN_OID(result);
1810+
}
1811+
1812+
/*
1813+
* to_regdatabase - converts database name to database OID
1814+
*
1815+
* If the name is not found, we return NULL.
1816+
*/
1817+
Datum
1818+
to_regdatabase(PG_FUNCTION_ARGS)
1819+
{
1820+
char *db_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
1821+
Datum result;
1822+
ErrorSaveContext escontext = {T_ErrorSaveContext};
1823+
1824+
if (!DirectInputFunctionCallSafe(regdatabasein, db_name,
1825+
InvalidOid, -1,
1826+
(Node *) &escontext,
1827+
&result))
1828+
PG_RETURN_NULL();
1829+
PG_RETURN_DATUM(result);
1830+
}
1831+
1832+
/*
1833+
* regdatabaseout - converts database OID to database name
1834+
*/
1835+
Datum
1836+
regdatabaseout(PG_FUNCTION_ARGS)
1837+
{
1838+
Oid dboid = PG_GETARG_OID(0);
1839+
char *result;
1840+
1841+
if (dboid == InvalidOid)
1842+
{
1843+
result = pstrdup("-");
1844+
PG_RETURN_CSTRING(result);
1845+
}
1846+
1847+
result = get_database_name(dboid);
1848+
1849+
if (result)
1850+
{
1851+
/* pstrdup is not really necessary, but it avoids a compiler warning */
1852+
result = pstrdup(quote_identifier(result));
1853+
}
1854+
else
1855+
{
1856+
/* If OID doesn't match any database, return it numerically */
1857+
result = (char *) palloc(NAMEDATALEN);
1858+
snprintf(result, NAMEDATALEN, "%u", dboid);
1859+
}
1860+
1861+
PG_RETURN_CSTRING(result);
1862+
}
1863+
1864+
/*
1865+
* regdatabaserecv - converts external binary format to regdatabase
1866+
*/
1867+
Datum
1868+
regdatabaserecv(PG_FUNCTION_ARGS)
1869+
{
1870+
/* Exactly the same as oidrecv, so share code */
1871+
return oidrecv(fcinfo);
1872+
}
1873+
1874+
/*
1875+
* regdatabasesend - converts regdatabase to binary format
1876+
*/
1877+
Datum
1878+
regdatabasesend(PG_FUNCTION_ARGS)
1879+
{
1880+
/* Exactly the same as oidsend, so share code */
1881+
return oidsend(fcinfo);
1882+
}
1883+
17661884
/*
17671885
* text_regclass: convert text to regclass
17681886
*

‎src/backend/utils/adt/selfuncs.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4619,6 +4619,7 @@ convert_to_scalar(Datum value, Oid valuetypid, Oid collid, double *scaledvalue,
46194619
case REGDICTIONARYOID:
46204620
case REGROLEOID:
46214621
case REGNAMESPACEOID:
4622+
case REGDATABASEOID:
46224623
*scaledvalue = convert_numeric_to_scalar(value, valuetypid,
46234624
&failure);
46244625
*scaledlobound = convert_numeric_to_scalar(lobound, boundstypid,
@@ -4751,6 +4752,7 @@ convert_numeric_to_scalar(Datum value, Oid typid, bool *failure)
47514752
case REGDICTIONARYOID:
47524753
case REGROLEOID:
47534754
case REGNAMESPACEOID:
4755+
case REGDATABASEOID:
47544756
/* we can treat OIDs as integers... */
47554757
return (double) DatumGetObjectId(value);
47564758
}

‎src/backend/utils/cache/catcache.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -317,6 +317,7 @@ GetCCHashEqFuncs(Oid keytype, CCHashFN *hashfunc, RegProcedure *eqfunc, CCFastEq
317317
case REGDICTIONARYOID:
318318
case REGROLEOID:
319319
case REGNAMESPACEOID:
320+
case REGDATABASEOID:
320321
*hashfunc = int4hashfast;
321322
*fasteqfunc = int4eqfast;
322323
*eqfunc = F_OIDEQ;

‎src/bin/pg_upgrade/check.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -168,6 +168,7 @@ static DataTypesUsageChecks data_types_usage_checks[] =
168168
/* pg_class.oid is preserved, so 'regclass' is OK */
169169
" 'regcollation', "
170170
" 'regconfig', "
171+
/* pg_database.oid is preserved, so 'regdatabase' is OK */
171172
" 'regdictionary', "
172173
" 'regnamespace', "
173174
" 'regoper', "

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202506291
60+
#define CATALOG_VERSION_NO 202506301
6161

6262
#endif

‎src/include/catalog/pg_cast.dat

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -281,6 +281,20 @@
281281
castcontext => 'a', castmethod => 'f' },
282282
{ castsource => 'regnamespace', casttarget => 'int4', castfunc => '0',
283283
castcontext => 'a', castmethod => 'b' },
284+
{ castsource => 'oid', casttarget => 'regdatabase', castfunc => '0',
285+
castcontext => 'i', castmethod => 'b' },
286+
{ castsource => 'regdatabase', casttarget => 'oid', castfunc => '0',
287+
castcontext => 'i', castmethod => 'b' },
288+
{ castsource => 'int8', casttarget => 'regdatabase', castfunc => 'oid',
289+
castcontext => 'i', castmethod => 'f' },
290+
{ castsource => 'int2', casttarget => 'regdatabase', castfunc => 'int4(int2)',
291+
castcontext => 'i', castmethod => 'f' },
292+
{ castsource => 'int4', casttarget => 'regdatabase', castfunc => '0',
293+
castcontext => 'i', castmethod => 'b' },
294+
{ castsource => 'regdatabase', casttarget => 'int8', castfunc => 'int8(oid)',
295+
castcontext => 'a', castmethod => 'f' },
296+
{ castsource => 'regdatabase', casttarget => 'int4', castfunc => '0',
297+
castcontext => 'a', castmethod => 'b' },
284298

285299
# String category
286300
{ castsource => 'text', casttarget => 'bpchar', castfunc => '0',

‎src/include/catalog/pg_proc.dat

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7455,6 +7455,17 @@
74557455
prorettype => 'regnamespace', proargtypes => 'text',
74567456
prosrc => 'to_regnamespace' },
74577457

7458+
{ oid => '8321', descr => 'I/O',
7459+
proname => 'regdatabasein', provolatile => 's', prorettype => 'regdatabase',
7460+
proargtypes => 'cstring', prosrc => 'regdatabasein' },
7461+
{ oid => '8322', descr => 'I/O',
7462+
proname => 'regdatabaseout', provolatile => 's', prorettype => 'cstring',
7463+
proargtypes => 'regdatabase', prosrc => 'regdatabaseout' },
7464+
{ oid => '8323', descr => 'convert database name to regdatabase',
7465+
proname => 'to_regdatabase', provolatile => 's',
7466+
prorettype => 'regdatabase', proargtypes => 'text',
7467+
prosrc => 'to_regdatabase' },
7468+
74587469
{ oid => '6210', descr => 'test whether string is valid input for data type',
74597470
proname => 'pg_input_is_valid', provolatile => 's', prorettype => 'bool',
74607471
proargtypes => 'text text', prosrc => 'pg_input_is_valid' },
@@ -8313,6 +8324,12 @@
83138324
{ oid => '4088', descr => 'I/O',
83148325
proname => 'regnamespacesend', prorettype => 'bytea',
83158326
proargtypes => 'regnamespace', prosrc => 'regnamespacesend' },
8327+
{ oid => '8324', descr => 'I/O',
8328+
proname => 'regdatabaserecv', prorettype => 'regdatabase',
8329+
proargtypes => 'internal', prosrc => 'regdatabaserecv' },
8330+
{ oid => '8325', descr => 'I/O',
8331+
proname => 'regdatabasesend', prorettype => 'bytea',
8332+
proargtypes => 'regdatabase', prosrc => 'regdatabasesend' },
83168333
{ oid => '2456', descr => 'I/O',
83178334
proname => 'bit_recv', prorettype => 'bit',
83188335
proargtypes => 'internal oid int4', prosrc => 'bit_recv' },

‎src/include/catalog/pg_type.dat

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -399,6 +399,11 @@
399399
typinput => 'regnamespacein', typoutput => 'regnamespaceout',
400400
typreceive => 'regnamespacerecv', typsend => 'regnamespacesend',
401401
typalign => 'i' },
402+
{ oid => '8326', array_type_oid => '8327', descr => 'registered database',
403+
typname => 'regdatabase', typlen => '4', typbyval => 't', typcategory => 'N',
404+
typinput => 'regdatabasein', typoutput => 'regdatabaseout',
405+
typreceive => 'regdatabaserecv', typsend => 'regdatabasesend',
406+
typalign => 'i' },
402407

403408
# uuid
404409
{ oid => '2950', array_type_oid => '2951', descr => 'UUID',

0 commit comments

Comments
 (0)