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

From 7a230ef1cac34988dbbae2b9ba6c401e149caad5 Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Mon, 3 Nov 2025 17:11:00 -0600
Subject: [PATCH v2] Adds pg_get_tablespace_ddl function

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.
---
 doc/src/sgml/func/func-info.sgml         |  45 +++++++++
 src/backend/utils/adt/ruleutils.c        | 113 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |   3 +
 src/test/regress/expected/tablespace.out |  68 ++++++++++++++
 src/test/regress/sql/tablespace.sql      |  46 +++++++++
 5 files changed, 275 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..79ef14c6b2f 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> )
+        <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/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 79ec136231b..85b55810ddf 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -22,6 +22,7 @@
 #include "access/amapi.h"
 #include "access/htup_details.h"
 #include "access/relation.h"
+#include "access/reloptions.h"
 #include "access/table.h"
 #include "catalog/pg_aggregate.h"
 #include "catalog/pg_am.h"
@@ -35,6 +36,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"
@@ -67,6 +69,7 @@
 #include "utils/rel.h"
 #include "utils/ruleutils.h"
 #include "utils/snapmgr.h"
+#include "utils/spccache.h"
 #include "utils/syscache.h"
 #include "utils/typcache.h"
 #include "utils/varlena.h"
@@ -13738,3 +13741,113 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf->data;
 }
+
+/*
+ * pg_get_tablespace_ddl - Get CREATE TABLESPACE statement for a tablespace
+ */
+Datum
+pg_get_tablespace_ddl(PG_FUNCTION_ARGS)
+{
+	Name		tspname = PG_GETARG_NAME(0);
+	char	   *path;
+	char	   *spcowner;
+	bool		isNull;
+	Oid			tspaceoid;
+	Oid			tspowneroid;
+	Datum		datumLocation;
+	Datum		datum;
+	HeapTuple	tuple;
+	StringInfoData buf;
+	Form_pg_tablespace tspForm;
+	ArrayType  *optArray;
+	Datum	   *optDatums;
+	int			optCount;
+	char	   *optCStr;
+
+	/* Get the OID of the tablespace; we need it to find the tablespace */
+	tspaceoid = get_tablespace_oid(NameStr(*tspname), false);
+
+	/* Look up the tablespace in pg_tablespace */
+	tuple = SearchSysCache1(TABLESPACEOID, ObjectIdGetDatum(tspaceoid));
+
+	Assert(HeapTupleIsValid(tuple));
+
+	initStringInfo(&buf);
+
+	/* Start building the CREATE TABLESPACE statement */
+	appendStringInfo(&buf, "CREATE TABLESPACE %s",
+					 quote_identifier(NameStr(*tspname)));
+
+	/* Get the OID of the owner of the tablespace name */
+	tspForm = (Form_pg_tablespace) GETSTRUCT(tuple);
+	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));
+	}
+
+	/* Find tablespace directory path */
+	datumLocation = DirectFunctionCall1(pg_tablespace_location, tspaceoid);
+	path = text_to_cstring(DatumGetTextP(datumLocation));
+	/* 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'", path);
+	}
+
+	/* Get tablespace's options datum from the tuple */
+	datum = SysCacheGetAttr(TABLESPACEOID,
+							tuple,
+							Anum_pg_tablespace_spcoptions,
+							&isNull);
+
+	// NEW
+	if (!isNull)
+	{
+		optArray = DatumGetArrayTypeP(datum);
+
+		/* parseRelOptionsInternal() provided inspiration here */
+		deconstruct_array_builtin(optArray, TEXTOID,
+				&optDatums, NULL, &optCount);
+
+		if (optCount > 0)
+		{
+			appendStringInfoString(&buf, " WITH (");
+			for (int i = 0; i < optCount; i++)
+			{
+				optCStr = TextDatumGetCString(optDatums[i]);
+				appendStringInfoString(&buf, optCStr);
+				appendStringInfoString(&buf, ", ");
+			}
+			/* buf ends up with unwanted trailing comma and space; remove them */
+			buf.len -= 2;
+			buf.data[buf.len] = '\0';	/* Null-terminate the modified string */
+
+			appendStringInfoChar(&buf, ')');
+		}
+
+		pfree(optDatums);
+	}
+
+	ReleaseSysCache(tuple);
+
+	/* Finally add semicolon to the statement */
+	appendStringInfoChar(&buf, ';');
+
+	PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9121a382f76..022c3493542 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8515,6 +8515,9 @@
 { 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' },
 { oid => '2509',
   descr => 'deparse an encoded expression with pretty-print option',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..ef472f8adb9 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -971,3 +971,71 @@ 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)
+
+-- 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..db3a3705065 100644
--- a/src/test/regress/sql/tablespace.sql
+++ b/src/test/regress/sql/tablespace.sql
@@ -437,3 +437,49 @@ 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');
+
+-- 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.43.0

From c751c0c6301ba371f96825025407dcaccb889e76 Mon Sep 17 00:00:00 2001
From: Nishant Sharma <nishant.sharma@enterprisedb.com>
Date: Tue, 4 Nov 2025 15:40:10 +0530
Subject: [PATCH v3] Adds pg_get_tablespace_ddl function

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
Discussion: https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3Scsnj02C0kObQjnbL2ajfPWGEw@mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml         |  45 ++++++++++
 src/backend/utils/adt/ruleutils.c        | 110 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |   3 +
 src/test/regress/expected/tablespace.out |  69 ++++++++++++++
 src/test/regress/sql/tablespace.sql      |  47 ++++++++++
 5 files changed, 274 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..79ef14c6b2f 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> )
+        <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/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 79ec136231b..89c977be3b5 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"
@@ -13738,3 +13739,112 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf->data;
 }
+
+/*
+ * pg_get_tablespace_ddl - Get CREATE TABLESPACE statement for a tablespace
+ */
+Datum
+pg_get_tablespace_ddl(PG_FUNCTION_ARGS)
+{
+	Name		tspname = PG_GETARG_NAME(0);
+	char	   *path;
+	char	   *spcowner;
+	bool		isNull;
+	Oid			tspaceoid;
+	Oid			tspowneroid;
+	Datum		datumlocation;
+	Datum		datum;
+	HeapTuple	tuple;
+	StringInfoData buf;
+	Form_pg_tablespace tspForm;
+
+	/* Get the OID of the tablespace; we need it to find the tablespace */
+	tspaceoid = get_tablespace_oid(NameStr(*tspname), false);
+
+	/* Look up the tablespace in pg_tablespace */
+	tuple = SearchSysCache1(TABLESPACEOID, ObjectIdGetDatum(tspaceoid));
+
+	Assert(HeapTupleIsValid(tuple));
+
+	initStringInfo(&buf);
+
+	/* Start building the CREATE TABLESPACE statement */
+	appendStringInfo(&buf, "CREATE TABLESPACE %s",
+					 quote_identifier(NameStr(*tspname)));
+
+	/* Get the OID of the owner of the tablespace name */
+	tspForm = (Form_pg_tablespace) GETSTRUCT(tuple);
+	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));
+	}
+
+	/* Find tablespace directory path */
+	datumlocation = DirectFunctionCall1(pg_tablespace_location, tspaceoid);
+	path = text_to_cstring(DatumGetTextP(datumlocation));
+	/* 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'", 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 */
+		{
+			appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+			appendStringInfoString(&buf, ", ");
+		}
+
+		/* 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, ';');
+
+	PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9121a382f76..022c3493542 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8515,6 +8515,9 @@
 { 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' },
 { oid => '2509',
   descr => 'deparse an encoded expression with pretty-print option',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..c2fed08acf7 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..aca170992d6 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.47.3

From 91cbdd8d85617b2c9f9ed6b8c5cf1e036911baf6 Mon Sep 17 00:00:00 2001
From: Nishant Sharma <nishant.sharma@enterprisedb.com>
Date: Wed, 5 Nov 2025 11:46:49 +0530
Subject: [PATCH v4] Adds pg_get_tablespace_ddl function

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, Alvaro 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/utils/adt/ruleutils.c        | 137 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |   6 +
 src/test/regress/expected/tablespace.out |  69 ++++++++++++
 src/test/regress/sql/tablespace.sql      |  47 ++++++++
 5 files changed, 304 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..79ef14c6b2f 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> )
+        <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/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 5398679cce2..a3b0dcfdca1 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"
@@ -546,6 +547,7 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
+static text *build_tablespace_ddl(const Oid tspaceoid);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -13742,3 +13744,138 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf->data;
 }
+
+/*
+ * build_tablespace_ddl - Build CREATE TABLESPACE statement for a
+ * tablespace from its OID. This is internal version which helps
+ * pg_get_tablespace_ddl_name() and pg_get_tablespace_ddl_oid().
+ */
+static text *
+build_tablespace_ddl(const Oid tspaceoid)
+{
+	char	   *path;
+	char	   *spcowner;
+	bool		isNull;
+	Oid			tspowneroid;
+	Datum		datumlocation;
+	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 %d 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));
+	}
+
+	/* Find tablespace directory path */
+	datumlocation = DirectFunctionCall1(pg_tablespace_location, tspaceoid);
+	path = text_to_cstring(DatumGetTextP(datumlocation));
+	/* 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'", 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 */
+			appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+			appendStringInfoString(&buf, ", ");
+		}
+
+		/* 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 string_to_text(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)
+{
+	PG_RETURN_TEXT_P(build_tablespace_ddl(get_tablespace_oid(NameStr(*(Name)PG_GETARG_NAME(0)),
+															 false)));
+}
+
+/*
+ * 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)
+{
+	PG_RETURN_TEXT_P(build_tablespace_ddl((Oid)PG_GETARG_OID(0)));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9121a382f76..bbc59e73537 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/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..c2fed08acf7 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..aca170992d6 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.47.3

From d6a213bcbf2c630f9000d14d2f23065d8017e6db Mon Sep 17 00:00:00 2001
From: Nishant Sharma <nishant.sharma@enterprisedb.com>
Date: Wed, 5 Nov 2025 16:19:26 +0530
Subject: [PATCH v5 1/2] Supporting changes for pg_get_tablespace_ddl function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Moving core logic of pg_tablespace_location() to new generic function
get_tablespace_loc_string()

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
---
 src/backend/utils/adt/misc.c      | 62 ++----------------------
 src/backend/utils/adt/ruleutils.c | 79 +++++++++++++++++++++++++++++++
 src/include/utils/ruleutils.h     |  2 +
 3 files changed, 85 insertions(+), 58 deletions(-)

diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index fa1cb675027..cb99d7435eb 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -315,66 +315,12 @@ Datum
 pg_tablespace_location(PG_FUNCTION_ARGS)
 {
 	Oid			tablespaceOid = PG_GETARG_OID(0);
-	char		sourcepath[MAXPGPATH];
-	char		targetpath[MAXPGPATH];
-	int			rllen;
-	struct stat st;
+	char	   *tablespaceLoc;
 
-	/*
-	 * It's useful to apply this function to pg_class.reltablespace, wherein
-	 * zero means "the database's default tablespace".  So, rather than
-	 * throwing an error for zero, we choose to assume that's what is meant.
-	 */
-	if (tablespaceOid == InvalidOid)
-		tablespaceOid = MyDatabaseTableSpace;
-
-	/*
-	 * Return empty string for the cluster's default tablespaces
-	 */
-	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
-		tablespaceOid == GLOBALTABLESPACE_OID)
-		PG_RETURN_TEXT_P(cstring_to_text(""));
-
-	/*
-	 * Find the location of the tablespace by reading the symbolic link that
-	 * is in pg_tblspc/<oid>.
-	 */
-	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
-
-	/*
-	 * Before reading the link, check if the source path is a link or a
-	 * junction point.  Note that a directory is possible for a tablespace
-	 * created with allow_in_place_tablespaces enabled.  If a directory is
-	 * found, a relative path to the data directory is returned.
-	 */
-	if (lstat(sourcepath, &st) < 0)
-	{
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not stat file \"%s\": %m",
-						sourcepath)));
-	}
-
-	if (!S_ISLNK(st.st_mode))
-		PG_RETURN_TEXT_P(cstring_to_text(sourcepath));
-
-	/*
-	 * In presence of a link or a junction point, return the path pointing to.
-	 */
-	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
-	if (rllen < 0)
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not read symbolic link \"%s\": %m",
-						sourcepath)));
-	if (rllen >= sizeof(targetpath))
-		ereport(ERROR,
-				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-				 errmsg("symbolic link \"%s\" target is too long",
-						sourcepath)));
-	targetpath[rllen] = '\0';
+	/* Get LOCATION string from its OID */
+	tablespaceLoc = get_tablespace_loc_string(tablespaceOid);
 
