Re: [PATCH] Add pg_get_tablespace_ddl() function to reconstruct CREATE TABLESPACE statement

From 04d4e447cfa2bcd8124f3b44fc4ea2ba6a813455 Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Thu, 13 Nov 2025 10:10:03 -0600
Subject: [PATCH v13] Adds pg_get_tablespace_ddl function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Currently, there exist ways of "wholesale" dumping the DDL for an entire
database or even cluster; this function will ideally be part of a suite
of similar "retail" functions for dumping the DDL of various database
objects.

Authors: Manni Wood <manni.wood@enterprisedb.com> and Nishant Sharma
<nishant.sharma@enterprisedb.com>
Reviewers: Vaibhav Dalvi, Ian Barwick, Jim Jones, Álvaro Herrera
Discussion:
https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3Scsnj02C0kObQjnbL2ajfPWGEw@mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml         |  45 +++++++++
 src/backend/catalog/pg_tablespace.c      | 118 +++++++++++++++++++++++
 src/backend/utils/adt/ruleutils.c        |  37 +++++++
 src/include/catalog/pg_proc.dat          |   6 ++
 src/include/catalog/pg_tablespace.h      |   1 +
 src/test/regress/expected/tablespace.out |  69 +++++++++++++
 src/test/regress/sql/tablespace.sql      |  47 +++++++++
 7 files changed, 323 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..64c56e0a469 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,49 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Get Object DDL Functions</title>
