Hello!
I am submitting a patch as a part of a larger Retail DDL functions project
described by Andrew Dunstan here:
https://www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net
This patch creates a function pg_get_tablespace_ddl, designed to retrieve
the full DDL statement for a tablespace. Users can obtain the DDL by
providing the tablespace name, like so:
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);
This patch includes documentation, comments, and regression tests, all of
which pass successfully.
--
Best,
Manni Wood
EnterpriseDB
From a5724c200748993ea6e1e22948961a5c0f821f9d Mon Sep 17 00:00:00 2001
From: Manni Wood <[email protected]>
Date: Tue, 28 Oct 2025 17:36:10 -0500
Subject: [PATCH v1] 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 | 118 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +
src/test/regress/expected/tablespace.out | 59 ++++++++++++
src/test/regress/sql/tablespace.sql | 41 ++++++++
5 files changed, 266 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..6b8aedc2115 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,118 @@ 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;
+ TableSpaceOpts *opts = NULL;
+ 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)
+ {
+ bytea *bytea_opts = tablespace_reloptions(datum, false);
+
+ opts = (TableSpaceOpts *) palloc0(VARSIZE(bytea_opts));
+ memcpy(opts, bytea_opts, VARSIZE(bytea_opts));
+
+ /* Add the valid options in WITH clause */
+ appendStringInfoString(&buf, " WITH (");
+
+ if (opts->random_page_cost > 0)
+ appendStringInfo(&buf, "random_page_cost = %g, ",
+ opts->random_page_cost);
+
+ if (opts->seq_page_cost > 0)
+ appendStringInfo(&buf, "seq_page_cost = %g, ",
+ opts->seq_page_cost);
+
+ if (opts->effective_io_concurrency > 0)
+ appendStringInfo(&buf, "effective_io_concurrency = %d, ",
+ opts->effective_io_concurrency);
+
+ if (opts->maintenance_io_concurrency > 0)
+ appendStringInfo(&buf, "maintenance_io_concurrency = %d, ",
+ opts->maintenance_io_concurrency);
+
+ /* buf ends up with unwanted trailing comma and space; remove them */
+ buf.len -= 2;
+ buf.data[buf.len] = '\0'; /* Null-terminate the modified string */
+
+ /* Free the opts now */
+ pfree(opts);
+
+ appendStringInfoChar(&buf, ')');
+ }
+
+ 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..8a1c678b1db 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -971,3 +971,62 @@ 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 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);
+(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);
+(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.6, 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 (random_page_cost = 1.6, seq_page_cost = 1.5, 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..2a478558c25 100644
--- a/src/test/regress/sql/tablespace.sql
+++ b/src/test/regress/sql/tablespace.sql
@@ -437,3 +437,44 @@ 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 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.6, 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