-	PG_RETURN_TEXT_P(cstring_to_text(targetpath));
+	PG_RETURN_TEXT_P(cstring_to_text(tablespaceLoc));
 }
 
 /*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 5398679cce2..976a8345ce4 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -18,6 +18,7 @@
 #include <ctype.h>
 #include <unistd.h>
 #include <fcntl.h>
+#include <sys/stat.h>
 
 #include "access/amapi.h"
 #include "access/htup_details.h"
@@ -13742,3 +13743,81 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf->data;
 }
+
+/*
+ * get_tablespace_loc_string - get location for a tablespace in string. This is
+ * internal version which helps pg_tablespace_location() and others.
+ */
+char *
+get_tablespace_loc_string(Oid tablespaceOid)
+{
+	char		sourcepath[MAXPGPATH] = { '\0' };
+	char		targetpath[MAXPGPATH] = { '\0' };
+	int			rllen;
+	struct stat st;
+	StringInfoData buf;
+
+	initStringInfo(&buf);
+	appendStringInfoString(&buf, "");
+
+	/*
+	 * It's useful to apply this function to pg_class.reltablespace, wherein
+	 * zero means "the database's default tablespace".  So, rather than
+	 * throwing an error for zero, we choose to assume that's what is meant.
+	 */
+	if (tablespaceOid == InvalidOid)
+		tablespaceOid = MyDatabaseTableSpace;
+
+	/*
+	 * Return empty string for the cluster's default tablespaces
+	 */
+	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
+		tablespaceOid == GLOBALTABLESPACE_OID)
+		return buf.data;
+
+	/*
+	 * Find the location of the tablespace by reading the symbolic link that
+	 * is in pg_tblspc/<oid>.
+	 */
+	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
+
+	/*
+	 * Before reading the link, check if the source path is a link or a
+	 * junction point.  Note that a directory is possible for a tablespace
+	 * created with allow_in_place_tablespaces enabled.  If a directory is
+	 * found, a relative path to the data directory is returned.
+	 */
+	if (lstat(sourcepath, &st) < 0)
+	{
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not stat file \"%s\": %m",
+						sourcepath)));
+	}
+
+	if (!S_ISLNK(st.st_mode))
+	{
+		appendStringInfoString(&buf, sourcepath);
+		return buf.data;
+	}
+
+	/*
+	 * In presence of a link or a junction point, return the path pointing to.
+	 */
+	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
+	if (rllen < 0)
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not read symbolic link \"%s\": %m",
+						sourcepath)));
+	if (rllen >= sizeof(targetpath))
+		ereport(ERROR,
+				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+				 errmsg("symbolic link \"%s\" target is too long",
+						sourcepath)));
+	targetpath[rllen] = '\0';
+
+	appendStringInfoString(&buf, targetpath);
+
+	return buf.data;
+}
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 7ba7d887914..6d74485f32a 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -54,4 +54,6 @@ extern char *get_range_partbound_string(List *bound_datums);
 
 extern char *pg_get_statisticsobjdef_string(Oid statextid);
 
+extern char *get_tablespace_loc_string(Oid tablespaceOid);
+
 #endif							/* RULEUTILS_H */
-- 
2.47.3

From 4e9bb52c032382a7793037823ff3c8d06ea9d74a Mon Sep 17 00:00:00 2001
From: Nishant Sharma <nishant.sharma@enterprisedb.com>
Date: Wed, 5 Nov 2025 16:31:56 +0530
Subject: [PATCH v5 2/2] 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/utils/adt/ruleutils.c        | 151 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |   6 +
 src/include/utils/ruleutils.h            |   1 +
 src/test/regress/expected/tablespace.out |  69 +++++++++++
 src/test/regress/sql/tablespace.sql      |  47 +++++++
 6 files changed, 319 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..79ef14c6b2f 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> )
+        <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/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 976a8345ce4..9dd13d19fd6 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -36,6 +36,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"
@@ -13821,3 +13822,153 @@ get_tablespace_loc_string(Oid tablespaceOid)
 
 	return buf.data;
 }
+
+
+/*
+ * build_tablespace_ddl_string - Build CREATE TABLESPACE statement for
+ * a tablespace from its OID. This is internal version which helps
+ * pg_get_tablespace_ddl_name() and pg_get_tablespace_ddl_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 %d 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));
+	}
+
+	/* Find tablespace directory path */
+	path = get_tablespace_loc_string(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'", 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 */
+			appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+			appendStringInfoString(&buf, ", ");
+		}
+
+		/* 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;
+}
+
+/*
+ * 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 34b7fddb0e7..ece0610b558 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/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 6d74485f32a..e709eafccfd 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -55,5 +55,6 @@ extern char *get_range_partbound_string(List *bound_datums);
 extern char *pg_get_statisticsobjdef_string(Oid statextid);
 
 extern char *get_tablespace_loc_string(Oid tablespaceOid);
+extern char *build_tablespace_ddl_string(const Oid tspaceoid);
 
 #endif							/* RULEUTILS_H */
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..c2fed08acf7 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..aca170992d6 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.47.3

From 11831687a0324aa88d13716ec7f051a80a42f8e1 Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Thu, 6 Nov 2025 14:03:02 -0600
Subject: [PATCH v6 1/2] Supporting changes for pg_get_tablespace_ddl function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Moving core logic of pg_tablespace_location() to new generic function
get_tablespace_loc_string()

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
---
 src/backend/utils/adt/misc.c      | 62 ++----------------------
 src/backend/utils/adt/ruleutils.c | 79 +++++++++++++++++++++++++++++++
 src/include/utils/ruleutils.h     |  2 +
 3 files changed, 85 insertions(+), 58 deletions(-)

diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index fa1cb675027..cb99d7435eb 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -315,66 +315,12 @@ Datum
 pg_tablespace_location(PG_FUNCTION_ARGS)
 {
 	Oid			tablespaceOid = PG_GETARG_OID(0);
-	char		sourcepath[MAXPGPATH];
-	char		targetpath[MAXPGPATH];
-	int			rllen;
-	struct stat st;
+	char	   *tablespaceLoc;
 
-	/*
-	 * It's useful to apply this function to pg_class.reltablespace, wherein
-	 * zero means "the database's default tablespace".  So, rather than
-	 * throwing an error for zero, we choose to assume that's what is meant.
-	 */
-	if (tablespaceOid == InvalidOid)
-		tablespaceOid = MyDatabaseTableSpace;
-
-	/*
-	 * Return empty string for the cluster's default tablespaces
-	 */
-	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
-		tablespaceOid == GLOBALTABLESPACE_OID)
-		PG_RETURN_TEXT_P(cstring_to_text(""));
-
-	/*
-	 * Find the location of the tablespace by reading the symbolic link that
-	 * is in pg_tblspc/<oid>.
-	 */
-	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
-
-	/*
-	 * Before reading the link, check if the source path is a link or a
-	 * junction point.  Note that a directory is possible for a tablespace
-	 * created with allow_in_place_tablespaces enabled.  If a directory is
-	 * found, a relative path to the data directory is returned.
-	 */
-	if (lstat(sourcepath, &st) < 0)
-	{
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not stat file \"%s\": %m",
-						sourcepath)));
-	}
-
-	if (!S_ISLNK(st.st_mode))
-		PG_RETURN_TEXT_P(cstring_to_text(sourcepath));
-
-	/*
-	 * In presence of a link or a junction point, return the path pointing to.
-	 */
-	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
-	if (rllen < 0)
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not read symbolic link \"%s\": %m",
-						sourcepath)));
-	if (rllen >= sizeof(targetpath))
-		ereport(ERROR,
-				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-				 errmsg("symbolic link \"%s\" target is too long",
-						sourcepath)));
-	targetpath[rllen] = '\0';
+	/* Get LOCATION string from its OID */
+	tablespaceLoc = get_tablespace_loc_string(tablespaceOid);
 
-	PG_RETURN_TEXT_P(cstring_to_text(targetpath));
+	PG_RETURN_TEXT_P(cstring_to_text(tablespaceLoc));
 }
 
 /*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..b15d85c5477 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -18,6 +18,7 @@
 #include <ctype.h>
 #include <unistd.h>
 #include <fcntl.h>
+#include <sys/stat.h>
 
 #include "access/amapi.h"
 #include "access/htup_details.h"
@@ -13743,3 +13744,81 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * get_tablespace_loc_string - get location for a tablespace in string. This is
+ * internal version which helps pg_tablespace_location() and others.
+ */
+char *
+get_tablespace_loc_string(Oid tablespaceOid)
+{
+	char		sourcepath[MAXPGPATH] = { '\0' };
+	char		targetpath[MAXPGPATH] = { '\0' };
+	int			rllen;
+	struct stat st;
+	StringInfoData buf;
+
+	initStringInfo(&buf);
+	appendStringInfoString(&buf, "");
+
+	/*
+	 * It's useful to apply this function to pg_class.reltablespace, wherein
+	 * zero means "the database's default tablespace".  So, rather than
+	 * throwing an error for zero, we choose to assume that's what is meant.
+	 */
+	if (tablespaceOid == InvalidOid)
+		tablespaceOid = MyDatabaseTableSpace;
+
+	/*
+	 * Return empty string for the cluster's default tablespaces
+	 */
+	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
+		tablespaceOid == GLOBALTABLESPACE_OID)
+		return buf.data;
+
+	/*
+	 * Find the location of the tablespace by reading the symbolic link that
+	 * is in pg_tblspc/<oid>.
+	 */
+	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
+
+	/*
+	 * Before reading the link, check if the source path is a link or a
+	 * junction point.  Note that a directory is possible for a tablespace
+	 * created with allow_in_place_tablespaces enabled.  If a directory is
+	 * found, a relative path to the data directory is returned.
+	 */
+	if (lstat(sourcepath, &st) < 0)
+	{
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not stat file \"%s\": %m",
+						sourcepath)));
+	}
+
+	if (!S_ISLNK(st.st_mode))
+	{
+		appendStringInfoString(&buf, sourcepath);
+		return buf.data;
+	}
+
+	/*
+	 * In presence of a link or a junction point, return the path pointing to.
+	 */
+	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
+	if (rllen < 0)
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not read symbolic link \"%s\": %m",
+						sourcepath)));
+	if (rllen >= sizeof(targetpath))
+		ereport(ERROR,
+				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+				 errmsg("symbolic link \"%s\" target is too long",
+						sourcepath)));
+	targetpath[rllen] = '\0';
+
+	appendStringInfoString(&buf, targetpath);
+
+	return buf.data;
+}
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 7ba7d887914..6d74485f32a 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -54,4 +54,6 @@ extern char *get_range_partbound_string(List *bound_datums);
 
 extern char *pg_get_statisticsobjdef_string(Oid statextid);
 
+extern char *get_tablespace_loc_string(Oid tablespaceOid);
+
 #endif							/* RULEUTILS_H */
-- 
2.51.2

From 1297f674c2f69aa0ec689fd65c61a45e466e9eae Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Thu, 6 Nov 2025 14:16:26 -0600
Subject: [PATCH v6 2/2] 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/utils/adt/ruleutils.c        | 151 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |   6 +
 src/include/utils/ruleutils.h            |   1 +
 src/test/regress/expected/tablespace.out |  69 +++++++++++
 src/test/regress/sql/tablespace.sql      |  47 +++++++
 6 files changed, 319 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..74ca0ed1066 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> )
+        <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/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index b15d85c5477..6d1aa3b20ab 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -36,6 +36,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"
@@ -13822,3 +13823,153 @@ get_tablespace_loc_string(Oid tablespaceOid)

 	return buf.data;
 }
+
+
+/*
+ * build_tablespace_ddl_string - Build CREATE TABLESPACE statement for
+ * a tablespace from its OID. This is internal version which helps
+ * pg_get_tablespace_ddl_name() and pg_get_tablespace_ddl_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 %d 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));
+	}
+
+	/* Find tablespace directory path */
+	path = get_tablespace_loc_string(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'", 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 */
+			appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+			appendStringInfoString(&buf, ", ");
+		}
+
+		/* 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;
+}
+
+/*
+ * 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 34b7fddb0e7..ece0610b558 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/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 6d74485f32a..e709eafccfd 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -55,5 +55,6 @@ extern char *get_range_partbound_string(List *bound_datums);
 extern char *pg_get_statisticsobjdef_string(Oid statextid);

 extern char *get_tablespace_loc_string(Oid tablespaceOid);
+extern char *build_tablespace_ddl_string(const Oid tspaceoid);

 #endif							/* RULEUTILS_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

From 84dabefcda2a5d7026280bdbaa91b3b44dd8784e Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Thu, 6 Nov 2025 19:19:50 -0600
Subject: [PATCH v7 2/2] 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/utils/adt/ruleutils.c        | 151 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |   6 +
 src/include/utils/ruleutils.h            |   1 +
 src/test/regress/expected/tablespace.out |  69 +++++++++++
 src/test/regress/sql/tablespace.sql      |  47 +++++++
 6 files changed, 319 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..74ca0ed1066 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> )
+        <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/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index b15d85c5477..6d1aa3b20ab 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -36,6 +36,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"
@@ -13822,3 +13823,153 @@ get_tablespace_loc_string(Oid tablespaceOid)
 
 	return buf.data;
 }
+
+
+/*
+ * build_tablespace_ddl_string - Build CREATE TABLESPACE statement for
+ * a tablespace from its OID. This is internal version which helps
+ * pg_get_tablespace_ddl_name() and pg_get_tablespace_ddl_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 %d 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));
+	}
+
+	/* Find tablespace directory path */
+	path = get_tablespace_loc_string(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'", 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 */
+			appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+			appendStringInfoString(&buf, ", ");
+		}
+
+		/* 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;
+}
+
+/*
+ * 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 34b7fddb0e7..ece0610b558 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/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 6d74485f32a..e709eafccfd 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -55,5 +55,6 @@ extern char *get_range_partbound_string(List *bound_datums);
 extern char *pg_get_statisticsobjdef_string(Oid statextid);
 
 extern char *get_tablespace_loc_string(Oid tablespaceOid);
+extern char *build_tablespace_ddl_string(const Oid tspaceoid);
 
 #endif							/* RULEUTILS_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

From 4df331b743cfac263e24105bcf97c93507f80d02 Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Thu, 6 Nov 2025 18:14:32 -0600
Subject: [PATCH v7 1/2] Supporting changes for pg_get_tablespace_ddl function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Moving core logic of pg_tablespace_location() to new generic function
get_tablespace_loc_string()

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
---
 src/backend/utils/adt/misc.c      | 62 ++----------------------
 src/backend/utils/adt/ruleutils.c | 79 +++++++++++++++++++++++++++++++
 src/include/utils/ruleutils.h     |  2 +
 3 files changed, 85 insertions(+), 58 deletions(-)

diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index fa1cb675027..cb99d7435eb 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -315,66 +315,12 @@ Datum
 pg_tablespace_location(PG_FUNCTION_ARGS)
 {
 	Oid			tablespaceOid = PG_GETARG_OID(0);
-	char		sourcepath[MAXPGPATH];
-	char		targetpath[MAXPGPATH];
-	int			rllen;
-	struct stat st;
+	char	   *tablespaceLoc;
 
-	/*
-	 * It's useful to apply this function to pg_class.reltablespace, wherein
-	 * zero means "the database's default tablespace".  So, rather than
-	 * throwing an error for zero, we choose to assume that's what is meant.
-	 */
-	if (tablespaceOid == InvalidOid)
-		tablespaceOid = MyDatabaseTableSpace;
-
-	/*
-	 * Return empty string for the cluster's default tablespaces
-	 */
-	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
-		tablespaceOid == GLOBALTABLESPACE_OID)
-		PG_RETURN_TEXT_P(cstring_to_text(""));
-
-	/*
-	 * Find the location of the tablespace by reading the symbolic link that
-	 * is in pg_tblspc/<oid>.
-	 */
-	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
-
-	/*
-	 * Before reading the link, check if the source path is a link or a
-	 * junction point.  Note that a directory is possible for a tablespace
-	 * created with allow_in_place_tablespaces enabled.  If a directory is
-	 * found, a relative path to the data directory is returned.
-	 */
-	if (lstat(sourcepath, &st) < 0)
-	{
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not stat file \"%s\": %m",
-						sourcepath)));
-	}
-
-	if (!S_ISLNK(st.st_mode))
-		PG_RETURN_TEXT_P(cstring_to_text(sourcepath));
-
-	/*
-	 * In presence of a link or a junction point, return the path pointing to.
-	 */
-	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
-	if (rllen < 0)
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not read symbolic link \"%s\": %m",
-						sourcepath)));
-	if (rllen >= sizeof(targetpath))
-		ereport(ERROR,
-				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-				 errmsg("symbolic link \"%s\" target is too long",
-						sourcepath)));
-	targetpath[rllen] = '\0';
+	/* Get LOCATION string from its OID */
+	tablespaceLoc = get_tablespace_loc_string(tablespaceOid);
 