+
+   <para>
+    The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+    print the DDL statements for various database objects.
+    (This is a decompiled reconstruction, not the original text
+    of the command.)
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Get Object DDL Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_tablespace_ddl</primary>
+        </indexterm>
+        <function>pg_get_tablespace_ddl</function>
+        ( <parameter>tablespace</parameter> <type>name</type> or <type>oid</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the creating command for a tablespace.
+        The result is a complete <command>CREATE TABLESPACE</command> statement.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/catalog/pg_tablespace.c b/src/backend/catalog/pg_tablespace.c
index 6aca24c231e..8f04ef7a6b2 100644
--- a/src/backend/catalog/pg_tablespace.c
+++ b/src/backend/catalog/pg_tablespace.c
@@ -17,9 +17,13 @@
 #include <unistd.h>
 #include <sys/stat.h>
 
+#include "access/htup_details.h"
 #include "catalog/pg_tablespace.h"
 #include "commands/tablespace.h"
 #include "miscadmin.h"
+#include "utils/array.h"
+#include "utils/builtins.h"
+#include "utils/syscache.h"
 
 
 /*
@@ -88,3 +92,117 @@ get_tablespace_location(Oid tablespaceOid)
 
 	return pstrdup(targetpath);
 }
+
+/*
+ * build_tablespace_ddl_string - Build CREATE TABLESPACE statement as a
+ * C-string for a tablespace from its OID.
+ */
+char *
+build_tablespace_ddl_string(const Oid tspaceoid)
+{
+	char	   *path;
+	char	   *spcowner;
+	bool		isNull;
+	Oid			tspowneroid;
+	Datum		datum;
+	HeapTuple	tuple;
+	StringInfoData buf;
+	Form_pg_tablespace tspForm;
+
+	/* Look up the tablespace in pg_tablespace */
+	tuple = SearchSysCache1(TABLESPACEOID, ObjectIdGetDatum(tspaceoid));
+
+	/* Confirm if tablespace OID was valid */
+	if (!HeapTupleIsValid(tuple))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("tablespace with oid %u does not exist",
+						tspaceoid)));
+
+	/* Get tablespace's details from its tuple */
+	tspForm = (Form_pg_tablespace) GETSTRUCT(tuple);
+
+	initStringInfo(&buf);
+
+	/* Start building the CREATE TABLESPACE statement */
+	appendStringInfo(&buf, "CREATE TABLESPACE %s",
+					 quote_identifier(NameStr(tspForm->spcname)));
+
+	/* Get the OID of the owner of the tablespace name */
+	tspowneroid = tspForm->spcowner;
+
+	/* Add OWNER clause, if the owner is not the current user */
+	if (GetUserId() != tspowneroid)
+	{
+		/* Get the owner name */
+		spcowner = GetUserNameFromId(tspowneroid, false);
+
+		appendStringInfo(&buf, " OWNER %s",
+						 quote_identifier(spcowner));
+		pfree(spcowner);
+	}
+
+	/* Find tablespace directory path */
+	path = get_tablespace_location(tspaceoid);
+
+	/* Add directory LOCATION (path), if it exists */
+	if (path[0] != '\0')
+	{
+		/*
+		 * Special case: if the tablespace was created with GUC
+		 * "allow_in_place_tablespaces = true" and "LOCATION ''", path will
+		 * begin with "pg_tblspc/". In that case, show "LOCATION ''" as the
+		 * user originally specified.
+		 */
+		if (strncmp(PG_TBLSPC_DIR_SLASH, path, strlen(PG_TBLSPC_DIR_SLASH)) == 0)
+			appendStringInfoString(&buf, " LOCATION ''");
+		else
+			appendStringInfo(&buf, " LOCATION %s", quote_literal_cstr(path));
+	}
+	/* Done with path */
+	pfree(path);
+
+	/* Get tablespace's options datum from the tuple */
+	datum = SysCacheGetAttr(TABLESPACEOID,
+							tuple,
+							Anum_pg_tablespace_spcoptions,
+							&isNull);
+
+	if (!isNull)
+	{
+		ArrayType  *optarray;
+		Datum	   *optdatums;
+		int			optcount;
+		int			i;
+
+		optarray = DatumGetArrayTypeP(datum);
+
+		deconstruct_array_builtin(optarray, TEXTOID,
+								  &optdatums, NULL, &optcount);
+
+		Assert(optcount);
+
+		/* Start WITH clause */
+		appendStringInfoString(&buf, " WITH (");
+
+		for (i = 0; i < (optcount - 1); i++)	/* Skipping last option */
+		{
+			/* Add the options in WITH clause */
+			appendStringInfo(&buf, "%s, ", TextDatumGetCString(optdatums[i]));
+		}
+
+		/* Adding the last remaining option */
+		appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+		/* Closing WITH clause */
+		appendStringInfoChar(&buf, ')');
+		/* Cleanup the datums found */
+		pfree(optdatums);
+	}
+
+	ReleaseSysCache(tuple);
+
+	/* Finally add semicolon to the statement */
+	appendStringInfoChar(&buf, ';');
+
+	return buf.data;
+}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..dc5365fbeae 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -35,6 +35,7 @@
 #include "catalog/pg_partitioned_table.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_tablespace.h"
 #include "catalog/pg_trigger.h"
 #include "catalog/pg_type.h"
 #include "commands/defrem.h"
