Greetings
Euler Taveira and I have been working on consolidating these patches.
These patches came out of a suggestion from me some time back [1], and I
used it as the base for some work at an EDB internal program. Perhaps I
was motivated a bit by Mao's dictum "Let a hundred flowers bloom; let a
hundred schools of thought contend." I wanted to see what people would
come up with. Therefore, if this has seemed a bit chaotic, I apologize,
both to the authors and to the list. I won't do things quite this way in
future.
Rather than adding to the already huge ruleutils.c, we decided to create
a new ddlutils.c file to contain these functions and their associated
infrastructure. There is in fact a fairly clean separation between these
functions and ruleutils. We just need to expose one function in ruleutils.
We (Euler and I) decided to concentrate on setting up common
infrastucture and ensuring a common argument and result structure. In
this first round, we are proposing to add functions for getting the DDL
for databases, tablespaces, and roles. We decided to stop there for now.
This sets up a good basis for dealing with more object types in future.
To the authors of the remaining patches - rest assured you have not been
forgotten.
Patch 1 sets up the functions used by the rest for option parsing. see [2]
Patch 2 implements pg_get_role_dll see[3]
Patch 3 implements pg_get_tabespace_ddl see [4]
Patch 4 implements pg_get_database_ddl see [2]
cheers
andrew
[1]
https://www.postgresql.org/message-id/flat/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net
[2]
https://www.postgresql.org/message-id/flat/canxoldc6fhbyjvcgonzys+jf0nuo3lq_83-rttbujgs9id_...@mail.gmail.com
[3]
https://www.postgresql.org/message-id/flat/[email protected]
[4]
https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3scsnj02c0kobqjnbl2ajfpw...@mail.gmail.com
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
From 0392c7a915dd12fbd264586cdcc42195d465dbe1 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email protected]>
Date: Thu, 19 Mar 2026 09:50:41 -0400
Subject: [PATCH 1/4] Add DDL option parsing infrastructure for pg_get_*_ddl
functions
Add parse_ddl_options() and append_ddl_option() helper functions in a
new ddlutils.c file that provide common option parsing for the
pg_get_*_ddl family of functions which will follow in later patches.
These accept VARIADIC text arguments as alternating name/value pairs.
Callers declare an array of DdlOption descriptors specifying the
accepted option names and their types (boolean, text, or integer).
parse_ddl_options() matches each supplied pair against the array,
validates the value, and fills in the result fields. This
descriptor-based scheme is based on an idea from Euler Taveira.
This is placed in a new ddlutils.c file which will contain the
pg_get_*_ddl functions.
Author: Akshay Joshi <[email protected]>
Co-authored-by: Andrew Dunstan <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3scsnj02c0kobqjnbl2ajfpw...@mail.gmail.com
Discussion: https://www.postgresql.org/message-id/flat/[email protected]
Discussion: https://www.postgresql.org/message-id/flat/canxoldc6fhbyjvcgonzys+jf0nuo3lq_83-rttbujgs9id_...@mail.gmail.com
---
src/backend/utils/adt/Makefile | 1 +
src/backend/utils/adt/ddlutils.c | 213 ++++++++++++++++++++++++++++++
src/backend/utils/adt/meson.build | 1 +
src/tools/pgindent/typedefs.list | 2 +
4 files changed, 217 insertions(+)
create mode 100644 src/backend/utils/adt/ddlutils.c
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index a8fd680589f..0c7621957c1 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -31,6 +31,7 @@ OBJS = \
datetime.o \
datum.o \
dbsize.o \
+ ddlutils.o \
domains.o \
encode.o \
enum.o \
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
new file mode 100644
index 00000000000..1ed44cbcb1a
--- /dev/null
+++ b/src/backend/utils/adt/ddlutils.c
@@ -0,0 +1,213 @@
+/*-------------------------------------------------------------------------
+ *
+ * ddlutils.c
+ * Utility functions for generating DDL statements
+ *
+ * This file contains the pg_get_*_ddl family of functions that generate
+ * DDL statements to recreate database objects such as roles, tablespaces,
+ * and databases, along with common infrastructure for option parsing and
+ * pretty-printing.
+ *
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/ddlutils.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "utils/builtins.h"
+#include "utils/guc.h"
+#include "utils/varlena.h"
+
+/* Option value types for DDL option parsing */
+typedef enum
+{
+ DDL_OPT_BOOL,
+ DDL_OPT_TEXT,
+ DDL_OPT_INT
+} DdlOptType;
+
+/*
+ * A single DDL option descriptor: caller fills in name and type,
+ * parse_ddl_options fills in isset + the appropriate value field.
+ */
+typedef struct DdlOption
+{
+ const char *name; /* option name (case-insensitive match) */
+ DdlOptType type; /* expected value type */
+ bool isset; /* true if caller supplied this option */
+ bool boolval; /* filled in for DDL_OPT_BOOL */
+ char *textval; /* filled in for DDL_OPT_TEXT (palloc'd) */
+ int intval; /* filled in for DDL_OPT_INT */
+} DdlOption;
+
+
+static void parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start,
+ DdlOption *opts, int nopts);
+static void append_ddl_option(StringInfo buf, bool pretty, int indent,
+ const char *fmt,...)
+ pg_attribute_printf(4, 5);
+
+
+/*
+ * parse_ddl_options
+ * Parse variadic name/value option pairs
+ *
+ * Options are passed as alternating key/value text pairs. The caller
+ * provides an array of DdlOption descriptors specifying the accepted
+ * option names and their types; this function matches each supplied
+ * pair against the array, validates the value, and fills in the
+ * result fields.
+ */
+static void
+parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start,
+ DdlOption *opts, int nopts)
+{
+ Datum *args;
+ bool *nulls;
+ Oid *types;
+ int nargs;
+
+ /* Clear all output fields */
+ for (int i = 0; i < nopts; i++)
+ {
+ opts[i].isset = false;
+ opts[i].boolval = false;
+ opts[i].textval = NULL;
+ opts[i].intval = 0;
+ }
+
+ nargs = extract_variadic_args(fcinfo, variadic_start, true,
+ &args, &types, &nulls);
+
+ if (nargs <= 0)
+ return;
+
+ /* Handle DEFAULT NULL case */
+ if (nargs == 1 && nulls[0])
+ return;
+
+ if (nargs % 2 != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("variadic arguments must be name/value pairs"),
+ errhint("Provide an even number of variadic arguments that can be divided into pairs.")));
+
+ /*
+ * For each option name/value pair, find corresponding positional option
+ * for the option name, and assign the option value.
+ */
+ for (int i = 0; i < nargs; i += 2)
+ {
+ char *name;
+ char *valstr;
+ DdlOption *opt = NULL;
+
+ if (nulls[i])
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("option name at variadic position %d is null", i + 1)));
+
+ name = TextDatumGetCString(args[i]);
+
+ if (nulls[i + 1])
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("value for option \"%s\" must not be null", name)));
+
+ /* Find matching option descriptor */
+ for (int j = 0; j < nopts; j++)
+ {
+ if (pg_strcasecmp(name, opts[j].name) == 0)
+ {
+ opt = &opts[j];
+ break;
+ }
+ }
+
+ if (opt == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unrecognized option: \"%s\"", name)));
+
+ if (opt->isset)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("option \"%s\" is specified more than once",
+ name)));
+
+ valstr = TextDatumGetCString(args[i + 1]);
+
+ switch (opt->type)
+ {
+ case DDL_OPT_BOOL:
+ if (!parse_bool(valstr, &opt->boolval))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid value for boolean option \"%s\": %s",
+ name, valstr)));
+ break;
+
+ case DDL_OPT_TEXT:
+ opt->textval = valstr;
+ valstr = NULL; /* don't pfree below */
+ break;
+
+ case DDL_OPT_INT:
+ {
+ char *endp;
+ long val;
+
+ errno = 0;
+ val = strtol(valstr, &endp, 10);
+ if (*endp != '\0' || errno == ERANGE ||
+ val < PG_INT32_MIN || val > PG_INT32_MAX)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid value for integer option \"%s\": %s",
+ name, valstr)));
+ opt->intval = (int) val;
+ }
+ break;
+ }
+
+ opt->isset = true;
+
+ if (valstr)
+ pfree(valstr);
+ pfree(name);
+ }
+}
+
+/*
+ * Helper to append a formatted string with optional pretty-printing.
+ */
+static void
+append_ddl_option(StringInfo buf, bool pretty, int indent,
+ const char *fmt,...)
+{
+ va_list args;
+
+ if (pretty)
+ {
+ appendStringInfoChar(buf, '\n');
+ appendStringInfoSpaces(buf, indent);
+ }
+ else
+ appendStringInfoChar(buf, ' ');
+
+ for (;;)
+ {
+ int needed;
+
+ va_start(args, fmt);
+ needed = appendStringInfoVA(buf, fmt, args);
+ va_end(args);
+ if (needed == 0)
+ break;
+ enlargeStringInfo(buf, needed);
+ }
+}
diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build
index fb8294d7e4a..d793f8145f6 100644
--- a/src/backend/utils/adt/meson.build
+++ b/src/backend/utils/adt/meson.build
@@ -30,6 +30,7 @@ backend_sources += files(
'datetime.c',
'datum.c',
'dbsize.c',
+ 'ddlutils.c',
'domains.c',
'encode.c',
'enum.c',
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 4673eca9cd6..007391484a4 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -620,6 +620,8 @@ DSMREntryType
DSMRegistryCtxStruct
DSMRegistryEntry
DWORD
+DdlOptType
+DdlOption
DataDirSyncMethod
DataDumperPtr
DataPageDeleteStack
--
2.43.0
From b615f89b98b2e09f275cb82cdea557bed0a2b68d Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email protected]>
Date: Thu, 19 Mar 2026 09:52:25 -0400
Subject: [PATCH 2/4] Add pg_get_role_ddl() function
Add a new SQL-callable function that returns the DDL statements needed
to recreate a role. It takes a regrole argument and an optional VARIADIC
text argument for options that are specified as alternating name/value
pairs. The following option is supported: pretty (boolean) for
formatted output. The return is one or multiple rows where the first row
is a CREATE ROLE statement and subsequent rows are ALTER ROLE statements
to set some role properties.
Author: Mario Gonzalez <[email protected]>
Author: Bryan Green <[email protected]>
Co-authored-by: Andrew Dunstan <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Quan Zongliang <[email protected]>
Reviewed-by: jian he <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/[email protected]
---
doc/src/sgml/func/func-info.sgml | 54 ++++
src/backend/utils/adt/ddlutils.c | 330 +++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 8 +
src/test/regress/expected/role_ddl.out | 100 ++++++++
src/test/regress/parallel_schedule | 2 +
src/test/regress/sql/role_ddl.sql | 63 +++++
6 files changed, 557 insertions(+)
create mode 100644 src/test/regress/expected/role_ddl.out
create mode 100644 src/test/regress/sql/role_ddl.sql
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 5b5f1f3c5df..acd1a7cfeed 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3860,4 +3860,58 @@ 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"/>
+ reconstruct DDL statements for various global database objects.
+ Each function returns a set of text rows, one SQL statement per row.
+ (This is a decompiled reconstruction, not the original text of the
+ command.) Functions that accept <literal>VARIADIC</literal> options
+ take alternating name/value text pairs; values are parsed as boolean,
+ integer or text.
+ </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_role_ddl</primary>
+ </indexterm>
+ <function>pg_get_role_ddl</function>
+ ( <parameter>role</parameter> <type>regrole</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE ROLE</command> statement and any
+ <command>ALTER ROLE ... SET</command> statements for the given role.
+ Each statement is returned as a separate row.
+ Password information is never included in the output.
+ The following option is supported: <literal>pretty</literal> (boolean)
+ for pretty-printed output.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
</sect1>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index 1ed44cbcb1a..c067de0a3d2 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -18,8 +18,24 @@
*/
#include "postgres.h"
+#include "access/genam.h"
+#include "access/htup_details.h"
+#include "access/relation.h"
+#include "access/table.h"
+#include "catalog/pg_authid.h"
+#include "catalog/pg_db_role_setting.h"
+#include "funcapi.h"
+#include "miscadmin.h"
+#include "utils/acl.h"
+#include "utils/array.h"
#include "utils/builtins.h"
+#include "utils/datetime.h"
+#include "utils/fmgroids.h"
#include "utils/guc.h"
+#include "utils/lsyscache.h"
+#include "utils/rel.h"
+#include "utils/syscache.h"
+#include "utils/timestamp.h"
#include "utils/varlena.h"
/* Option value types for DDL option parsing */
@@ -50,6 +66,7 @@ static void parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start,
static void append_ddl_option(StringInfo buf, bool pretty, int indent,
const char *fmt,...)
pg_attribute_printf(4, 5);
+static List *pg_get_role_ddl_internal(Oid roleid, bool pretty);
/*
@@ -211,3 +228,316 @@ append_ddl_option(StringInfo buf, bool pretty, int indent,
enlargeStringInfo(buf, needed);
}
}
+
+/*
+ * pg_get_role_ddl_internal
+ * Generate DDL statements to recreate a role
+ *
+ * Returns a List of palloc'd strings, each being a complete SQL statement.
+ * The first list element is always the CREATE ROLE statement; subsequent
+ * elements are ALTER ROLE SET statements for any role-specific or
+ * role-in-database configuration settings.
+ */
+static List *
+pg_get_role_ddl_internal(Oid roleid, bool pretty)
+{
+ HeapTuple tuple;
+ Form_pg_authid roleform;
+ StringInfoData buf;
+ char *rolname;
+ Datum rolevaliduntil;
+ bool isnull;
+ Relation rel;
+ ScanKeyData scankey;
+ SysScanDesc scan;
+ List *statements = NIL;
+
+ tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid));
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("role with OID %u does not exist", roleid)));
+
+ roleform = (Form_pg_authid) GETSTRUCT(tuple);
+ rolname = pstrdup(NameStr(roleform->rolname));
+
+ /*
+ * We don't support generating DDL for system roles. The primary reason
+ * for this is that users shouldn't be recreating them.
+ */
+ if (IsReservedName(rolname))
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role name \"%s\" is reserved", rolname),
+ errdetail("Role names starting with \"pg_\" are reserved for system roles.")));
+
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "CREATE ROLE %s", quote_identifier(rolname));
+
+ /*
+ * Append role attributes. The order here follows the same sequence as
+ * you'd typically write them in a CREATE ROLE command, though any order
+ * is actually acceptable to the parser.
+ */
+ append_ddl_option(&buf, pretty, 4, "%s",
+ roleform->rolsuper ? "SUPERUSER" : "NOSUPERUSER");
+
+ append_ddl_option(&buf, pretty, 4, "%s",
+ roleform->rolinherit ? "INHERIT" : "NOINHERIT");
+
+ append_ddl_option(&buf, pretty, 4, "%s",
+ roleform->rolcreaterole ? "CREATEROLE" : "NOCREATEROLE");
+
+ append_ddl_option(&buf, pretty, 4, "%s",
+ roleform->rolcreatedb ? "CREATEDB" : "NOCREATEDB");
+
+ append_ddl_option(&buf, pretty, 4, "%s",
+ roleform->rolcanlogin ? "LOGIN" : "NOLOGIN");
+
+ append_ddl_option(&buf, pretty, 4, "%s",
+ roleform->rolreplication ? "REPLICATION" : "NOREPLICATION");
+
+ append_ddl_option(&buf, pretty, 4, "%s",
+ roleform->rolbypassrls ? "BYPASSRLS" : "NOBYPASSRLS");
+
+ /*
+ * CONNECTION LIMIT is only interesting if it's not -1 (the default,
+ * meaning no limit).
+ */
+ if (roleform->rolconnlimit >= 0)
+ append_ddl_option(&buf, pretty, 4, "CONNECTION LIMIT %d",
+ roleform->rolconnlimit);
+
+ rolevaliduntil = SysCacheGetAttr(AUTHOID, tuple,
+ Anum_pg_authid_rolvaliduntil,
+ &isnull);
+ if (!isnull)
+ {
+ TimestampTz ts;
+ int tz;
+ struct pg_tm tm;
+ fsec_t fsec;
+ const char *tzn;
+ char ts_str[MAXDATELEN + 1];
+
+ ts = DatumGetTimestampTz(rolevaliduntil);
+ if (TIMESTAMP_NOT_FINITE(ts))
+ EncodeSpecialTimestamp(ts, ts_str);
+ else if (timestamp2tm(ts, &tz, &tm, &fsec, &tzn, NULL) == 0)
+ EncodeDateTime(&tm, fsec, true, tz, tzn, DateStyle, ts_str);
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ append_ddl_option(&buf, pretty, 4, "VALID UNTIL %s",
+ quote_literal_cstr(ts_str));
+ }
+
+ ReleaseSysCache(tuple);
+
+ /*
+ * We intentionally omit PASSWORD. There's no way to retrieve the
+ * original password text from the stored hash, and even if we could,
+ * exposing passwords through a SQL function would be a security issue.
+ * Users must set passwords separately after recreating roles.
+ */
+
+ appendStringInfoChar(&buf, ';');
+
+ statements = lappend(statements, pstrdup(buf.data));
+
+ /*
+ * Now scan pg_db_role_setting for ALTER ROLE SET configurations.
+ *
+ * These can be role-wide (setdatabase = 0) or specific to a particular
+ * database (setdatabase = a valid DB OID). It generates one ALTER
+ * statement per setting.
+ */
+ rel = table_open(DbRoleSettingRelationId, AccessShareLock);
+ ScanKeyInit(&scankey,
+ Anum_pg_db_role_setting_setrole,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+ scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,
+ NULL, 1, &scankey);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ Form_pg_db_role_setting setting = (Form_pg_db_role_setting) GETSTRUCT(tuple);
+ Oid datid = setting->setdatabase;
+ Datum datum;
+ ArrayType *reloptions;
+ Datum *settings;
+ bool *nulls;
+ int nsettings;
+ char *datname = NULL;
+
+ /*
+ * If setdatabase is valid, this is a role-in-database setting;
+ * otherwise it's a role-wide setting. Look up the database name once
+ * for all settings in this row.
+ */
+ if (OidIsValid(datid))
+ {
+ datname = get_database_name(datid);
+ /* Database has been dropped; skip all settings in this row. */
+ if (datname == NULL)
+ continue;
+ }
+
+ /*
+ * The setconfig column is a text array in "name=value" format. It
+ * should never be null for a valid row, but be defensive.
+ */
+ datum = heap_getattr(tuple, Anum_pg_db_role_setting_setconfig,
+ RelationGetDescr(rel), &isnull);
+ if (isnull)
+ continue;
+
+ reloptions = DatumGetArrayTypeP(datum);
+
+ deconstruct_array_builtin(reloptions, TEXTOID, &settings, &nulls, &nsettings);
+
+ for (int i = 0; i < nsettings; i++)
+ {
+ char *s,
+ *p;
+
+ if (nulls[i])
+ continue;
+
+ s = TextDatumGetCString(settings[i]);
+ p = strchr(s, '=');
+ if (p == NULL)
+ {
+ pfree(s);
+ continue;
+ }
+ *p++ = '\0';
+
+ /* Build a fresh ALTER ROLE statement for this setting */
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "ALTER ROLE %s", quote_identifier(rolname));
+
+ if (datname != NULL)
+ appendStringInfo(&buf, " IN DATABASE %s",
+ quote_identifier(datname));
+
+ appendStringInfo(&buf, " SET %s TO ",
+ quote_identifier(s));
+
+ /*
+ * Variables that are marked GUC_LIST_QUOTE were already fully
+ * quoted before they were put into the setconfig array. Break
+ * the list value apart and then quote the elements as string
+ * literals.
+ */
+ if (GetConfigOptionFlags(s, true) & GUC_LIST_QUOTE)
+ {
+ List *namelist;
+ ListCell *lc;
+
+ /* Parse string into list of identifiers */
+ if (!SplitGUCList(p, ',', &namelist))
+ {
+ /* this shouldn't fail really */
+ elog(ERROR, "invalid list syntax in setconfig item");
+ }
+ /* Special case: represent an empty list as NULL */
+ if (namelist == NIL)
+ appendStringInfoString(&buf, "NULL");
+ foreach(lc, namelist)
+ {
+ char *curname = (char *) lfirst(lc);
+
+ appendStringInfoString(&buf, quote_literal_cstr(curname));
+ if (lnext(namelist, lc))
+ appendStringInfoString(&buf, ", ");
+ }
+ }
+ else
+ appendStringInfoString(&buf, quote_literal_cstr(p));
+
+ appendStringInfoChar(&buf, ';');
+
+ statements = lappend(statements, pstrdup(buf.data));
+
+ pfree(s);
+ }
+
+ pfree(settings);
+ pfree(reloptions);
+
+ if (datname != NULL)
+ pfree(datname);
+ }
+
+ systable_endscan(scan);
+ table_close(rel, AccessShareLock);
+
+ pfree(buf.data);
+ pfree(rolname);
+
+ return statements;
+}
+
+/*
+ * pg_get_role_ddl
+ * Return DDL to recreate a role as a set of text rows.
+ *
+ * Each row is a complete SQL statement. The first row is always the
+ * CREATE ROLE statement; subsequent rows are ALTER ROLE SET statements.
+ * Returns no rows if the role argument is NULL.
+ */
+Datum
+pg_get_role_ddl(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+ ListCell *lc;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ Oid roleid;
+ DdlOption opts[] = {{"pretty", DDL_OPT_BOOL}};
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (PG_ARGISNULL(0))
+ {
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ roleid = PG_GETARG_OID(0);
+ parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+ statements = pg_get_role_ddl_internal(roleid,
+ opts[0].isset && opts[0].boolval);
+ funcctx->user_fctx = statements;
+ funcctx->max_calls = list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ statements = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ lc = list_nth_cell(statements, funcctx->call_cntr);
+ stmt = (char *) lfirst(lc);
+
+ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+ }
+ else
+ {
+ list_free_deep(statements);
+ SRF_RETURN_DONE(funcctx);
+ }
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fc8d82665b8..e908146645f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8588,6 +8588,14 @@
{ 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 => '8760', descr => 'get DDL to recreate a role',
+ proname => 'pg_get_role_ddl', provariadic => 'text', proisstrict => 'f',
+ provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
+ proargtypes => 'regrole text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{regrole,text}',
+ pronargdefaults => '1', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_role_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/role_ddl.out b/src/test/regress/expected/role_ddl.out
new file mode 100644
index 00000000000..98ef42c9e28
--- /dev/null
+++ b/src/test/regress/expected/role_ddl.out
@@ -0,0 +1,100 @@
+-- Consistent test results
+SET timezone TO 'UTC';
+SET DateStyle TO 'ISO, YMD';
+-- Create test database
+CREATE DATABASE regression_role_ddl_test;
+-- Basic role
+CREATE ROLE regress_role_ddl_test1;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1');
+ pg_get_role_ddl
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test1 NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- Role with LOGIN
+CREATE ROLE regress_role_ddl_test2 LOGIN;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test2');
+ pg_get_role_ddl
+-----------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test2 NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- Role with multiple privileges
+CREATE ROLE regress_role_ddl_test3
+ LOGIN
+ SUPERUSER
+ CREATEDB
+ CREATEROLE
+ CONNECTION LIMIT 5
+ VALID UNTIL '2030-12-31 23:59:59+00';
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3');
+ pg_get_role_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test3 SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 5 VALID UNTIL '2030-12-31 23:59:59+00';
+(1 row)
+
+-- Role with configuration parameters
+CREATE ROLE regress_role_ddl_test4;
+ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB';
+ALTER ROLE regress_role_ddl_test4 SET search_path TO 'myschema, public';
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test4');
+ pg_get_role_ddl
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test4 NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+ ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB';
+ ALTER ROLE regress_role_ddl_test4 SET search_path TO 'myschema, public';
+(3 rows)
+
+-- Role with database-specific configuration
+CREATE ROLE regress_role_ddl_test5;
+ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test5');
+ pg_get_role_ddl
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test5 NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+ ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';
+(2 rows)
+
+-- Role with special characters (requires quoting)
+CREATE ROLE "regress_role-with-dash";
+SELECT * FROM pg_get_role_ddl('regress_role-with-dash');
+ pg_get_role_ddl
+---------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE "regress_role-with-dash" NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- Pretty-printed output
+\pset format unaligned
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3', 'pretty', 'true');
+pg_get_role_ddl
+CREATE ROLE regress_role_ddl_test3
+ SUPERUSER
+ INHERIT
+ CREATEROLE
+ CREATEDB
+ LOGIN
+ NOREPLICATION
+ NOBYPASSRLS
+ CONNECTION LIMIT 5
+ VALID UNTIL '2030-12-31 23:59:59+00';
+(1 row)
+\pset format aligned
+-- Non-existent role (should return no rows)
+SELECT * FROM pg_get_role_ddl(9999999::oid);
+ERROR: role with OID 9999999 does not exist
+-- NULL input (should return no rows)
+SELECT * FROM pg_get_role_ddl(NULL);
+ pg_get_role_ddl
+-----------------
+(0 rows)
+
+-- Cleanup
+DROP ROLE regress_role_ddl_test1;
+DROP ROLE regress_role_ddl_test2;
+DROP ROLE regress_role_ddl_test3;
+DROP ROLE regress_role_ddl_test4;
+DROP ROLE regress_role_ddl_test5;
+DROP ROLE "regress_role-with-dash";
+DROP DATABASE regression_role_ddl_test;
+-- Reset timezone to default
+RESET timezone;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 734da057c34..7e059cef034 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -130,6 +130,8 @@ test: partition_merge partition_split partition_join partition_prune reloptions
# oidjoins is read-only, though, and should run late for best coverage
test: oidjoins event_trigger
+test: role_ddl
+
# event_trigger_login cannot run concurrently with any other tests because
# on-login event handling could catch connection of a concurrent test.
test: event_trigger_login
diff --git a/src/test/regress/sql/role_ddl.sql b/src/test/regress/sql/role_ddl.sql
new file mode 100644
index 00000000000..c9509ae474e
--- /dev/null
+++ b/src/test/regress/sql/role_ddl.sql
@@ -0,0 +1,63 @@
+-- Consistent test results
+SET timezone TO 'UTC';
+SET DateStyle TO 'ISO, YMD';
+
+-- Create test database
+CREATE DATABASE regression_role_ddl_test;
+
+-- Basic role
+CREATE ROLE regress_role_ddl_test1;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1');
+
+-- Role with LOGIN
+CREATE ROLE regress_role_ddl_test2 LOGIN;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test2');
+
+-- Role with multiple privileges
+CREATE ROLE regress_role_ddl_test3
+ LOGIN
+ SUPERUSER
+ CREATEDB
+ CREATEROLE
+ CONNECTION LIMIT 5
+ VALID UNTIL '2030-12-31 23:59:59+00';
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3');
+
+-- Role with configuration parameters
+CREATE ROLE regress_role_ddl_test4;
+ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB';
+ALTER ROLE regress_role_ddl_test4 SET search_path TO 'myschema, public';
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test4');
+
+-- Role with database-specific configuration
+CREATE ROLE regress_role_ddl_test5;
+ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test5');
+
+-- Role with special characters (requires quoting)
+CREATE ROLE "regress_role-with-dash";
+SELECT * FROM pg_get_role_ddl('regress_role-with-dash');
+
+-- Pretty-printed output
+\pset format unaligned
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3', 'pretty', 'true');
+\pset format aligned
+
+-- Non-existent role (should return no rows)
+SELECT * FROM pg_get_role_ddl(9999999::oid);
+
+-- NULL input (should return no rows)
+SELECT * FROM pg_get_role_ddl(NULL);
+
+-- Cleanup
+DROP ROLE regress_role_ddl_test1;
+DROP ROLE regress_role_ddl_test2;
+DROP ROLE regress_role_ddl_test3;
+DROP ROLE regress_role_ddl_test4;
+DROP ROLE regress_role_ddl_test5;
+DROP ROLE "regress_role-with-dash";
+
+DROP DATABASE regression_role_ddl_test;
+
+-- Reset timezone to default
+RESET timezone;
--
2.43.0
From f344010073bb54c6935d7d7599ddb17444589880 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email protected]>
Date: Thu, 19 Mar 2026 09:57:35 -0400
Subject: [PATCH 4/4] Add pg_get_database_ddl() function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Add a new SQL-callable function that returns the DDL statements needed
to recreate a database. It takes a regdatabase argument and an optional
VARIADIC text argument for options that are specified as alternating
name/value pairs. The following options are supported: pretty (boolean)
for formatted output, owner (boolean) to include OWNER and tablespace
(boolean) to include TABLESPACE. The return is one or multiple rows
where the first row is a CREATE DATABASE statement and subsequent rows are
ALTER DATABASE statements to set some database properties.
Author: Akshay Joshi <[email protected]>
Co-authored-by: Andrew Dunstan <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
Reviewed-by: Quan Zongliang <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/canxoldc6fhbyjvcgonzys+jf0nuo3lq_83-rttbujgs9id_...@mail.gmail.com
---
doc/src/sgml/func/func-info.sgml | 23 ++
src/backend/utils/adt/ddlutils.c | 349 +++++++++++++++++++++
src/include/catalog/pg_proc.dat | 8 +
src/test/regress/expected/database_ddl.out | 107 +++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/database_ddl.sql | 89 ++++++
6 files changed, 577 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/database_ddl.out
create mode 100644 src/test/regress/sql/database_ddl.sql
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index f44bd0d0f8b..ce6194f606d 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3935,6 +3935,29 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
for formatted output.
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_database_ddl</primary>
+ </indexterm>
+ <function>pg_get_database_ddl</function>
+ ( <parameter>database</parameter> <type>regdatabase</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE DATABASE</command> statement for the
+ specified database, followed by <command>ALTER DATABASE</command>
+ statements for connection limit, template status, and configuration
+ settings. Each statement is returned as a separate row.
+ The following options are supported:
+ <literal>pretty</literal> (boolean) for formatted output,
+ <literal>owner</literal> (boolean) to include <literal>OWNER</literal>,
+ and <literal>tablespace</literal> (boolean) to include
+ <literal>TABLESPACE</literal>.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index 65df3c723cc..41838e403d3 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -22,11 +22,14 @@
#include "access/htup_details.h"
#include "access/table.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_collation.h"
+#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
#include "catalog/pg_tablespace.h"
#include "commands/tablespace.h"
#include "common/relpath.h"
#include "funcapi.h"
+#include "mb/pg_wchar.h"
#include "miscadmin.h"
#include "utils/acl.h"
#include "utils/array.h"
@@ -35,6 +38,7 @@
#include "utils/fmgroids.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
+#include "utils/pg_locale.h"
#include "utils/rel.h"
#include "utils/ruleutils.h"
#include "utils/syscache.h"
@@ -72,6 +76,8 @@ static void append_ddl_option(StringInfo buf, bool pretty, int indent,
static List *pg_get_role_ddl_internal(Oid roleid, bool pretty);
static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty);
static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);
+static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
+ bool no_owner, bool no_tablespace);
/*
@@ -729,3 +735,346 @@ pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS)
return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull);
}
+
+/*
+ * pg_get_database_ddl_internal
+ * Generate DDL statements to recreate a database.
+ *
+ * Returns a List of palloc'd strings. The first element is the
+ * CREATE DATABASE statement; subsequent elements are ALTER DATABASE
+ * statements for properties and configuration settings.
+ */
+static List *
+pg_get_database_ddl_internal(Oid dbid, bool pretty,
+ bool no_owner, bool no_tablespace)
+{
+ HeapTuple tuple;
+ Form_pg_database dbform;
+ StringInfoData buf;
+ bool isnull;
+ Datum datum;
+ const char *encoding;
+ const char *dbname;
+ char *collate;
+ char *ctype;
+ Relation rel;
+ ScanKeyData scankey;
+ SysScanDesc scan;
+ List *statements = NIL;
+
+ tuple = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbid));
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("database with OID %u does not exist", dbid)));
+
+ dbform = (Form_pg_database) GETSTRUCT(tuple);
+ dbname = quote_identifier(NameStr(dbform->datname));
+
+ /*
+ * We don't support generating DDL for system databases. The primary
+ * reason for this is that users shouldn't be recreating them.
+ */
+ if (strcmp(dbname, "template0") == 0 || strcmp(dbname, "template1") == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("database \"%s\" is a system database", dbname)));
+
+ initStringInfo(&buf);
+
+ /* --- Build CREATE DATABASE statement --- */
+ appendStringInfo(&buf, "CREATE DATABASE %s", dbname);
+
+ append_ddl_option(&buf, pretty, 4, "WITH TEMPLATE = template0");
+
+ /* ENCODING */
+ encoding = pg_encoding_to_char(dbform->encoding);
+ if (strlen(encoding) > 0)
+ append_ddl_option(&buf, pretty, 4, "ENCODING = %s",
+ quote_literal_cstr(encoding));
+
+ /* LOCALE_PROVIDER */
+ if (dbform->datlocprovider == COLLPROVIDER_BUILTIN ||
+ dbform->datlocprovider == COLLPROVIDER_ICU ||
+ dbform->datlocprovider == COLLPROVIDER_LIBC)
+ append_ddl_option(&buf, pretty, 4, "LOCALE_PROVIDER = %s",
+ collprovider_name(dbform->datlocprovider));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("unrecognized locale provider: %c",
+ dbform->datlocprovider)));
+
+ /* LOCALE, LC_COLLATE, LC_CTYPE */
+ datum = SysCacheGetAttr(DATABASEOID, tuple,
+ Anum_pg_database_datcollate, &isnull);
+ if (!isnull)
+ collate = TextDatumGetCString(datum);
+ else
+ collate = "";
+ datum = SysCacheGetAttr(DATABASEOID, tuple,
+ Anum_pg_database_datctype, &isnull);
+ if (!isnull)
+ ctype = TextDatumGetCString(datum);
+ else
+ ctype = "";
+ if (strlen(collate) > 0 && strcmp(collate, ctype) == 0)
+ {
+ append_ddl_option(&buf, pretty, 4, "LOCALE = %s",
+ quote_literal_cstr(collate));
+ }
+ else
+ {
+ if (strlen(collate) > 0)
+ append_ddl_option(&buf, pretty, 4, "LC_COLLATE = %s",
+ quote_literal_cstr(collate));
+ if (strlen(ctype) > 0)
+ append_ddl_option(&buf, pretty, 4, "LC_CTYPE = %s",
+ quote_literal_cstr(ctype));
+ }
+
+ /* LOCALE (provider-specific) */
+ datum = SysCacheGetAttr(DATABASEOID, tuple,
+ Anum_pg_database_datlocale, &isnull);
+ if (!isnull)
+ {
+ const char *locale = TextDatumGetCString(datum);
+
+ if (dbform->datlocprovider == COLLPROVIDER_BUILTIN)
+ append_ddl_option(&buf, pretty, 4, "BUILTIN_LOCALE = %s",
+ quote_literal_cstr(locale));
+ else if (dbform->datlocprovider == COLLPROVIDER_ICU)
+ append_ddl_option(&buf, pretty, 4, "ICU_LOCALE = %s",
+ quote_literal_cstr(locale));
+ }
+
+ /* ICU_RULES */
+ datum = SysCacheGetAttr(DATABASEOID, tuple,
+ Anum_pg_database_daticurules, &isnull);
+ if (!isnull && dbform->datlocprovider == COLLPROVIDER_ICU)
+ append_ddl_option(&buf, pretty, 4, "ICU_RULES = %s",
+ quote_literal_cstr(TextDatumGetCString(datum)));
+
+ /* TABLESPACE */
+ if (!no_tablespace && OidIsValid(dbform->dattablespace))
+ {
+ char *spcname = get_tablespace_name(dbform->dattablespace);
+
+ if (pg_strcasecmp(spcname, "pg_default") != 0)
+ append_ddl_option(&buf, pretty, 4, "TABLESPACE = %s",
+ quote_identifier(spcname));
+ }
+
+ appendStringInfoChar(&buf, ';');
+ statements = lappend(statements, pstrdup(buf.data));
+
+ /* OWNER */
+ if (!no_owner && OidIsValid(dbform->datdba))
+ {
+ char *owner = GetUserNameFromId(dbform->datdba, false);
+
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "ALTER DATABASE %s OWNER = %s;",
+ dbname, quote_identifier(owner));
+ pfree(owner);
+ statements = lappend(statements, pstrdup(buf.data));
+ }
+
+ /* CONNECTION LIMIT */
+ if (dbform->datconnlimit != -1)
+ {
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "ALTER DATABASE %s CONNECTION LIMIT = %d;",
+ dbname, dbform->datconnlimit);
+ statements = lappend(statements, pstrdup(buf.data));
+ }
+
+ /* IS_TEMPLATE */
+ if (dbform->datistemplate)
+ {
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "ALTER DATABASE %s IS_TEMPLATE = true;",
+ dbname);
+ statements = lappend(statements, pstrdup(buf.data));
+ }
+
+ /* ALLOW_CONNECTIONS */
+ if (!dbform->datallowconn)
+ {
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "ALTER DATABASE %s ALLOW_CONNECTIONS = false;",
+ dbname);
+ statements = lappend(statements, pstrdup(buf.data));
+ }
+
+ ReleaseSysCache(tuple);
+
+ /*
+ * Now scan pg_db_role_setting for ALTER DATABASE SET configurations.
+ *
+ * It is only database-wide (setrole = 0). It generates one ALTER
+ * statement per setting.
+ */
+ rel = table_open(DbRoleSettingRelationId, AccessShareLock);
+ ScanKeyInit(&scankey,
+ Anum_pg_db_role_setting_setdatabase,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(dbid));
+
+ scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,
+ NULL, 1, &scankey);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ ArrayType *reloptions;
+ Datum *settings;
+ bool *nulls;
+ int nsettings;
+
+ /*
+ * The setconfig column is a text array in "name=value" format. It
+ * should never be null for a valid row, but be defensive.
+ */
+ datum = heap_getattr(tuple, Anum_pg_db_role_setting_setconfig,
+ RelationGetDescr(rel), &isnull);
+ if (isnull)
+ continue;
+
+ reloptions = DatumGetArrayTypeP(datum);
+
+ deconstruct_array_builtin(reloptions, TEXTOID, &settings, &nulls, &nsettings);
+
+ for (int i = 0; i < nsettings; i++)
+ {
+ char *s,
+ *p;
+
+ if (nulls[i])
+ continue;
+
+ s = TextDatumGetCString(settings[i]);
+ p = strchr(s, '=');
+ if (p == NULL)
+ {
+ pfree(s);
+ continue;
+ }
+ *p++ = '\0';
+
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "ALTER DATABASE %s SET %s TO ",
+ dbname,
+ quote_identifier(s));
+
+ /*
+ * Variables that are marked GUC_LIST_QUOTE were already fully
+ * quoted before they were put into the setconfig array. Break
+ * the list value apart and then quote the elements as string
+ * literals.
+ */
+ if (GetConfigOptionFlags(s, true) & GUC_LIST_QUOTE)
+ {
+ List *namelist;
+ ListCell *lc;
+
+ /* Parse string into list of identifiers */
+ if (!SplitGUCList(p, ',', &namelist))
+ {
+ /* this shouldn't fail really */
+ elog(ERROR, "invalid list syntax in setconfig item");
+ }
+ /* Special case: represent an empty list as NULL */
+ if (namelist == NIL)
+ appendStringInfoString(&buf, "NULL");
+ foreach(lc, namelist)
+ {
+ char *curname = (char *) lfirst(lc);
+
+ appendStringInfoString(&buf, quote_literal_cstr(curname));
+ if (lnext(namelist, lc))
+ appendStringInfoString(&buf, ", ");
+ }
+ }
+ else
+ appendStringInfoString(&buf, quote_literal_cstr(p));
+
+ appendStringInfoChar(&buf, ';');
+
+ statements = lappend(statements, pstrdup(buf.data));
+
+ pfree(s);
+ }
+
+ pfree(settings);
+ pfree(reloptions);
+ }
+
+ systable_endscan(scan);
+ table_close(rel, AccessShareLock);
+
+ pfree(buf.data);
+
+ return statements;
+}
+
+/*
+ * pg_get_database_ddl
+ * Return DDL to recreate a database as a set of text rows.
+ */
+Datum
+pg_get_database_ddl(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+ ListCell *lc;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ Oid dbid;
+ DdlOption opts[] = {
+ {"pretty", DDL_OPT_BOOL},
+ {"owner", DDL_OPT_BOOL},
+ {"tablespace", DDL_OPT_BOOL},
+ };
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (PG_ARGISNULL(0))
+ {
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ dbid = PG_GETARG_OID(0);
+ parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+ statements = pg_get_database_ddl_internal(dbid,
+ opts[0].isset && opts[0].boolval,
+ opts[1].isset && !opts[1].boolval,
+ opts[2].isset && !opts[2].boolval);
+ funcctx->user_fctx = statements;
+ funcctx->max_calls = list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ statements = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ lc = list_nth_cell(statements, funcctx->call_cntr);
+ stmt = (char *) lfirst(lc);
+
+ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+ }
+ else
+ {
+ list_free_deep(statements);
+ SRF_RETURN_DONE(funcctx);
+ }
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d25b6966d5b..b5ad34ccf2c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8612,6 +8612,14 @@
proallargtypes => '{name,text}',
pronargdefaults => '1', proargdefaults => '{NULL}',
prosrc => 'pg_get_tablespace_ddl_name' },
+{ oid => '8762', descr => 'get DDL to recreate a database',
+ proname => 'pg_get_database_ddl', provariadic => 'text', proisstrict => 'f',
+ provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
+ proargtypes => 'regdatabase text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{regdatabase,text}',
+ pronargdefaults => '1', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_database_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/database_ddl.out b/src/test/regress/expected/database_ddl.out
new file mode 100644
index 00000000000..60f933f57f1
--- /dev/null
+++ b/src/test/regress/expected/database_ddl.out
@@ -0,0 +1,107 @@
+--
+-- Tests for pg_get_database_ddl()
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- function removes collation and locale related details.
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+ cleaned_ddl TEXT;
+BEGIN
+ -- Remove LOCALE_PROVIDER placeholders
+ cleaned_ddl := regexp_replace(
+ ddl_input,
+ '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)',
+ '',
+ 'gi'
+ );
+
+ -- Remove LC_COLLATE assignments
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove LC_CTYPE assignments
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove LOCALE placeholders
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove COLLATION placeholders
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
+CREATE ROLE regress_datdba;
+CREATE DATABASE regress_database_ddl
+ ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0
+ OWNER regress_datdba;
+ALTER DATABASE regress_database_ddl CONNECTION_LIMIT 123;
+-- Database doesn't exist
+SELECT * FROM pg_get_database_ddl('regression_database');
+ERROR: database "regression_database" does not exist
+LINE 1: SELECT * FROM pg_get_database_ddl('regression_database');
+ ^
+-- NULL value
+SELECT * FROM pg_get_database_ddl(NULL);
+ pg_get_database_ddl
+---------------------
+(0 rows)
+
+-- Invalid option value (should error)
+SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'invalid');
+ERROR: invalid value for boolean option "owner": invalid
+-- Duplicate option (should error)
+SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false', 'owner', 'true');
+ERROR: option "owner" is specified more than once
+-- Without options
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl');
+ ddl_filter
+-----------------------------------------------------------------------------------
+ CREATE DATABASE regress_database_ddl WITH TEMPLATE = template0 ENCODING = 'UTF8';
+ ALTER DATABASE regress_database_ddl OWNER = regress_datdba;
+ ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123;
+(3 rows)
+
+-- Without owner
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false');
+ ddl_filter
+-----------------------------------------------------------------------------------
+ CREATE DATABASE regress_database_ddl WITH TEMPLATE = template0 ENCODING = 'UTF8';
+ ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123;
+(2 rows)
+
+-- Pretty-printed output
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'pretty', 'true', 'tablespace', 'false');
+ddl_filter
+CREATE DATABASE regress_database_ddl
+ WITH TEMPLATE = template0
+ ENCODING = 'UTF8';
+ALTER DATABASE regress_database_ddl OWNER = regress_datdba;
+ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123;
+(3 rows)
+\pset format aligned
+DROP DATABASE regress_database_ddl;
+DROP FUNCTION ddl_filter(text);
+DROP ROLE regress_datdba;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index f3a01aecf04..d97b9f16908 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -130,7 +130,7 @@ test: partition_merge partition_split partition_join partition_prune reloptions
# oidjoins is read-only, though, and should run late for best coverage
test: oidjoins event_trigger
-test: role_ddl tablespace_ddl
+test: role_ddl tablespace_ddl database_ddl
# event_trigger_login cannot run concurrently with any other tests because
# on-login event handling could catch connection of a concurrent test.
diff --git a/src/test/regress/sql/database_ddl.sql b/src/test/regress/sql/database_ddl.sql
new file mode 100644
index 00000000000..4460dfedcd7
--- /dev/null
+++ b/src/test/regress/sql/database_ddl.sql
@@ -0,0 +1,89 @@
+--
+-- Tests for pg_get_database_ddl()
+--
+
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- function removes collation and locale related details.
+
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+ cleaned_ddl TEXT;
+BEGIN
+ -- Remove LOCALE_PROVIDER placeholders
+ cleaned_ddl := regexp_replace(
+ ddl_input,
+ '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)',
+ '',
+ 'gi'
+ );
+
+ -- Remove LC_COLLATE assignments
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove LC_CTYPE assignments
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove LOCALE placeholders
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove COLLATION placeholders
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE ROLE regress_datdba;
+CREATE DATABASE regress_database_ddl
+ ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0
+ OWNER regress_datdba;
+ALTER DATABASE regress_database_ddl CONNECTION_LIMIT 123;
+
+-- Database doesn't exist
+SELECT * FROM pg_get_database_ddl('regression_database');
+
+-- NULL value
+SELECT * FROM pg_get_database_ddl(NULL);
+
+-- Invalid option value (should error)
+SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'invalid');
+
+-- Duplicate option (should error)
+SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false', 'owner', 'true');
+
+-- Without options
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl');
+
+-- Without owner
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false');
+
+-- Pretty-printed output
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'pretty', 'true', 'tablespace', 'false');
+\pset format aligned
+
+DROP DATABASE regress_database_ddl;
+DROP FUNCTION ddl_filter(text);
+DROP ROLE regress_datdba;
--
2.43.0
From c82a8c634fa828d2ae1aebf9b8394b3a42e1fe65 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email protected]>
Date: Thu, 19 Mar 2026 09:55:16 -0400
Subject: [PATCH 3/4] Add pg_get_tablespace_ddl() function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Add a new SQL-callable function that returns the DDL statements needed
to recreate a tablespace. It takes a tablespace name or OID and an
optional VARIADIC text argument for options that are specified as
alternating name/value pairs. The following option is supported: pretty
(boolean) for formatted output. (It includes two variants because there
is no regtablespace pseudotype.) The return is one or multiple rows where
the first row is a CREATE TABLESPACE statement and subsequent rows are
ALTER TABLESPACE statements to set some tablespace properties.
get_reloptions() in ruleutils.c is made non-static so it can be called
from the new ddlutils.c file.
Author: Nishant Sharma <[email protected]>
Author: Manni Wood <[email protected]>
Co-authored-by: Andrew Dunstan <[email protected]>
Reviewed-by: Jim Jones <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3scsnj02c0kobqjnbl2ajfpw...@mail.gmail.com
---
doc/src/sgml/func/func-info.sgml | 27 +++
src/backend/utils/adt/ddlutils.c | 190 ++++++++++++++++++-
src/backend/utils/adt/ruleutils.c | 4 +-
src/include/catalog/pg_proc.dat | 16 ++
src/include/utils/ruleutils.h | 1 +
src/test/regress/expected/tablespace_ddl.out | 65 +++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/tablespace_ddl.sql | 43 +++++
8 files changed, 344 insertions(+), 4 deletions(-)
create mode 100644 src/test/regress/expected/tablespace_ddl.out
create mode 100644 src/test/regress/sql/tablespace_ddl.sql
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index acd1a7cfeed..f44bd0d0f8b 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3908,6 +3908,33 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
for pretty-printed output.
</para></entry>
</row>
+ <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>oid</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ <function>pg_get_tablespace_ddl</function>
+ ( <parameter>tablespace</parameter> <type>name</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE TABLESPACE</command> statement for
+ the specified tablespace (by OID or name). If the tablespace has
+ options set, an <command>ALTER TABLESPACE ... SET</command> statement
+ is also returned. Each statement is returned as a separate row.
+ The following option is supported: <literal>pretty</literal> (boolean)
+ for formatted output.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index c067de0a3d2..65df3c723cc 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -20,10 +20,12 @@
#include "access/genam.h"
#include "access/htup_details.h"
-#include "access/relation.h"
#include "access/table.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_db_role_setting.h"
+#include "catalog/pg_tablespace.h"
+#include "commands/tablespace.h"
+#include "common/relpath.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "utils/acl.h"
@@ -34,6 +36,7 @@
#include "utils/guc.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
+#include "utils/ruleutils.h"
#include "utils/syscache.h"
#include "utils/timestamp.h"
#include "utils/varlena.h"
@@ -67,6 +70,8 @@ static void append_ddl_option(StringInfo buf, bool pretty, int indent,
const char *fmt,...)
pg_attribute_printf(4, 5);
static List *pg_get_role_ddl_internal(Oid roleid, bool pretty);
+static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty);
+static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);
/*
@@ -541,3 +546,186 @@ pg_get_role_ddl(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * pg_get_tablespace_ddl_internal
+ * Generate DDL statements to recreate a tablespace.
+ *
+ * Returns a List of palloc'd strings. The first element is the
+ * CREATE TABLESPACE statement; if the tablespace has reloptions,
+ * a second element with ALTER TABLESPACE SET (...) is appended.
+ */
+static List *
+pg_get_tablespace_ddl_internal(Oid tsid, bool pretty)
+{
+ HeapTuple tuple;
+ Form_pg_tablespace tspForm;
+ StringInfoData buf;
+ char *spcname;
+ char *spcowner;
+ char *path;
+ bool isNull;
+ Datum datum;
+ List *statements = NIL;
+
+ tuple = SearchSysCache1(TABLESPACEOID, ObjectIdGetDatum(tsid));
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("tablespace with OID %u does not exist",
+ tsid)));
+
+ tspForm = (Form_pg_tablespace) GETSTRUCT(tuple);
+ spcname = pstrdup(NameStr(tspForm->spcname));
+
+ /*
+ * We don't support generating DDL for system tablespaces. The primary
+ * reason for this is that users shouldn't be recreating them.
+ */
+ if (IsReservedName(spcname))
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("tablespace name \"%s\" is reserved", spcname),
+ errdetail("Tablespace names starting with \"pg_\" are reserved for system tablespaces.")));
+
+ initStringInfo(&buf);
+
+ /* Start building the CREATE TABLESPACE statement */
+ appendStringInfo(&buf, "CREATE TABLESPACE %s", quote_identifier(spcname));
+
+ /* Add OWNER clause */
+ spcowner = GetUserNameFromId(tspForm->spcowner, false);
+ append_ddl_option(&buf, pretty, 4, "OWNER %s",
+ quote_identifier(spcowner));
+ pfree(spcowner);
+
+ /* Find tablespace directory path */
+ path = get_tablespace_location(tsid);
+
+ /* 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)
+ append_ddl_option(&buf, pretty, 4, "LOCATION ''");
+ else
+ append_ddl_option(&buf, pretty, 4, "LOCATION %s",
+ quote_literal_cstr(path));
+ }
+ pfree(path);
+
+ appendStringInfoChar(&buf, ';');
+ statements = lappend(statements, pstrdup(buf.data));
+
+ /* Check for tablespace options */
+ datum = SysCacheGetAttr(TABLESPACEOID, tuple,
+ Anum_pg_tablespace_spcoptions, &isNull);
+ if (!isNull)
+ {
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "ALTER TABLESPACE %s SET (",
+ quote_identifier(spcname));
+ get_reloptions(&buf, datum);
+ appendStringInfoString(&buf, ");");
+ statements = lappend(statements, pstrdup(buf.data));
+ }
+
+ ReleaseSysCache(tuple);
+ pfree(buf.data);
+
+ return statements;
+}
+
+/*
+ * pg_get_tablespace_ddl_srf - common SRF logic for tablespace DDL
+ */
+static Datum
+pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ DdlOption opts[] = {{"pretty", DDL_OPT_BOOL}};
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (isnull)
+ {
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+ statements = pg_get_tablespace_ddl_internal(tsid,
+ opts[0].isset && opts[0].boolval);
+ funcctx->user_fctx = statements;
+ funcctx->max_calls = list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ statements = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ stmt = (char *) list_nth(statements, funcctx->call_cntr);
+
+ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+ }
+ else
+ {
+ list_free_deep(statements);
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
+/*
+ * pg_get_tablespace_ddl_oid
+ * Return DDL to recreate a tablespace, taking OID.
+ */
+Datum
+pg_get_tablespace_ddl_oid(PG_FUNCTION_ARGS)
+{
+ Oid tsid = InvalidOid;
+ bool isnull;
+
+ isnull = PG_ARGISNULL(0);
+ tsid = PG_GETARG_OID(0);
+
+ return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull);
+}
+
+/*
+ * pg_get_tablespace_ddl_name
+ * Return DDL to recreate a tablespace, taking name.
+ */
+Datum
+pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS)
+{
+ Oid tsid = InvalidOid;
+ Name tspname;
+ bool isnull;
+
+ isnull = PG_ARGISNULL(0);
+
+ if (!isnull)
+ {
+ tspname = PG_GETARG_NAME(0);
+ tsid = get_tablespace_oid(NameStr(*tspname), false);
+ }
+
+ return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull);
+}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 7bc12589e40..1450c101e9e 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -545,7 +545,7 @@ 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);
+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,
@@ -14199,7 +14199,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;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index e908146645f..d25b6966d5b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8596,6 +8596,22 @@
proallargtypes => '{regrole,text}',
pronargdefaults => '1', proargdefaults => '{NULL}',
prosrc => 'pg_get_role_ddl' },
+{ oid => '8758', descr => 'get DDL to recreate a tablespace',
+ proname => 'pg_get_tablespace_ddl', provariadic => 'text', proisstrict => 'f',
+ provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
+ proargtypes => 'oid text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{oid,text}',
+ pronargdefaults => '1', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_tablespace_ddl_oid' },
+{ oid => '8759', descr => 'get DDL to recreate a tablespace',
+ proname => 'pg_get_tablespace_ddl', provariadic => 'text', proisstrict => 'f',
+ provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
+ proargtypes => 'name text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{name,text}',
+ pronargdefaults => '1', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_tablespace_ddl_name' },
{ 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 908b2708ed4..ac40d4c714e 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -51,6 +51,7 @@ extern char *get_window_frame_options_for_explain(int frameOptions,
extern char *generate_collation_name(Oid collid);
extern char *generate_opclass_name(Oid opclass);
extern char *get_range_partbound_string(List *bound_datums);
+extern void get_reloptions(StringInfo buf, Datum reloptions);
extern char *pg_get_statisticsobjdef_string(Oid statextid);
diff --git a/src/test/regress/expected/tablespace_ddl.out b/src/test/regress/expected/tablespace_ddl.out
new file mode 100644
index 00000000000..993841a7de1
--- /dev/null
+++ b/src/test/regress/expected/tablespace_ddl.out
@@ -0,0 +1,65 @@
+--
+-- Tests for pg_get_tablespace_ddl()
+--
+SET allow_in_place_tablespaces = true;
+CREATE ROLE regress_tblspc_ddl_user;
+-- error: non-existent tablespace by name
+SELECT * FROM pg_get_tablespace_ddl('regress_nonexistent_tblsp');
+ERROR: tablespace "regress_nonexistent_tblsp" does not exist
+-- error: non-existent tablespace by OID
+SELECT * FROM pg_get_tablespace_ddl(0::oid);
+ERROR: tablespace with OID 0 does not exist
+-- NULL input returns no rows (name variant)
+SELECT * FROM pg_get_tablespace_ddl(NULL::name);
+ pg_get_tablespace_ddl
+-----------------------
+(0 rows)
+
+-- NULL input returns no rows (OID variant)
+SELECT * FROM pg_get_tablespace_ddl(NULL::oid);
+ pg_get_tablespace_ddl
+-----------------------
+(0 rows)
+
+-- tablespace name requiring quoting
+CREATE TABLESPACE "regress_ tblsp" OWNER regress_tblspc_ddl_user LOCATION '';
+SELECT * FROM pg_get_tablespace_ddl('regress_ tblsp');
+ pg_get_tablespace_ddl
+-------------------------------------------------------------------------------
+ CREATE TABLESPACE "regress_ tblsp" OWNER regress_tblspc_ddl_user LOCATION '';
+(1 row)
+
+DROP TABLESPACE "regress_ tblsp";
+-- tablespace with multiple options
+CREATE TABLESPACE regress_allopt_tblsp OWNER regress_tblspc_ddl_user LOCATION ''
+ WITH (seq_page_cost = '1.5', random_page_cost = '1.1234567890',
+ effective_io_concurrency = '17', maintenance_io_concurrency = '18');
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp');
+ pg_get_tablespace_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLESPACE regress_allopt_tblsp OWNER regress_tblspc_ddl_user LOCATION '';
+ ALTER TABLESPACE regress_allopt_tblsp SET (seq_page_cost='1.5', random_page_cost='1.1234567890', effective_io_concurrency='17', maintenance_io_concurrency='18');
+(2 rows)
+
+-- pretty-printed output
+\pset format unaligned
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'pretty', 'true');
+pg_get_tablespace_ddl
+CREATE TABLESPACE regress_allopt_tblsp
+ OWNER regress_tblspc_ddl_user
+ LOCATION '';
+ALTER TABLESPACE regress_allopt_tblsp SET (seq_page_cost='1.5', random_page_cost='1.1234567890', effective_io_concurrency='17', maintenance_io_concurrency='18');
+(2 rows)
+\pset format aligned
+DROP TABLESPACE regress_allopt_tblsp;
+-- test by OID
+CREATE TABLESPACE regress_oid_tblsp OWNER regress_tblspc_ddl_user LOCATION '';
+SELECT oid AS tsid FROM pg_tablespace WHERE spcname = 'regress_oid_tblsp' \gset
+SELECT * FROM pg_get_tablespace_ddl(:tsid);
+ pg_get_tablespace_ddl
+--------------------------------------------------------------------------------
+ CREATE TABLESPACE regress_oid_tblsp OWNER regress_tblspc_ddl_user LOCATION '';
+(1 row)
+
+DROP TABLESPACE regress_oid_tblsp;
+DROP ROLE regress_tblspc_ddl_user;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 7e059cef034..f3a01aecf04 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -130,7 +130,7 @@ test: partition_merge partition_split partition_join partition_prune reloptions
# oidjoins is read-only, though, and should run late for best coverage
test: oidjoins event_trigger
-test: role_ddl
+test: role_ddl tablespace_ddl
# event_trigger_login cannot run concurrently with any other tests because
# on-login event handling could catch connection of a concurrent test.
diff --git a/src/test/regress/sql/tablespace_ddl.sql b/src/test/regress/sql/tablespace_ddl.sql
new file mode 100644
index 00000000000..90ee6c1d703
--- /dev/null
+++ b/src/test/regress/sql/tablespace_ddl.sql
@@ -0,0 +1,43 @@
+--
+-- Tests for pg_get_tablespace_ddl()
+--
+
+SET allow_in_place_tablespaces = true;
+CREATE ROLE regress_tblspc_ddl_user;
+
+-- error: non-existent tablespace by name
+SELECT * FROM pg_get_tablespace_ddl('regress_nonexistent_tblsp');
+
+-- error: non-existent tablespace by OID
+SELECT * FROM pg_get_tablespace_ddl(0::oid);
+
+-- NULL input returns no rows (name variant)
+SELECT * FROM pg_get_tablespace_ddl(NULL::name);
+
+-- NULL input returns no rows (OID variant)
+SELECT * FROM pg_get_tablespace_ddl(NULL::oid);
+
+-- tablespace name requiring quoting
+CREATE TABLESPACE "regress_ tblsp" OWNER regress_tblspc_ddl_user LOCATION '';
+SELECT * FROM pg_get_tablespace_ddl('regress_ tblsp');
+DROP TABLESPACE "regress_ tblsp";
+
+-- tablespace with multiple options
+CREATE TABLESPACE regress_allopt_tblsp OWNER regress_tblspc_ddl_user LOCATION ''
+ WITH (seq_page_cost = '1.5', random_page_cost = '1.1234567890',
+ effective_io_concurrency = '17', maintenance_io_concurrency = '18');
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp');
+
+-- pretty-printed output
+\pset format unaligned
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'pretty', 'true');
+\pset format aligned
+DROP TABLESPACE regress_allopt_tblsp;
+
+-- test by OID
+CREATE TABLESPACE regress_oid_tblsp OWNER regress_tblspc_ddl_user LOCATION '';
+SELECT oid AS tsid FROM pg_tablespace WHERE spcname = 'regress_oid_tblsp' \gset
+SELECT * FROM pg_get_tablespace_ddl(:tsid);
+DROP TABLESPACE regress_oid_tblsp;
+
+DROP ROLE regress_tblspc_ddl_user;
--
2.43.0