-	PG_RETURN_TEXT_P(cstring_to_text(targetpath));
+	PG_RETURN_TEXT_P(cstring_to_text(tablespaceLoc));
 }
 
 /*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..b15d85c5477 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -18,6 +18,7 @@
 #include <ctype.h>
 #include <unistd.h>
 #include <fcntl.h>
+#include <sys/stat.h>
 
 #include "access/amapi.h"
 #include "access/htup_details.h"
@@ -13743,3 +13744,81 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * get_tablespace_loc_string - get location for a tablespace in string. This is
+ * internal version which helps pg_tablespace_location() and others.
+ */
+char *
+get_tablespace_loc_string(Oid tablespaceOid)
+{
+	char		sourcepath[MAXPGPATH] = { '\0' };
+	char		targetpath[MAXPGPATH] = { '\0' };
+	int			rllen;
+	struct stat st;
+	StringInfoData buf;
+
+	initStringInfo(&buf);
+	appendStringInfoString(&buf, "");
+
+	/*
+	 * It's useful to apply this function to pg_class.reltablespace, wherein
+	 * zero means "the database's default tablespace".  So, rather than
+	 * throwing an error for zero, we choose to assume that's what is meant.
+	 */
+	if (tablespaceOid == InvalidOid)
+		tablespaceOid = MyDatabaseTableSpace;
+
+	/*
+	 * Return empty string for the cluster's default tablespaces
+	 */
+	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
+		tablespaceOid == GLOBALTABLESPACE_OID)
+		return buf.data;
+
+	/*
+	 * Find the location of the tablespace by reading the symbolic link that
+	 * is in pg_tblspc/<oid>.
+	 */
+	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
+
+	/*
+	 * Before reading the link, check if the source path is a link or a
+	 * junction point.  Note that a directory is possible for a tablespace
+	 * created with allow_in_place_tablespaces enabled.  If a directory is
+	 * found, a relative path to the data directory is returned.
+	 */
+	if (lstat(sourcepath, &st) < 0)
+	{
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not stat file \"%s\": %m",
+						sourcepath)));
+	}
+
+	if (!S_ISLNK(st.st_mode))
+	{
+		appendStringInfoString(&buf, sourcepath);
+		return buf.data;
+	}
+
+	/*
+	 * In presence of a link or a junction point, return the path pointing to.
+	 */
+	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
+	if (rllen < 0)
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not read symbolic link \"%s\": %m",
+						sourcepath)));
+	if (rllen >= sizeof(targetpath))
+		ereport(ERROR,
+				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+				 errmsg("symbolic link \"%s\" target is too long",
+						sourcepath)));
+	targetpath[rllen] = '\0';
+
+	appendStringInfoString(&buf, targetpath);
+
+	return buf.data;
+}
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 7ba7d887914..6d74485f32a 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -54,4 +54,6 @@ extern char *get_range_partbound_string(List *bound_datums);
 
 extern char *pg_get_statisticsobjdef_string(Oid statextid);
 
+extern char *get_tablespace_loc_string(Oid tablespaceOid);
+
 #endif							/* RULEUTILS_H */
-- 
2.51.2

From 29e92da55e817c7938cf0be5bdda4bc2d420c7ca Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Fri, 7 Nov 2025 15:59:14 -0600
Subject: [PATCH v8 2/2] 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/utils/adt/ruleutils.c        | 153 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |   6 +
 src/test/regress/expected/tablespace.out |  69 ++++++++++
 src/test/regress/sql/tablespace.sql      |  47 +++++++
 5 files changed, 320 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..74ca0ed1066 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> )
+        <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/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index c8023e83f4b..31ddd2ea49a 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -36,6 +36,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"
@@ -13744,3 +13745,155 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * build_tablespace_ddl_string - Build CREATE TABLESPACE statement for
+ * a tablespace from its OID. This is internal version which helps
+ * pg_get_tablespace_ddl_name() and pg_get_tablespace_ddl_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 %d 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_loc_string(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'", 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 */
+			appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+			appendStringInfoString(&buf, ", ");
+		}
+
+		/* 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;
+}
+
+/*
+ * 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/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

From b3e23e59ff74f9def70416a65a4ebb59052c49bf Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Fri, 7 Nov 2025 15:12:18 -0600
Subject: [PATCH v8 1/2] Supporting changes for pg_get_tablespace_ddl function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Moving core logic of pg_tablespace_location() to new generic function
get_tablespace_loc_string()

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
---
 src/backend/catalog/Makefile        |   1 +
 src/backend/catalog/pg_tablespace.c | 151 ++++++++++++++++++++++++++++
 src/backend/utils/adt/misc.c        |  62 +-----------
 src/backend/utils/adt/ruleutils.c   |   1 +
 src/include/catalog/pg_tablespace.h |   2 +
 5 files changed, 159 insertions(+), 58 deletions(-)
 create mode 100644 src/backend/catalog/pg_tablespace.c

diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index c090094ed08..8e40e1b8189 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -44,6 +44,7 @@ OBJS = \
 	pg_range.o \
 	pg_shdepend.o \
 	pg_subscription.o \
+	pg_tablespace.o \
 	pg_type.o \
 	storage.o \
 	toasting.o
diff --git a/src/backend/catalog/pg_tablespace.c b/src/backend/catalog/pg_tablespace.c
new file mode 100644
index 00000000000..95f7c3bf6d0
--- /dev/null
+++ b/src/backend/catalog/pg_tablespace.c
@@ -0,0 +1,151 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_tablespace.c
+ *	  routines to support tablespaces
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ *	  src/backend/catalog/pg_tablespace.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <ctype.h>
+#include <unistd.h>
+#include <fcntl.h>
+#include <sys/stat.h>
+
+#include "access/amapi.h"
+#include "access/htup_details.h"
+#include "access/relation.h"
+#include "access/table.h"
+#include "catalog/pg_aggregate.h"
+#include "catalog/pg_am.h"
+#include "catalog/pg_authid.h"
+#include "catalog/pg_collation.h"
+#include "catalog/pg_constraint.h"
+#include "catalog/pg_depend.h"
+#include "catalog/pg_language.h"
+#include "catalog/pg_opclass.h"
+#include "catalog/pg_operator.h"
+#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"
+#include "commands/tablespace.h"
+#include "common/keywords.h"
+#include "executor/spi.h"
+#include "funcapi.h"
+#include "mb/pg_wchar.h"
+#include "miscadmin.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/pathnodes.h"
+#include "optimizer/optimizer.h"
+#include "parser/parse_agg.h"
+#include "parser/parse_func.h"
+#include "parser/parse_oper.h"
+#include "parser/parse_relation.h"
+#include "parser/parser.h"
+#include "parser/parsetree.h"
+#include "rewrite/rewriteHandler.h"
+#include "rewrite/rewriteManip.h"
+#include "rewrite/rewriteSupport.h"
+#include "utils/array.h"
+#include "utils/builtins.h"
+#include "utils/fmgroids.h"
+#include "utils/guc.h"
+#include "utils/hsearch.h"
+#include "utils/lsyscache.h"
+#include "utils/partcache.h"
+#include "utils/rel.h"
+#include "utils/ruleutils.h"
+#include "utils/snapmgr.h"
+#include "utils/syscache.h"
+#include "utils/typcache.h"
+#include "utils/varlena.h"
+#include "utils/xml.h"
+
+/*
+ * get_tablespace_loc_string - Get a tablespace's location as a C-string.
+ */
+char *
+get_tablespace_loc_string(Oid tablespaceOid)
+{
+	char		sourcepath[MAXPGPATH] = {'\0'};
+	char		targetpath[MAXPGPATH] = {'\0'};
+	int			rllen;
+	struct stat st;
+	StringInfoData buf;
+
+	initStringInfo(&buf);
+	appendStringInfoString(&buf, "");
+
+	/*
+	 * It's useful to apply this function to pg_class.reltablespace, wherein
+	 * zero means "the database's default tablespace".  So, rather than
+	 * throwing an error for zero, we choose to assume that's what is meant.
+	 */
+	if (tablespaceOid == InvalidOid)
+		tablespaceOid = MyDatabaseTableSpace;
+
+	/*
+	 * Return empty string for the cluster's default tablespaces
+	 */
+	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
+		tablespaceOid == GLOBALTABLESPACE_OID)
+		return buf.data;
+
+	/*
+	 * Find the location of the tablespace by reading the symbolic link that
+	 * is in pg_tblspc/<oid>.
+	 */
+	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
+
+	/*
+	 * Before reading the link, check if the source path is a link or a
+	 * junction point.  Note that a directory is possible for a tablespace
+	 * created with allow_in_place_tablespaces enabled.  If a directory is
+	 * found, a relative path to the data directory is returned.
+	 */
+	if (lstat(sourcepath, &st) < 0)
+	{
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not stat file \"%s\": %m",
+						sourcepath)));
+	}
+
+	if (!S_ISLNK(st.st_mode))
+	{
+		appendStringInfoString(&buf, sourcepath);
+		return buf.data;
+	}
+
+	/*
+	 * In presence of a link or a junction point, return the path pointing to.
+	 */
+	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
+	if (rllen < 0)
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not read symbolic link \"%s\": %m",
+						sourcepath)));
+	if (rllen >= sizeof(targetpath))
+		ereport(ERROR,
+				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+				 errmsg("symbolic link \"%s\" target is too long",
+						sourcepath)));
+	targetpath[rllen] = '\0';
+
+	appendStringInfoString(&buf, targetpath);
+
+	return buf.data;
+}
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index fa1cb675027..cb99d7435eb 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -315,66 +315,12 @@ Datum
 pg_tablespace_location(PG_FUNCTION_ARGS)
 {
 	Oid			tablespaceOid = PG_GETARG_OID(0);
-	char		sourcepath[MAXPGPATH];
-	char		targetpath[MAXPGPATH];
-	int			rllen;
-	struct stat st;
+	char	   *tablespaceLoc;
 
-	/*
-	 * It's useful to apply this function to pg_class.reltablespace, wherein
-	 * zero means "the database's default tablespace".  So, rather than
-	 * throwing an error for zero, we choose to assume that's what is meant.
-	 */
-	if (tablespaceOid == InvalidOid)
-		tablespaceOid = MyDatabaseTableSpace;
-
-	/*
-	 * Return empty string for the cluster's default tablespaces
-	 */
-	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
-		tablespaceOid == GLOBALTABLESPACE_OID)
-		PG_RETURN_TEXT_P(cstring_to_text(""));
-
-	/*
-	 * Find the location of the tablespace by reading the symbolic link that
-	 * is in pg_tblspc/<oid>.
-	 */
-	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
-
-	/*
-	 * Before reading the link, check if the source path is a link or a
-	 * junction point.  Note that a directory is possible for a tablespace
-	 * created with allow_in_place_tablespaces enabled.  If a directory is
-	 * found, a relative path to the data directory is returned.
-	 */
-	if (lstat(sourcepath, &st) < 0)
-	{
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not stat file \"%s\": %m",
-						sourcepath)));
-	}
-
-	if (!S_ISLNK(st.st_mode))
-		PG_RETURN_TEXT_P(cstring_to_text(sourcepath));
-
-	/*
-	 * In presence of a link or a junction point, return the path pointing to.
-	 */
-	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
-	if (rllen < 0)
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not read symbolic link \"%s\": %m",
-						sourcepath)));
-	if (rllen >= sizeof(targetpath))
-		ereport(ERROR,
-				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-				 errmsg("symbolic link \"%s\" target is too long",
-						sourcepath)));
-	targetpath[rllen] = '\0';
+	/* Get LOCATION string from its OID */
+	tablespaceLoc = get_tablespace_loc_string(tablespaceOid);
 