@@ -13743,3 +13744,39 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * pg_get_tablespace_ddl_name - Get CREATE TABLESPACE statement for a
+ * tablespace. This takes name as parameter for pg_get_tablespace_ddl().
+ */
+Datum
+pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS)
+{
+	Name		tspname = PG_GETARG_NAME(0);
+	Oid			tspaceoid;
+	char	   *ddl_stmt;
+
+	/* Get the OID of the tablespace from its name */
+	tspaceoid = get_tablespace_oid(NameStr(*tspname), false);
+
+	/* Get the CREATE TABLESPACE DDL statement from its OID */
+	ddl_stmt = build_tablespace_ddl_string(tspaceoid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * pg_get_tablespace_ddl_oid - Get CREATE TABLESPACE statement for a
+ * tablespace. This takes oid as parameter for pg_get_tablespace_ddl().
+ */
+Datum
+pg_get_tablespace_ddl_oid(PG_FUNCTION_ARGS)
+{
+	Oid			tspaceoid = PG_GETARG_OID(0);
+	char	   *ddl_stmt;
+
+	/* Get the CREATE TABLESPACE DDL statement from its OID */
+	ddl_stmt = build_tablespace_ddl_string(tspaceoid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5cf9e12fcb9..0fcd0f4fa8f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8515,6 +8515,12 @@
 { oid => '2508', descr => 'constraint description with pretty-print option',
   proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid bool', prosrc => 'pg_get_constraintdef_ext' },
+{ oid => '8758', descr => 'get CREATE statement for tablespace',
+  proname => 'pg_get_tablespace_ddl', prorettype => 'text',
+  proargtypes => 'name', prosrc => 'pg_get_tablespace_ddl_name' },
+{ oid => '8759', descr => 'get CREATE statement for tablespace',
+  proname => 'pg_get_tablespace_ddl', prorettype => 'text',
+  proargtypes => 'oid', prosrc => 'pg_get_tablespace_ddl_oid' },
 { oid => '2509',
   descr => 'deparse an encoded expression with pretty-print option',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/include/catalog/pg_tablespace.h b/src/include/catalog/pg_tablespace.h
index 7816d779d8c..b70035832df 100644
--- a/src/include/catalog/pg_tablespace.h
+++ b/src/include/catalog/pg_tablespace.h
@@ -55,5 +55,6 @@ DECLARE_UNIQUE_INDEX(pg_tablespace_spcname_index, 2698, TablespaceNameIndexId, p
 MAKE_SYSCACHE(TABLESPACEOID, pg_tablespace_oid_index, 4);
 
 extern char *get_tablespace_location(Oid tablespaceOid);
+extern char *build_tablespace_ddl_string(const Oid tspaceoid);
 
 #endif							/* PG_TABLESPACE_H */
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..6b77e3323d1 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -971,3 +971,72 @@ drop cascades to materialized view testschema.amv
 drop cascades to table testschema.tablespace_acl
 DROP ROLE regress_tablespace_user1;
 DROP ROLE regress_tablespace_user2;
+-- Test pg_get_tablespace_ddl() by creating tablespaces with various
+-- configurations and checking the DDL.
+SET allow_in_place_tablespaces = true;
+-- create a tablespace using no options
+CREATE TABLESPACE regress_noopt_tblsp LOCATION ''; -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_noopt_tblsp');
+               pg_get_tablespace_ddl                
+----------------------------------------------------
+ CREATE TABLESPACE regress_noopt_tblsp LOCATION '';
+(1 row)
+
+DROP TABLESPACE regress_noopt_tblsp;
+-- create a tablespace with a space in the name
+CREATE TABLESPACE "regress_ tblsp" LOCATION ''; -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_ tblsp');
+              pg_get_tablespace_ddl              
+-------------------------------------------------
+ CREATE TABLESPACE "regress_ tblsp" LOCATION '';
+(1 row)
+
+DROP TABLESPACE "regress_ tblsp";
+-- create a tablespace using one option
+CREATE TABLESPACE regress_oneopt_tblsp LOCATION '' WITH (random_page_cost = 3.0); -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_oneopt_tblsp');
+                              pg_get_tablespace_ddl                              
+---------------------------------------------------------------------------------
+ CREATE TABLESPACE regress_oneopt_tblsp LOCATION '' WITH (random_page_cost=3.0);
+(1 row)
+
+DROP TABLESPACE regress_oneopt_tblsp;
+-- create tablespace owned by a particular user
+CREATE USER regress_user;
+CREATE TABLESPACE regress_owner_tblsp OWNER regress_user LOCATION '' WITH (random_page_cost = 3.0); -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_owner_tblsp');
+                                       pg_get_tablespace_ddl                                       
+---------------------------------------------------------------------------------------------------
+ CREATE TABLESPACE regress_owner_tblsp OWNER regress_user LOCATION '' WITH (random_page_cost=3.0);
+(1 row)
+
+DROP TABLESPACE regress_owner_tblsp;
+DROP USER regress_user;
+-- create a tablespace using all the options
+CREATE TABLESPACE regress_allopt_tblsp LOCATION '' WITH (seq_page_cost = 1.5, random_page_cost = 1.1234567890, effective_io_concurrency = 17, maintenance_io_concurrency = 18); -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_allopt_tblsp');
+                                                                          pg_get_tablespace_ddl                                                                          
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLESPACE regress_allopt_tblsp LOCATION '' WITH (seq_page_cost=1.5, random_page_cost=1.1234567890, effective_io_concurrency=17, maintenance_io_concurrency=18);
+(1 row)
+
+DROP TABLESPACE regress_allopt_tblsp;
+-- see the pg_get_tablespace_ddl error for a dropped 'regress_allopt_tblsp'
+-- tablespace name
+SELECT pg_get_tablespace_ddl('regress_allopt_tblsp');
+ERROR:  tablespace "regress_allopt_tblsp" does not exist
+-- see the pg_get_tablespace_ddl error for an empty input
+SELECT pg_get_tablespace_ddl('');
+ERROR:  tablespace "" does not exist
+-- see the pg_get_tablespace_ddl output for a NULL input
+SELECT pg_get_tablespace_ddl(NULL);
+ pg_get_tablespace_ddl 
+-----------------------
+ 
+(1 row)
+
diff --git a/src/test/regress/sql/tablespace.sql b/src/test/regress/sql/tablespace.sql
index dfe3db096e2..6a49100ca14 100644
--- a/src/test/regress/sql/tablespace.sql
+++ b/src/test/regress/sql/tablespace.sql
@@ -437,3 +437,50 @@ DROP SCHEMA testschema CASCADE;
 
 DROP ROLE regress_tablespace_user1;
 DROP ROLE regress_tablespace_user2;
+
+-- Test pg_get_tablespace_ddl() by creating tablespaces with various
+-- configurations and checking the DDL.
+
+SET allow_in_place_tablespaces = true;
+
+-- create a tablespace using no options
+CREATE TABLESPACE regress_noopt_tblsp LOCATION ''; -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_noopt_tblsp');
+DROP TABLESPACE regress_noopt_tblsp;
+
+-- create a tablespace with a space in the name
+CREATE TABLESPACE "regress_ tblsp" LOCATION ''; -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_ tblsp');
+DROP TABLESPACE "regress_ tblsp";
+
+-- create a tablespace using one option
+CREATE TABLESPACE regress_oneopt_tblsp LOCATION '' WITH (random_page_cost = 3.0); -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_oneopt_tblsp');
+DROP TABLESPACE regress_oneopt_tblsp;
+
+-- create tablespace owned by a particular user
+CREATE USER regress_user;
+CREATE TABLESPACE regress_owner_tblsp OWNER regress_user LOCATION '' WITH (random_page_cost = 3.0); -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_owner_tblsp');
+DROP TABLESPACE regress_owner_tblsp;
+DROP USER regress_user;
+
+-- create a tablespace using all the options
+CREATE TABLESPACE regress_allopt_tblsp LOCATION '' WITH (seq_page_cost = 1.5, random_page_cost = 1.1234567890, effective_io_concurrency = 17, maintenance_io_concurrency = 18); -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_allopt_tblsp');
+DROP TABLESPACE regress_allopt_tblsp;
+
+-- see the pg_get_tablespace_ddl error for a dropped 'regress_allopt_tblsp'
+-- tablespace name
+SELECT pg_get_tablespace_ddl('regress_allopt_tblsp');
+
+-- see the pg_get_tablespace_ddl error for an empty input
+SELECT pg_get_tablespace_ddl('');
+
+-- see the pg_get_tablespace_ddl output for a NULL input
+SELECT pg_get_tablespace_ddl(NULL);
-- 
2.51.2