Hi all,
Good day!
I am a newbee to PostgreSQL and recently came across an idea about
type-casting tablespace OID.
The motibation is that when I have to upgrade a PostgreSQL database, we
need to join other tables to
track tablespace name. I have just created a simple patch to resolve this.
Hope you can take a look with this.
My Execution Sample:
# After Patch:
------------------------------------------------------------------------
postgres=# SELECT oid,oid::regtablespace,spcname from pg_tablespace ;
oid | oid | spcname
------+------------+------------
1663 | pg_default | pg_default
1664 | pg_global | pg_global
(2 rows)
------------------------------------------------------------------------
# Before Patch
------------------------------------------------------------------------
postgres-# SELECT oid,oid::regtablespace,spcname from pg_tablespace ;
ERROR: syntax error at or near "oid"
LINE 1: oid | oid | spcname
^
------------------------------------------------------------------------
I added the "::regtablespace" part to source.
Note: While developing, I also had to add several rows to pgcatalog tables.
Please point out if any OID newly assigned is not appropriate.
Kind Regards,
Yuki Tei
diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c
index 296930eb3b..3ab0f75c84 100644
--- a/src/backend/utils/adt/regproc.c
+++ b/src/backend/utils/adt/regproc.c
@@ -30,6 +30,7 @@
#include "catalog/pg_ts_config.h"
#include "catalog/pg_ts_dict.h"
#include "catalog/pg_type.h"
+#include "commands/tablespace.h"
#include "lib/stringinfo.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
@@ -869,6 +870,122 @@ regoperatorsend(PG_FUNCTION_ARGS)
return oidsend(fcinfo);
}
+/*
+ * regtablespacein - converts "tablespacename" to tablespace OID
+ *
+ * We also accept a numeric OID, for symmetry with the output routine.
+ *
+ * '-' signifies unknown (OID 0). In all other cases, the input must
+ * match an existing pg_tablespace entry.
+ */
+Datum
+regtablespacein(PG_FUNCTION_ARGS)
+{
+ char *tablespace_name_or_oid = PG_GETARG_CSTRING(0);
+ Node *escontext = fcinfo->context;
+ Oid result;
+ List *names;
+
+ /* Handle "-" or numeric OID */
+ if (parseDashOrOid(tablespace_name_or_oid, &result, escontext))
+ PG_RETURN_OID(result);
+
+ /* The rest of this wouldn't work in bootstrap mode */
+ if (IsBootstrapProcessingMode())
+ elog(ERROR, "regtablespace values must be OIDs in bootstrap mode");
+
+ /* Normal case: see if the name matches any pg_tablespace entry. */
+ names = stringToQualifiedNameList(tablespace_name_or_oid, escontext);
+ if (names == NIL)
+ PG_RETURN_NULL();
+
+ if (list_length(names) != 1)
+ ereturn(escontext, (Datum) 0,
+ (errcode(ERRCODE_INVALID_NAME),
+ errmsg("invalid name syntax")));
+
+ result = get_tablespace_oid(strVal(linitial(names)), true);
+
+ if (!OidIsValid(result))
+ ereturn(escontext, (Datum) 0,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("tablespace \"%s\" does not exist",
+ strVal(linitial(names)))));
+
+ PG_RETURN_OID(result);
+}
+
+/*
+ * to_regtablespace - converts "tablespacename" to tablespace OID
+ *
+ * If the name is not found, we return NULL.
+ */
+Datum
+to_regtablespace(PG_FUNCTION_ARGS)
+{
+ char *tablespace_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ Datum result;
+ ErrorSaveContext escontext = {T_ErrorSaveContext};
+
+ if (!DirectInputFunctionCallSafe(regtablespacein, tablespace_name,
+ InvalidOid, -1,
+ (Node *) &escontext,
+ &result))
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(result);
+}
+
+/*
+ * regtablespaceout - converts tablespace OID to "tablespace_name"
+ */
+Datum
+regtablespaceout(PG_FUNCTION_ARGS)
+{
+ Oid spcid = PG_GETARG_OID(0);
+ char *result;
+
+ if (spcid == InvalidOid)
+ {
+ result = pstrdup("-");
+ PG_RETURN_CSTRING(result);
+ }
+
+ result = get_tablespace_name(spcid);
+
+ if (result)
+ {
+ /* pstrdup is not really necessary, but it avoids a compiler warning */
+ result = pstrdup(quote_identifier(result));
+ }
+ else
+ {
+ /* If OID doesn't match any tablespace, return it numerically */
+ result = (char *) palloc(NAMEDATALEN);
+ snprintf(result, NAMEDATALEN, "%u", spcid);
+ }
+
+ PG_RETURN_CSTRING(result);
+}
+
+/*
+ * regtablespacerecv - converts external binary format to regtablespace
+ */
+Datum
+regtablespacerecv(PG_FUNCTION_ARGS)
+{
+ /* Exactly the same as oidrecv, so share code */
+ return oidrecv(fcinfo);
+}
+
+/*
+ * regtablespacesend - converts regtablespace to binary format
+ */
+Datum
+regtablespacesend(PG_FUNCTION_ARGS)
+{
+ /* Exactly the same as oidsend, so share code */
+ return oidsend(fcinfo);
+}
/*
* regclassin - converts "classname" to class OID
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index fb3a7b3f3b..f7f78e3a0d 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -281,6 +281,20 @@
castcontext => 'a', castmethod => 'f' },
{ castsource => 'regnamespace', casttarget => 'int4', castfunc => '0',
castcontext => 'a', castmethod => 'b' },
+{ castsource => 'oid', casttarget => 'regtablespace', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'regtablespace', casttarget => 'oid', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'int8', casttarget => 'regtablespace', castfunc => 'oid',
+ castcontext => 'i', castmethod => 'f' },
+{ castsource => 'int2', casttarget => 'regtablespace', castfunc => 'int4(int2)',
+ castcontext => 'i', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'regtablespace', castfunc => '0',
+ castcontext => 'i', castmethod => 'b' },
+{ castsource => 'regtablespace', casttarget => 'int8', castfunc => 'int8(oid)',
+ castcontext => 'a', castmethod => 'f' },
+{ castsource => 'regtablespace', casttarget => 'int4', castfunc => '0',
+ castcontext => 'a', castmethod => 'b' },
# String category
{ castsource => 'text', casttarget => 'bpchar', castfunc => '0',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9805bc6118..c0f7f41848 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7154,6 +7154,17 @@
prorettype => 'regnamespace', proargtypes => 'text',
prosrc => 'to_regnamespace' },
+{ oid => '6403', descr => 'I/O',
+ proname => 'regtablespacein', provolatile => 's', prorettype => 'regtablespace',
+ proargtypes => 'cstring', prosrc => 'regtablespacein' },
+{ oid => '6404', descr => 'I/O',
+ proname => 'regtablespaceout', provolatile => 's', prorettype => 'cstring',
+ proargtypes => 'regtablespace', prosrc => 'regtablespaceout' },
+{ oid => '6405', descr => 'convert tablespacespace name to regtablespace',
+ proname => 'to_regtablespace', provolatile => 's',
+ prorettype => 'regtablespace', proargtypes => 'text',
+ prosrc => 'to_regtablespace' },
+
{ oid => '6210', descr => 'test whether string is valid input for data type',
proname => 'pg_input_is_valid', provolatile => 's', prorettype => 'bool',
proargtypes => 'text text', prosrc => 'pg_input_is_valid' },
@@ -8000,6 +8011,12 @@
{ oid => '4088', descr => 'I/O',
proname => 'regnamespacesend', prorettype => 'bytea',
proargtypes => 'regnamespace', prosrc => 'regnamespacesend' },
+{ oid => '6406', descr => 'I/O',
+ proname => 'regtablespacerecv', prorettype => 'regtablespace',
+ proargtypes => 'internal', prosrc => 'regtablespacerecv' },
+{ oid => '6407', descr => 'I/O',
+ proname => 'regtablespacesend', prorettype => 'bytea',
+ proargtypes => 'regtablespace', prosrc => 'regtablespacesend' },
{ oid => '2456', descr => 'I/O',
proname => 'bit_recv', prorettype => 'bit',
proargtypes => 'internal oid int4', prosrc => 'bit_recv' },
diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat
index 92bcaf2c73..887a44dadc 100644
--- a/src/include/catalog/pg_type.dat
+++ b/src/include/catalog/pg_type.dat
@@ -398,6 +398,11 @@
typinput => 'regnamespacein', typoutput => 'regnamespaceout',
typreceive => 'regnamespacerecv', typsend => 'regnamespacesend',
typalign => 'i' },
+{ oid => '6401', array_type_oid => '6402', descr => 'registered tablespace',
+ typname => 'regtablespace', typlen => '4', typbyval => 't', typcategory => 'N',
+ typinput => 'regtablespacein', typoutput => 'regtablespaceout',
+ typreceive => 'regtablespacerecv', typsend => 'regtablespacesend',
+ typalign => 'i' },
# uuid
{ oid => '2950', array_type_oid => '2951', descr => 'UUID datatype',