-	PG_RETURN_TEXT_P(cstring_to_text(targetpath));
+	PG_RETURN_TEXT_P(cstring_to_text(tablespaceLoc));
 }
 
 /*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..c8023e83f4b 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -18,6 +18,7 @@
 #include <ctype.h>
 #include <unistd.h>
 #include <fcntl.h>
+#include <sys/stat.h>
 
 #include "access/amapi.h"
 #include "access/htup_details.h"
diff --git a/src/include/catalog/pg_tablespace.h b/src/include/catalog/pg_tablespace.h
index 5293488c630..f065cff9ddc 100644
--- a/src/include/catalog/pg_tablespace.h
+++ b/src/include/catalog/pg_tablespace.h
@@ -54,4 +54,6 @@ DECLARE_UNIQUE_INDEX(pg_tablespace_spcname_index, 2698, TablespaceNameIndexId, p
 
 MAKE_SYSCACHE(TABLESPACEOID, pg_tablespace_oid_index, 4);
 
+extern char *get_tablespace_loc_string(Oid tablespaceOid);
+
 #endif							/* PG_TABLESPACE_H */
-- 
2.51.2

From e5eccab287ca461ad943977dcc9d5bb437957866 Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Fri, 7 Nov 2025 15:59:14 -0600
Subject: [PATCH v9 2/2] 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/utils/adt/ruleutils.c        | 154 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat          |   6 +
 src/test/regress/expected/tablespace.out |  69 ++++++++++
 src/test/regress/sql/tablespace.sql      |  47 +++++++
 5 files changed, 321 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..74ca0ed1066 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> )
+        <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/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index c8023e83f4b..53b89fc2b03 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -36,6 +36,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"
@@ -547,6 +548,7 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
+static char *build_tablespace_ddl_string(const Oid tspaceoid);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -13744,3 +13746,155 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * build_tablespace_ddl_string - Build CREATE TABLESPACE statement for
+ * a tablespace from its OID. This is internal version which helps
+ * pg_get_tablespace_ddl_name() and pg_get_tablespace_ddl_oid().
+ */
+static 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 %d 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_loc_string(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'", 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 */
+			appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+			appendStringInfoString(&buf, ", ");
+		}
+
+		/* 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;
+}
+
+/*
+ * 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/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

From b3e23e59ff74f9def70416a65a4ebb59052c49bf Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Fri, 7 Nov 2025 15:12:18 -0600
Subject: [PATCH v9 1/2] Supporting changes for pg_get_tablespace_ddl function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Moving core logic of pg_tablespace_location() to new generic function
get_tablespace_loc_string()

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
---
 src/backend/catalog/Makefile        |   1 +
 src/backend/catalog/pg_tablespace.c | 151 ++++++++++++++++++++++++++++
 src/backend/utils/adt/misc.c        |  62 +-----------
 src/backend/utils/adt/ruleutils.c   |   1 +
 src/include/catalog/pg_tablespace.h |   2 +
 5 files changed, 159 insertions(+), 58 deletions(-)
 create mode 100644 src/backend/catalog/pg_tablespace.c

diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index c090094ed08..8e40e1b8189 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -44,6 +44,7 @@ OBJS = \
 	pg_range.o \
 	pg_shdepend.o \
 	pg_subscription.o \
+	pg_tablespace.o \
 	pg_type.o \
 	storage.o \
 	toasting.o
diff --git a/src/backend/catalog/pg_tablespace.c b/src/backend/catalog/pg_tablespace.c
new file mode 100644
index 00000000000..95f7c3bf6d0
--- /dev/null
+++ b/src/backend/catalog/pg_tablespace.c
@@ -0,0 +1,151 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_tablespace.c
+ *	  routines to support tablespaces
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ *	  src/backend/catalog/pg_tablespace.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <ctype.h>
+#include <unistd.h>
+#include <fcntl.h>
+#include <sys/stat.h>
+
+#include "access/amapi.h"
+#include "access/htup_details.h"
+#include "access/relation.h"
+#include "access/table.h"
+#include "catalog/pg_aggregate.h"
+#include "catalog/pg_am.h"
+#include "catalog/pg_authid.h"
+#include "catalog/pg_collation.h"
+#include "catalog/pg_constraint.h"
+#include "catalog/pg_depend.h"
+#include "catalog/pg_language.h"
+#include "catalog/pg_opclass.h"
+#include "catalog/pg_operator.h"
+#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"
+#include "commands/tablespace.h"
+#include "common/keywords.h"
+#include "executor/spi.h"
+#include "funcapi.h"
+#include "mb/pg_wchar.h"
+#include "miscadmin.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/pathnodes.h"
+#include "optimizer/optimizer.h"
+#include "parser/parse_agg.h"
+#include "parser/parse_func.h"
+#include "parser/parse_oper.h"
+#include "parser/parse_relation.h"
+#include "parser/parser.h"
+#include "parser/parsetree.h"
+#include "rewrite/rewriteHandler.h"
+#include "rewrite/rewriteManip.h"
+#include "rewrite/rewriteSupport.h"
+#include "utils/array.h"
+#include "utils/builtins.h"
+#include "utils/fmgroids.h"
+#include "utils/guc.h"
+#include "utils/hsearch.h"
+#include "utils/lsyscache.h"
+#include "utils/partcache.h"
+#include "utils/rel.h"
+#include "utils/ruleutils.h"
+#include "utils/snapmgr.h"
+#include "utils/syscache.h"
+#include "utils/typcache.h"
+#include "utils/varlena.h"
+#include "utils/xml.h"
+
+/*
+ * get_tablespace_loc_string - Get a tablespace's location as a C-string.
+ */
+char *
+get_tablespace_loc_string(Oid tablespaceOid)
+{
+	char		sourcepath[MAXPGPATH] = {'\0'};
+	char		targetpath[MAXPGPATH] = {'\0'};
+	int			rllen;
+	struct stat st;
+	StringInfoData buf;
+
+	initStringInfo(&buf);
+	appendStringInfoString(&buf, "");
+
+	/*
+	 * It's useful to apply this function to pg_class.reltablespace, wherein
+	 * zero means "the database's default tablespace".  So, rather than
+	 * throwing an error for zero, we choose to assume that's what is meant.
+	 */
+	if (tablespaceOid == InvalidOid)
+		tablespaceOid = MyDatabaseTableSpace;
+
+	/*
+	 * Return empty string for the cluster's default tablespaces
+	 */
+	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
+		tablespaceOid == GLOBALTABLESPACE_OID)
+		return buf.data;
+
+	/*
+	 * Find the location of the tablespace by reading the symbolic link that
+	 * is in pg_tblspc/<oid>.
+	 */
+	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
+
+	/*
+	 * Before reading the link, check if the source path is a link or a
+	 * junction point.  Note that a directory is possible for a tablespace
+	 * created with allow_in_place_tablespaces enabled.  If a directory is
+	 * found, a relative path to the data directory is returned.
+	 */
+	if (lstat(sourcepath, &st) < 0)
+	{
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not stat file \"%s\": %m",
+						sourcepath)));
+	}
+
+	if (!S_ISLNK(st.st_mode))
+	{
+		appendStringInfoString(&buf, sourcepath);
+		return buf.data;
+	}
+
+	/*
+	 * In presence of a link or a junction point, return the path pointing to.
+	 */
+	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
+	if (rllen < 0)
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not read symbolic link \"%s\": %m",
+						sourcepath)));
+	if (rllen >= sizeof(targetpath))
+		ereport(ERROR,
+				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+				 errmsg("symbolic link \"%s\" target is too long",
+						sourcepath)));
+	targetpath[rllen] = '\0';
+
+	appendStringInfoString(&buf, targetpath);
+
+	return buf.data;
+}
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index fa1cb675027..cb99d7435eb 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -315,66 +315,12 @@ Datum
 pg_tablespace_location(PG_FUNCTION_ARGS)
 {
 	Oid			tablespaceOid = PG_GETARG_OID(0);
-	char		sourcepath[MAXPGPATH];
-	char		targetpath[MAXPGPATH];
-	int			rllen;
-	struct stat st;
+	char	   *tablespaceLoc;
 
-	/*
-	 * It's useful to apply this function to pg_class.reltablespace, wherein
-	 * zero means "the database's default tablespace".  So, rather than
-	 * throwing an error for zero, we choose to assume that's what is meant.
-	 */
-	if (tablespaceOid == InvalidOid)
-		tablespaceOid = MyDatabaseTableSpace;
-
-	/*
-	 * Return empty string for the cluster's default tablespaces
-	 */
-	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
-		tablespaceOid == GLOBALTABLESPACE_OID)
-		PG_RETURN_TEXT_P(cstring_to_text(""));
-
-	/*
-	 * Find the location of the tablespace by reading the symbolic link that
-	 * is in pg_tblspc/<oid>.
-	 */
-	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
-
-	/*
-	 * Before reading the link, check if the source path is a link or a
-	 * junction point.  Note that a directory is possible for a tablespace
-	 * created with allow_in_place_tablespaces enabled.  If a directory is
-	 * found, a relative path to the data directory is returned.
-	 */
-	if (lstat(sourcepath, &st) < 0)
-	{
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not stat file \"%s\": %m",
-						sourcepath)));
-	}
-
-	if (!S_ISLNK(st.st_mode))
-		PG_RETURN_TEXT_P(cstring_to_text(sourcepath));
-
-	/*
-	 * In presence of a link or a junction point, return the path pointing to.
-	 */
-	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
-	if (rllen < 0)
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not read symbolic link \"%s\": %m",
-						sourcepath)));
-	if (rllen >= sizeof(targetpath))
-		ereport(ERROR,
-				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-				 errmsg("symbolic link \"%s\" target is too long",
-						sourcepath)));
-	targetpath[rllen] = '\0';
+	/* Get LOCATION string from its OID */
+	tablespaceLoc = get_tablespace_loc_string(tablespaceOid);
 
-	PG_RETURN_TEXT_P(cstring_to_text(targetpath));
+	PG_RETURN_TEXT_P(cstring_to_text(tablespaceLoc));
 }
 
 /*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..c8023e83f4b 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -18,6 +18,7 @@
 #include <ctype.h>
 #include <unistd.h>
 #include <fcntl.h>
+#include <sys/stat.h>
 
 #include "access/amapi.h"
 #include "access/htup_details.h"
diff --git a/src/include/catalog/pg_tablespace.h b/src/include/catalog/pg_tablespace.h
index 5293488c630..f065cff9ddc 100644
--- a/src/include/catalog/pg_tablespace.h
+++ b/src/include/catalog/pg_tablespace.h
@@ -54,4 +54,6 @@ DECLARE_UNIQUE_INDEX(pg_tablespace_spcname_index, 2698, TablespaceNameIndexId, p
 
 MAKE_SYSCACHE(TABLESPACEOID, pg_tablespace_oid_index, 4);
 
+extern char *get_tablespace_loc_string(Oid tablespaceOid);
+
 #endif							/* PG_TABLESPACE_H */
-- 
2.51.2

From 30c24eef357966c929c00465465e3e111aba9285 Mon Sep 17 00:00:00 2001
From: Nishant Sharma <nishant.sharma@enterprisedb.com>
Date: Mon, 10 Nov 2025 10:54:34 +0530
Subject: [PATCH v10 2/2] 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      | 119 +++++++++++++++++++++++
 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, 324 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 c00888456c8..c147c0bc575 100644
--- a/src/backend/catalog/pg_tablespace.c
+++ b/src/backend/catalog/pg_tablespace.c
@@ -16,9 +16,13 @@
 
 #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"
 
 
 /*
@@ -98,3 +102,118 @@ get_tablespace_loc_string(Oid tablespaceOid)
 
 	return buf.data;
 }
+
+/*
+ * 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 %d 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_loc_string(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'", 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 */
+			appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+			appendStringInfoString(&buf, ", ");
+		}
+
+		/* 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 f065cff9ddc..c78dcc91b02 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_loc_string(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.47.3

From ca63d247d031e740a58f73642ce2c1806c43b18d Mon Sep 17 00:00:00 2001
From: Nishant Sharma <nishant.sharma@enterprisedb.com>
Date: Mon, 10 Nov 2025 11:22:00 +0530
Subject: [PATCH v10 1/2] Supporting changes for pg_get_tablespace_ddl function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Moving core logic of pg_tablespace_location() to new generic function
get_tablespace_loc_string()

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
---
 src/backend/catalog/Makefile        |   1 +
 src/backend/catalog/meson.build     |   1 +
 src/backend/catalog/pg_tablespace.c | 100 ++++++++++++++++++++++++++++
 src/backend/utils/adt/misc.c        |  62 ++---------------
 src/include/catalog/pg_tablespace.h |   2 +
 5 files changed, 108 insertions(+), 58 deletions(-)
 create mode 100644 src/backend/catalog/pg_tablespace.c

diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index c090094ed08..8e40e1b8189 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -44,6 +44,7 @@ OBJS = \
 	pg_range.o \
 	pg_shdepend.o \
 	pg_subscription.o \
+	pg_tablespace.o \
 	pg_type.o \
 	storage.o \
 	toasting.o
diff --git a/src/backend/catalog/meson.build b/src/backend/catalog/meson.build
index 1958ea9238a..58674ffeee6 100644
--- a/src/backend/catalog/meson.build
+++ b/src/backend/catalog/meson.build
@@ -31,6 +31,7 @@ backend_sources += files(
   'pg_range.c',
   'pg_shdepend.c',
   'pg_subscription.c',
+  'pg_tablespace.c',
   'pg_type.c',
   'storage.c',
   'toasting.c',
diff --git a/src/backend/catalog/pg_tablespace.c b/src/backend/catalog/pg_tablespace.c
new file mode 100644
index 00000000000..c00888456c8
--- /dev/null
+++ b/src/backend/catalog/pg_tablespace.c
@@ -0,0 +1,100 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_tablespace.c
+ *	  routines to support tablespaces
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ *	  src/backend/catalog/pg_tablespace.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <sys/stat.h>
+
+#include "catalog/pg_tablespace.h"
+#include "commands/tablespace.h"
+#include "miscadmin.h"
+
+
+/*
+ * get_tablespace_loc_string - Get a tablespace's location as a C-string for a
+ * tablespace from its OID.
+ */
+char *
+get_tablespace_loc_string(Oid tablespaceOid)
+{
+	char		sourcepath[MAXPGPATH] = {'\0'};
+	char		targetpath[MAXPGPATH] = {'\0'};
+	int			rllen;
+	struct stat st;
+	StringInfoData buf;
+
+	initStringInfo(&buf);
+	appendStringInfoString(&buf, "");
+
+	/*
+	 * It's useful to apply this function to pg_class.reltablespace, wherein
+	 * zero means "the database's default tablespace".  So, rather than
+	 * throwing an error for zero, we choose to assume that's what is meant.
+	 */
+	if (tablespaceOid == InvalidOid)
+		tablespaceOid = MyDatabaseTableSpace;
+
+	/*
+	 * Return empty string for the cluster's default tablespaces
+	 */
+	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
+		tablespaceOid == GLOBALTABLESPACE_OID)
+		return buf.data;
+
+	/*
+	 * Find the location of the tablespace by reading the symbolic link that
+	 * is in pg_tblspc/<oid>.
+	 */
+	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
+
+	/*
+	 * Before reading the link, check if the source path is a link or a
+	 * junction point.  Note that a directory is possible for a tablespace
+	 * created with allow_in_place_tablespaces enabled.  If a directory is
+	 * found, a relative path to the data directory is returned.
+	 */
+	if (lstat(sourcepath, &st) < 0)
+	{
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not stat file \"%s\": %m",
+						sourcepath)));
+	}
+
+	if (!S_ISLNK(st.st_mode))
+	{
+		appendStringInfoString(&buf, sourcepath);
+		return buf.data;
+	}
+
+	/*
+	 * In presence of a link or a junction point, return the path pointing to.
+	 */
+	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
+	if (rllen < 0)
+		ereport(ERROR,
+				(errcode_for_file_access(),
+				 errmsg("could not read symbolic link \"%s\": %m",
+						sourcepath)));
+	if (rllen >= sizeof(targetpath))
+		ereport(ERROR,
+				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+				 errmsg("symbolic link \"%s\" target is too long",
+						sourcepath)));
+	targetpath[rllen] = '\0';
+
+	appendStringInfoString(&buf, targetpath);
+
+	return buf.data;
+}
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index fa1cb675027..cb99d7435eb 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -315,66 +315,12 @@ Datum
 pg_tablespace_location(PG_FUNCTION_ARGS)
 {
 	Oid			tablespaceOid = PG_GETARG_OID(0);
-	char		sourcepath[MAXPGPATH];
-	char		targetpath[MAXPGPATH];
-	int			rllen;
-	struct stat st;
+	char	   *tablespaceLoc;
 
-	/*
-	 * It's useful to apply this function to pg_class.reltablespace, wherein
-	 * zero means "the database's default tablespace".  So, rather than
-	 * throwing an error for zero, we choose to assume that's what is meant.
-	 */
-	if (tablespaceOid == InvalidOid)
-		tablespaceOid = MyDatabaseTableSpace;
-
-	/*
-	 * Return empty string for the cluster's default tablespaces
-	 */
-	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
-		tablespaceOid == GLOBALTABLESPACE_OID)
-		PG_RETURN_TEXT_P(cstring_to_text(""));
-
-	/*
-	 * Find the location of the tablespace by reading the symbolic link that
-	 * is in pg_tblspc/<oid>.
-	 */
-	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
-
-	/*
-	 * Before reading the link, check if the source path is a link or a
-	 * junction point.  Note that a directory is possible for a tablespace
-	 * created with allow_in_place_tablespaces enabled.  If a directory is
-	 * found, a relative path to the data directory is returned.
-	 */
-	if (lstat(sourcepath, &st) < 0)
-	{
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not stat file \"%s\": %m",
-						sourcepath)));
-	}
-
-	if (!S_ISLNK(st.st_mode))
-		PG_RETURN_TEXT_P(cstring_to_text(sourcepath));
-
-	/*
-	 * In presence of a link or a junction point, return the path pointing to.
-	 */
-	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
-	if (rllen < 0)
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not read symbolic link \"%s\": %m",
-						sourcepath)));
-	if (rllen >= sizeof(targetpath))
-		ereport(ERROR,
-				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-				 errmsg("symbolic link \"%s\" target is too long",
-						sourcepath)));
-	targetpath[rllen] = '\0';
+	/* Get LOCATION string from its OID */
+	tablespaceLoc = get_tablespace_loc_string(tablespaceOid);
 
-	PG_RETURN_TEXT_P(cstring_to_text(targetpath));
+	PG_RETURN_TEXT_P(cstring_to_text(tablespaceLoc));
 }
 
 /*
diff --git a/src/include/catalog/pg_tablespace.h b/src/include/catalog/pg_tablespace.h
index 5293488c630..f065cff9ddc 100644
--- a/src/include/catalog/pg_tablespace.h
+++ b/src/include/catalog/pg_tablespace.h
@@ -54,4 +54,6 @@ DECLARE_UNIQUE_INDEX(pg_tablespace_spcname_index, 2698, TablespaceNameIndexId, p
 
 MAKE_SYSCACHE(TABLESPACEOID, pg_tablespace_oid_index, 4);
 
+extern char *get_tablespace_loc_string(Oid tablespaceOid);
+
 #endif							/* PG_TABLESPACE_H */
-- 
2.47.3

commit 08f168ab38d635f9e33480b506f3a0d667f80244
Author:     Álvaro Herrera <alvherre@kurilemu.de> [Álvaro Herrera <alvherre@kurilemu.de>]
AuthorDate: Tue Nov 11 15:48:45 2025 +0100
CommitDate: Tue Nov 11 15:48:45 2025 +0100

    []review changes

diff --git a/src/backend/catalog/pg_tablespace.c b/src/backend/catalog/pg_tablespace.c
index c00888456c8..d23aa7ae274 100644
--- a/src/backend/catalog/pg_tablespace.c
+++ b/src/backend/catalog/pg_tablespace.c
@@ -3,7 +3,7 @@
  * pg_tablespace.c
  *	  routines to support tablespaces
  *
- * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 2011-2025, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  *
@@ -14,6 +14,7 @@
  */
 #include "postgres.h"
 
+#include <unistd.h>
 #include <sys/stat.h>
 
 #include "catalog/pg_tablespace.h"
@@ -22,25 +23,21 @@
 
 
 /*
- * get_tablespace_loc_string - Get a tablespace's location as a C-string for a
- * tablespace from its OID.
+ * get_tablespace_location
+ *		Get a tablespace's location as a C-string, by its OID
  */
 char *
-get_tablespace_loc_string(Oid tablespaceOid)
+get_tablespace_location(Oid tablespaceOid)
 {
-	char		sourcepath[MAXPGPATH] = {'\0'};
-	char		targetpath[MAXPGPATH] = {'\0'};
+	char		sourcepath[MAXPGPATH];
+	char		targetpath[MAXPGPATH];
 	int			rllen;
 	struct stat st;
-	StringInfoData buf;
-
-	initStringInfo(&buf);
-	appendStringInfoString(&buf, "");
 
 	/*
-	 * It's useful to apply this function to pg_class.reltablespace, wherein
-	 * zero means "the database's default tablespace".  So, rather than
-	 * throwing an error for zero, we choose to assume that's what is meant.
+	 * It's useful to apply this to pg_class.reltablespace, wherein zero means
+	 * "the database's default tablespace".  So, rather than throwing an error
+	 * for zero, we choose to assume that's what is meant.
 	 */
 	if (tablespaceOid == InvalidOid)
 		tablespaceOid = MyDatabaseTableSpace;
@@ -50,7 +47,7 @@ get_tablespace_loc_string(Oid tablespaceOid)
 	 */
 	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
 		tablespaceOid == GLOBALTABLESPACE_OID)
-		return buf.data;
+		return pstrdup("");
 
 	/*
 	 * Find the location of the tablespace by reading the symbolic link that
@@ -65,36 +62,29 @@ get_tablespace_loc_string(Oid tablespaceOid)
 	 * found, a relative path to the data directory is returned.
 	 */
 	if (lstat(sourcepath, &st) < 0)
-	{
 		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not stat file \"%s\": %m",
-						sourcepath)));
-	}
+				errcode_for_file_access(),
+				errmsg("could not stat file \"%s\": %m",
+					   sourcepath));
 
 	if (!S_ISLNK(st.st_mode))
-	{
-		appendStringInfoString(&buf, sourcepath);
-		return buf.data;
-	}
+		return pstrdup(sourcepath);
 
 	/*
-	 * In presence of a link or a junction point, return the path pointing to.
+	 * In presence of a link or a junction point, return the path pointed to.
 	 */
 	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
 	if (rllen < 0)
 		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not read symbolic link \"%s\": %m",
-						sourcepath)));
+				errcode_for_file_access(),
+				errmsg("could not read symbolic link \"%s\": %m",
+					   sourcepath));
 	if (rllen >= sizeof(targetpath))
 		ereport(ERROR,
-				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-				 errmsg("symbolic link \"%s\" target is too long",
-						sourcepath)));
+				errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+				errmsg("symbolic link \"%s\" target is too long",
+					   sourcepath));
 	targetpath[rllen] = '\0';
 
-	appendStringInfoString(&buf, targetpath);
-
-	return buf.data;
+	return pstrdup(targetpath);
 }
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index cb99d7435eb..a365c432d34 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -318,7 +318,7 @@ pg_tablespace_location(PG_FUNCTION_ARGS)
 	char	   *tablespaceLoc;
 
 	/* Get LOCATION string from its OID */
-	tablespaceLoc = get_tablespace_loc_string(tablespaceOid);
+	tablespaceLoc = get_tablespace_location(tablespaceOid);
 
 	PG_RETURN_TEXT_P(cstring_to_text(tablespaceLoc));
 }
diff --git a/src/include/catalog/pg_tablespace.h b/src/include/catalog/pg_tablespace.h
index f065cff9ddc..7816d779d8c 100644
--- a/src/include/catalog/pg_tablespace.h
+++ b/src/include/catalog/pg_tablespace.h
@@ -54,6 +54,6 @@ DECLARE_UNIQUE_INDEX(pg_tablespace_spcname_index, 2698, TablespaceNameIndexId, p
 
 MAKE_SYSCACHE(TABLESPACEOID, pg_tablespace_oid_index, 4);
 
-extern char *get_tablespace_loc_string(Oid tablespaceOid);
+extern char *get_tablespace_location(Oid tablespaceOid);
 
 #endif							/* PG_TABLESPACE_H */

From efe8c1ab7273a38380c45a5260f7c663b10e6a25 Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Tue, 11 Nov 2025 18:34:12 -0600
Subject: [PATCH v11 2/2] 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      | 119 +++++++++++++++++++++++
 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, 324 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 c205b883502..964184aefc5 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,118 @@ 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 %d 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'", 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 */
+			appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+			appendStringInfoString(&buf, ", ");
+		}
+
+		/* 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.47.3

From 40ee6ffe76b987c52a2affda41ba73f6217c0355 Mon Sep 17 00:00:00 2001
From: Nishant Sharma <nishant.sharma@enterprisedb.com>
Date: Wed, 12 Nov 2025 14:24:49 +0530
Subject: [PATCH v11 1/2] Supporting changes for pg_get_tablespace_ddl function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Moving core logic of pg_tablespace_location() to new generic function
get_tablespace_loc_string()

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
---
 src/backend/catalog/Makefile        |  1 +
 src/backend/catalog/meson.build     |  1 +
 src/backend/catalog/pg_tablespace.c | 90 +++++++++++++++++++++++++++++
 src/backend/utils/adt/misc.c        | 62 ++------------------
 src/include/catalog/pg_tablespace.h |  2 +
 5 files changed, 98 insertions(+), 58 deletions(-)
 create mode 100644 src/backend/catalog/pg_tablespace.c

diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index c090094ed08..8e40e1b8189 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -44,6 +44,7 @@ OBJS = \
 	pg_range.o \
 	pg_shdepend.o \
 	pg_subscription.o \
+	pg_tablespace.o \
 	pg_type.o \
 	storage.o \
 	toasting.o
diff --git a/src/backend/catalog/meson.build b/src/backend/catalog/meson.build
index 1958ea9238a..58674ffeee6 100644
--- a/src/backend/catalog/meson.build
+++ b/src/backend/catalog/meson.build
@@ -31,6 +31,7 @@ backend_sources += files(
   'pg_range.c',
   'pg_shdepend.c',
   'pg_subscription.c',
+  'pg_tablespace.c',
   'pg_type.c',
   'storage.c',
   'toasting.c',
diff --git a/src/backend/catalog/pg_tablespace.c b/src/backend/catalog/pg_tablespace.c
new file mode 100644
index 00000000000..c205b883502
--- /dev/null
+++ b/src/backend/catalog/pg_tablespace.c
@@ -0,0 +1,90 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_tablespace.c
+ *	  routines to support tablespaces
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ *	  src/backend/catalog/pg_tablespace.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <unistd.h>
+#include <sys/stat.h>
+
+#include "catalog/pg_tablespace.h"
+#include "commands/tablespace.h"
+#include "miscadmin.h"
+
+
+/*
+ * get_tablespace_location
+ *		Get a tablespace's location as a C-string, by its OID
+ */
+char *
+get_tablespace_location(Oid tablespaceOid)
+{
+	char		sourcepath[MAXPGPATH];
+	char		targetpath[MAXPGPATH];
+	int			rllen;
+	struct stat st;
+
+	/*
+	 * It's useful to apply this to pg_class.reltablespace, wherein zero means
+	 * "the database's default tablespace".  So, rather than throwing an error
+	 * for zero, we choose to assume that's what is meant.
+	 */
+	if (tablespaceOid == InvalidOid)
+		tablespaceOid = MyDatabaseTableSpace;
+
+	/*
+	 * Return empty string for the cluster's default tablespaces
+	 */
+	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
+		tablespaceOid == GLOBALTABLESPACE_OID)
+		return pstrdup("");
+
+	/*
+	 * Find the location of the tablespace by reading the symbolic link that
+	 * is in pg_tblspc/<oid>.
+	 */
+	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
+
+	/*
+	 * Before reading the link, check if the source path is a link or a
+	 * junction point.  Note that a directory is possible for a tablespace
+	 * created with allow_in_place_tablespaces enabled.  If a directory is
+	 * found, a relative path to the data directory is returned.
+	 */
+	if (lstat(sourcepath, &st) < 0)
+		ereport(ERROR,
+				errcode_for_file_access(),
+				errmsg("could not stat file \"%s\": %m",
+					   sourcepath));
+
+	if (!S_ISLNK(st.st_mode))
+		return pstrdup(sourcepath);
+
+	/*
+	 * In presence of a link or a junction point, return the path pointed to.
+	 */
+	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
+	if (rllen < 0)
+		ereport(ERROR,
+				errcode_for_file_access(),
+				errmsg("could not read symbolic link \"%s\": %m",
+					   sourcepath));
+	if (rllen >= sizeof(targetpath))
+		ereport(ERROR,
+				errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+				errmsg("symbolic link \"%s\" target is too long",
+					   sourcepath));
+	targetpath[rllen] = '\0';
+
+	return pstrdup(targetpath);
+}
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index fa1cb675027..a365c432d34 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -315,66 +315,12 @@ Datum
 pg_tablespace_location(PG_FUNCTION_ARGS)
 {
 	Oid			tablespaceOid = PG_GETARG_OID(0);
-	char		sourcepath[MAXPGPATH];
-	char		targetpath[MAXPGPATH];
-	int			rllen;
-	struct stat st;
+	char	   *tablespaceLoc;
 
-	/*
-	 * It's useful to apply this function to pg_class.reltablespace, wherein
-	 * zero means "the database's default tablespace".  So, rather than
-	 * throwing an error for zero, we choose to assume that's what is meant.
-	 */
-	if (tablespaceOid == InvalidOid)
-		tablespaceOid = MyDatabaseTableSpace;
-
-	/*
-	 * Return empty string for the cluster's default tablespaces
-	 */
-	if (tablespaceOid == DEFAULTTABLESPACE_OID ||
-		tablespaceOid == GLOBALTABLESPACE_OID)
-		PG_RETURN_TEXT_P(cstring_to_text(""));
-
-	/*
-	 * Find the location of the tablespace by reading the symbolic link that
-	 * is in pg_tblspc/<oid>.
-	 */
-	snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
-
-	/*
-	 * Before reading the link, check if the source path is a link or a
-	 * junction point.  Note that a directory is possible for a tablespace
-	 * created with allow_in_place_tablespaces enabled.  If a directory is
-	 * found, a relative path to the data directory is returned.
-	 */
-	if (lstat(sourcepath, &st) < 0)
-	{
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not stat file \"%s\": %m",
-						sourcepath)));
-	}
-
-	if (!S_ISLNK(st.st_mode))
-		PG_RETURN_TEXT_P(cstring_to_text(sourcepath));
-
-	/*
-	 * In presence of a link or a junction point, return the path pointing to.
-	 */
-	rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
-	if (rllen < 0)
-		ereport(ERROR,
-				(errcode_for_file_access(),
-				 errmsg("could not read symbolic link \"%s\": %m",
-						sourcepath)));
-	if (rllen >= sizeof(targetpath))
-		ereport(ERROR,
-				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-				 errmsg("symbolic link \"%s\" target is too long",
-						sourcepath)));
-	targetpath[rllen] = '\0';
+	/* Get LOCATION string from its OID */
+	tablespaceLoc = get_tablespace_location(tablespaceOid);
 
-	PG_RETURN_TEXT_P(cstring_to_text(targetpath));
+	PG_RETURN_TEXT_P(cstring_to_text(tablespaceLoc));
 }
 
 /*
diff --git a/src/include/catalog/pg_tablespace.h b/src/include/catalog/pg_tablespace.h
index 5293488c630..7816d779d8c 100644
--- a/src/include/catalog/pg_tablespace.h
+++ b/src/include/catalog/pg_tablespace.h
@@ -54,4 +54,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);
+
 #endif							/* PG_TABLESPACE_H */
-- 
2.47.3

From 07247f97726ddaf05505e58a9d46550adf9a2eee Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Tue, 11 Nov 2025 18:34:12 -0600
Subject: [PATCH v12] 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.

Author: Manni Wood <manni.wood@enterprisedb.com>
Author: Nishant Sharma <nishant.sharma@enterprisedb.com>
Reviewed-by: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Reviewed-by: Ian Lawrence Barwick <barwick@gmail.com>
Reviewed-by: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Discussion: https://postgr.es/m/CAKWEB6rmnmGKUA87Zmq-s=b3Scsnj02C0kObQjnbL2ajfPWGEw@mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml         |  45 +++++++++
 src/backend/catalog/pg_tablespace.c      | 119 +++++++++++++++++++++++
 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, 324 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..cb088851178 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,118 @@ 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 %d 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'", 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 */
+			appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+			appendStringInfoString(&buf, ", ");
+		}
+
+		/* 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.47.3

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

From d5dbad978a5f3d4d31cab1e256c2108ed1714740 Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Wed, 19 Nov 2025 16:01:36 -0600
Subject: [PATCH v14] 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      | 100 +++++++++++++++++++++++
 src/backend/utils/adt/ruleutils.c        |  40 ++++++++-
 src/include/catalog/pg_proc.dat          |   6 ++
 src/include/catalog/pg_tablespace.h      |   1 +
 src/include/utils/ruleutils.h            |   2 +
 src/test/regress/expected/tablespace.out |  69 ++++++++++++++++
 src/test/regress/sql/tablespace.sql      |  47 +++++++++++
 8 files changed, 308 insertions(+), 2 deletions(-)

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..efe6dbe51ca 100644
--- a/src/backend/catalog/pg_tablespace.c
+++ b/src/backend/catalog/pg_tablespace.c
@@ -17,9 +17,14 @@
 #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/ruleutils.h"
+#include "utils/syscache.h"
 
 
 /*
@@ -88,3 +93,98 @@ 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)
+	{
+		/* Start WITH clause */
+		appendStringInfoString(&buf, " WITH (");
+
+		/* Append reloptions to buf */
+		get_reloptions(&buf, datum);
+
+		/* Close WITH clause */
+		appendStringInfoChar(&buf, ')');
+	}
+
+	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..1c9ef0a597e 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"
@@ -536,7 +537,6 @@ static void add_cast_to(StringInfo buf, Oid typid);
 static char *generate_qualified_type_name(Oid typid);
 static text *string_to_text(char *str);
 static char *flatten_reloptions(Oid relid);
-static void get_reloptions(StringInfo buf, Datum reloptions);
 static void get_json_path_spec(Node *path_spec, deparse_context *context,
 							   bool showimplicit);
 static void get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan,
@@ -13619,7 +13619,7 @@ string_to_text(char *str)
 /*
  * Generate a C string representing a relation options from text[] datum.
  */
-static void
+void
 get_reloptions(StringInfo buf, Datum reloptions)
 {
 	Datum	   *options;
@@ -13743,3 +13743,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 aaadfd8c748..77968d1b13f 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/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 7ba7d887914..0a7d30de85a 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -54,4 +54,6 @@ extern char *get_range_partbound_string(List *bound_datums);
 
 extern char *pg_get_statisticsobjdef_string(Oid statextid);
 
+extern void get_reloptions(StringInfo buf, Datum reloptions);
+
 #endif							/* RULEUTILS_H */
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..aa8ec8da506 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..6298ef1f37b 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.52.0