Subject: Re: pg_get__*_ddl consolidation

From: Andrew Dunstan <[email-masked]>
Date: Fri, 20 Mar 2026 09:31:11 -0400

On 2026-03-20 Fr 9:15 AM, Álvaro Herrera wrote:
> On 2026-Mar-19, Andrew Dunstan wrote:
>
>> Greetings
>>
>> Euler Taveira and I have been working on consolidating these patches.
> Hmm, did you remove the permissions checking to dump objects?  I thought
> we had concluded that these were needed -- ie. you have to have at least
> CONNECT to a database to be able to dump it, and so on.  This way, the
> functions do not override a DBAs intention to hide the information, when
> they run REVOKE on the catalogs.  I know this is a nonstandard thing to
> do, but some people do it nonetheless.
>
> https://postgr.es/m/[email-masked]
>

Oh, hmm, yes, I think we did. Will work on it.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

---

From: "Euler Taveira" <[email-masked]>
Date: Tue, 24 Mar 2026 12:43:00 -0300

> Oh, hmm, yes, I think we did. Will work on it.

Here is a new patchset (v2) including all suggested changes in this thread. It
fixes:

* DDLOptType: comma in the last element;
* union for boolval, textval, intval;
* va_list in a restricted scope;
* foreach_ptr + boolean in patches 0002 and 0004;
* list_nth instead of list_nth_cell in patches 0002 and 0004;
* OWNER = role typo. Add test;
* use pstrdup for dbname;
* output only database-specific GUCs;
* add ACL_CONNECT check as the original patch. I removed the pg_read_all_stats
case because it doesn't match the role description;

However, I didn't include the suggestion to explain that pg_get_role_ddl is
dependent on the DateStyle. I think it fits better in the CREATE ROLE [1] that
does not mention it in the VALID UNTIL clause. I'm not opposed to the idea of
adding a sentence to the function description but my suggestion is that this
new sentence points to CREATE ROLE page.

[1] https://www.postgresql.org/docs/current/sql-createrole.html

-- 
Euler Taveira
EDB   https://www.enterprisedb.com/

From a97e576398ece58c067173ea724588415bd4a20c Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 09:50:41 -0400
Subject: [PATCH v2 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-masked]>
Co-authored-by: Andrew Dunstan <[email-masked]>
Reviewed-by: Euler Taveira <[email-masked]>
Discussion: https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=[email-masked]
Discussion: https://www.postgresql.org/message-id/flat/[email-masked]
Discussion: https://www.postgresql.org/message-id/flat/[email-masked]
---
 src/backend/utils/adt/Makefile    |   1 +
 src/backend/utils/adt/ddlutils.c  | 226 ++++++++++++++++++++++++++++++
 src/backend/utils/adt/meson.build |   1 +
 src/tools/pgindent/typedefs.list  |   2 +
 4 files changed, 230 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..a15d1e00d79
--- /dev/null
+++ b/src/backend/utils/adt/ddlutils.c
@@ -0,0 +1,226 @@
+/*-------------------------------------------------------------------------
+ *
+ * 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 */
+	/* fields for specific option types */
+	union
+	{
+		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;
+		switch (opts[i].type)
+		{
+			case DDL_OPT_BOOL:
+				opts[i].boolval = false;
+				break;
+			case DDL_OPT_TEXT:
+				opts[i].textval = NULL;
+				break;
+			case DDL_OPT_INT:
+				opts[i].intval = 0;
+				break;
+		}
+	}
+
+	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,...)
+{
+
+	if (pretty)
+	{
+		appendStringInfoChar(buf, '\n');
+		appendStringInfoSpaces(buf, indent);
+	}
+	else
+		appendStringInfoChar(buf, ' ');
+
+	for (;;)
+	{
+		va_list		args;
+		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 8df23840e57..82a21a593d4 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -624,6 +624,8 @@ DSMREntryType
 DSMRegistryCtxStruct
 DSMRegistryEntry
 DWORD
+DdlOptType
+DdlOption
 DataDirSyncMethod
 DataDumperPtr
 DataPageDeleteStack
-- 
2.39.5

From cb94896caa6915a7980b07f70f40d936ce95eb10 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 09:52:25 -0400
Subject: [PATCH v2 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-masked]>
Author: Bryan Green <[email-masked]>
Co-authored-by: Andrew Dunstan <[email-masked]>
Reviewed-by: Euler Taveira <[email-masked]>
Reviewed-by: Japin Li <[email-masked]>
Reviewed-by: Quan Zongliang <[email-masked]>
Reviewed-by: jian he <[email-masked]>
Discussion: https://www.postgresql.org/message-id/flat/[email-masked]
---
 doc/src/sgml/func/func-info.sgml       |  54 ++++
 src/backend/utils/adt/ddlutils.c       | 328 +++++++++++++++++++++++++
 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, 555 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 a15d1e00d79..fd85edd7eeb 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 */
@@ -54,6 +70,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);

 /*
@@ -224,3 +241,314 @@ 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;
+				bool		first = true;
+
+				/* 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_ptr(char, curname, namelist)
+				{
+					if (first)
+						first = false;
+					else
+						appendStringInfoString(&buf, ", ");
+					appendStringInfoString(&buf, quote_literal_cstr(curname));
+				}
+			}
+			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;
+
+	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;
+
+		stmt = list_nth(statements, funcctx->call_cntr);
+
+		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 0118e970dda..fbd400b5a67 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8603,6 +8603,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.39.5

From 2c37306fb1763599141c7f04244f1ad82b840121 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 09:55:16 -0400
Subject: [PATCH v2 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-masked]>
Author: Manni Wood <[email-masked]>
Co-authored-by: Andrew Dunstan <[email-masked]>
Reviewed-by: Jim Jones <[email-masked]>
Reviewed-by: Álvaro Herrera <[email-masked]>
Reviewed-by: Chao Li <[email-masked]>
Reviewed-by: Euler Taveira <[email-masked]>
Discussion: https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=[email-masked]
---
 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 fd85edd7eeb..0deece914ce 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"
@@ -71,6 +74,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);

 /*
@@ -552,3 +557,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 fbd400b5a67..f5baa0d62f1 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8611,6 +8611,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.39.5

From 4aefd1b597c8ce7af042ea4dedfcaf35c80b226f Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 09:57:35 -0400
Subject: [PATCH v2 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-masked]>
Co-authored-by: Andrew Dunstan <[email-masked]>
Reviewed-by: Japin Li <[email-masked]>
Reviewed-by: Chao Li <[email-masked]>
Reviewed-by: Álvaro Herrera <[email-masked]>
Reviewed-by: Euler Taveira <[email-masked]>
Reviewed-by: Quan Zongliang <[email-masked]>
Discussion: https://www.postgresql.org/message-id/flat/[email-masked]
---
 doc/src/sgml/func/func-info.sgml           |  23 ++
 src/backend/utils/adt/ddlutils.c           | 359 +++++++++++++++++++++
 src/include/catalog/pg_proc.dat            |   8 +
 src/test/regress/expected/database_ddl.out | 113 +++++++
 src/test/regress/parallel_schedule         |   2 +-
 src/test/regress/sql/database_ddl.sql      |  91 ++++++
 6 files changed, 595 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 0deece914ce..1071985c99b 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"
@@ -76,6 +80,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);

 /*
@@ -740,3 +746,356 @@ 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;
+	char	   *dbname;
+	char	   *collate;
+	char	   *ctype;
+	Relation	rel;
+	ScanKeyData scankey[2];
+	SysScanDesc scan;
+	List	   *statements = NIL;
+	AclResult	aclresult;
+
+	tuple = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbid));
+	if (!HeapTupleIsValid(tuple))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("database with OID %u does not exist", dbid)));
+
+	/* User must have connect privilege for target database. */
+	aclresult = object_aclcheck(DatabaseRelationId, dbid, GetUserId(), ACL_CONNECT);
+	if (aclresult != ACLCHECK_OK)
+		aclcheck_error(aclresult, OBJECT_DATABASE,
+					   get_database_name(dbid));
+
+	dbform = (Form_pg_database) GETSTRUCT(tuple);
+	dbname = pstrdup(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", quote_identifier(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 TO %s;",
+						 quote_identifier(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;",
+						 quote_identifier(dbname), dbform->datconnlimit);
+		statements = lappend(statements, pstrdup(buf.data));
+	}
+
+	/* IS_TEMPLATE */
+	if (dbform->datistemplate)
+	{
+		resetStringInfo(&buf);
+		appendStringInfo(&buf, "ALTER DATABASE %s IS_TEMPLATE = true;",
+						 quote_identifier(dbname));
+		statements = lappend(statements, pstrdup(buf.data));
+	}
+
+	/* ALLOW_CONNECTIONS */
+	if (!dbform->datallowconn)
+	{
+		resetStringInfo(&buf);
+		appendStringInfo(&buf, "ALTER DATABASE %s ALLOW_CONNECTIONS = false;",
+						 quote_identifier(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[0],
+				Anum_pg_db_role_setting_setdatabase,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(dbid));
+	ScanKeyInit(&scankey[1],
+				Anum_pg_db_role_setting_setrole,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(InvalidOid));
+
+	scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,
+							  NULL, 2, 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 ",
+							 quote_identifier(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;
+				bool		first = true;
+
+				/* 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_ptr(char, curname, namelist)
+				{
+					if (first)
+						first = false;
+					else
+						appendStringInfoString(&buf, ", ");
+					appendStringInfoString(&buf, quote_literal_cstr(curname));
+				}
+			}
+			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);
+	pfree(dbname);
+
+	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;
+
+	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;
+
+		stmt = list_nth(statements, funcctx->call_cntr);
+
+		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 f5baa0d62f1..dee53c1cd38 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8627,6 +8627,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..5f4a2ca7566
--- /dev/null
+++ b/src/test/regress/expected/database_ddl.out
@@ -0,0 +1,113 @@
+--
+-- 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;
+ALTER DATABASE regress_database_ddl SET random_page_cost = 2.0;
+ALTER ROLE regress_datdba IN DATABASE regress_database_ddl SET random_page_cost = 1.1;
+-- 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 TO regress_datdba;
+ ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123;
+ ALTER DATABASE regress_database_ddl SET random_page_cost TO '2.0';
+(4 rows)
+
+-- With owner
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'true');
+                                    ddl_filter                                     
+-----------------------------------------------------------------------------------
+ CREATE DATABASE regress_database_ddl WITH TEMPLATE = template0 ENCODING = 'UTF8';
+ ALTER DATABASE regress_database_ddl OWNER TO regress_datdba;
+ ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123;
+ ALTER DATABASE regress_database_ddl SET random_page_cost TO '2.0';
+(4 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 TO regress_datdba;
+ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123;
+ALTER DATABASE regress_database_ddl SET random_page_cost TO '2.0';
+(4 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..2ffbc27fccb
--- /dev/null
+++ b/src/test/regress/sql/database_ddl.sql
@@ -0,0 +1,91 @@
+--
+-- 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;
+ALTER DATABASE regress_database_ddl SET random_page_cost = 2.0;
+ALTER ROLE regress_datdba IN DATABASE regress_database_ddl SET random_page_cost = 1.1;
+
+-- 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');
+
+-- With owner
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'true');
+
+-- 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.39.5

---

From: Zsolt Parragi <[email-masked]>
Date: Tue, 24 Mar 2026 21:56:56 +0000

v2 is definitely better, I can confirm the improvements.

> including all suggested changes in this thread

But I want to point out that the permission check question, and the
role membership question is still open.

For the membership question

> I'm not opposed
> to your idea but IMO it should be an option.

I'm also fine with leaving it out, but then it should be a mentioned limitation.

For v2:

Shouldn't the tablespace function also support an owner option
similarly to the database function?

A pfree(nulls) and pfree(spcname) seem to be missing, all other
variables are freed properly.

+
+ /*

This part seems to be duplicated between the two functions, maybe
could be a helper?

Maybe it would be useful to also test the "SET search_path TO
myschema, public" variant, without quotes?

I also want to go back to the datestyle question one more time:

> The non-fixed DateStyle is by design. It mimics pg_dumpall.

Isn't pg_dump and pg_dumpall inconsistent in this? pg_dump sets it to
ISO, pg_dumpall uses DateStyle. So I'm not that sure about the
"pg_dumpall works this way" argument because pg_dump works
differently. Maybe either of those tools should also be fixed?

The pg_dump behavior is actually a bugfix in cf4cee1b, which was never
applied to pg_dumpall, so it seems like an oversight to me?

> At present, dates are put into a dump in the format specified by the
> default datestyle.  This is not portable between installations.
>
> This patch sets DATESTYLE to ISO at the start of a pg_dump, so that the
> dates written into the dump will be restorable onto any database,
> regardless of how its default datestyle is set.

---

From: Zsolt Parragi <[email-masked]>
Date: Wed, 1 Apr 2026 00:30:32 +0100

v3 looks good to me

---

From: Japin Li <[email-masked]>
Date: Thu, 02 Apr 2026 21:20:43 +0800

Hi, Andrew

On Tue, 31 Mar 2026 at 15:42, Andrew Dunstan <[email-masked]> wrote:
> On 2026-03-24 Tu 5:56 PM, Zsolt Parragi wrote:
> OK, I hope the attached set addresses all the outstanding
> issues. We're using ISO dates, and there are appropriate permissions
> checks. There is an option to dump role memberships.
>
>

Thanks for updating the v3 patches.  Here are some comments.

v3-0001
=======

1.
+               List       *namelist;
+               bool            first = true;
+
+               /* Parse string into list of identifiers */
+               if (!SplitGUCList(rawval, ',', &namelist))

According to SplitGUCList()'s comment, the caller should call list_free() on
the returned list even on error. Should we also call list_free() on namelist?

v3-0003
=======

1.
+       /* Add OWNER clause */
+       if (!no_owner)
+       {

The spcowner is only used within the if scope, so we can narrow its scope.

v3-0004
========

1.

I'm curious why WITH TEMPLATE = template0 is hardcoded. For example:

  [local]:1374846 postgres=# create database db01 IS_TEMPLATE true;
  CREATE DATABASE
  [local]:1374846 postgres=# create database db02 template db01;
  CREATE DATABASE
  [local]:1374846 postgres=# select pg_get_database_ddl('db02');
                                                 pg_get_database_ddl
  -----------------------------------------------------------------------------------------------------------------
   CREATE DATABASE db02 WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';
   ALTER DATABASE db02 OWNER TO japin;
  (2 rows)

Is this working as expected?

It seems there's no way to reconstruct the WITH TEMPLATE clause, right?
A comment here would help.

> [2. text/x-patch; v3-0001-Add-infrastructure-for-pg_get_-_ddl-functions.patch]...
>
> [3. text/x-patch; v3-0002-Add-pg_get_role_ddl-function.patch]...
>
> [4. text/x-patch; v3-0003-Add-pg_get_tablespace_ddl-function.patch]...
>
> [5. text/x-patch; v3-0004-Add-pg_get_database_ddl-function.patch]...

-- 
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 2 Apr 2026 06:35:59 -0700

On Thursday, April 2, 2026, Japin Li <[email-masked]> wrote:

>   ------------------------------------------------------------
> -----------------------------------------------------
>    CREATE DATABASE db02 WITH TEMPLATE = template0 ENCODING = 'UTF8'
> LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';

There is no way or use in constructing the original template clause, though
I agree it’s worth a comment.  At the end of the day the catalog data that
was found in the db01 database already exists in the db02 database when
executing these DLL reconstruction functions against the existing db02
database.  Taking nothing from the template is the correct behavior - hence
template0.

David J.

---

From: Andrew Dunstan <[email-masked]>
Date: Thu, 2 Apr 2026 12:27:21 -0400

On 2026-04-02 Th 9:35 AM, David G. Johnston wrote:
>        CREATE DATABASE db02 WITH TEMPLATE = template0 ENCODING =
>     'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';
> There is no way or use in constructing the original template clause, 
> though I agree it’s worth a comment.  At the end of the day the 
> catalog data that was found in the db01 database already exists in the 
> db02 database when executing these DLL reconstruction functions 
> against the existing db02 database. Taking nothing from the template 
> is the correct behavior - hence template0.
>
>

OK, here's a v4.

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com
From 09508ffa3a627f3fe92efeee1ae4b932bbf34cfe Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 09:50:41 -0400
Subject: [PATCH v4 1/4] Add infrastructure for pg_get_*_ddl functions

Add parse_ddl_options(), append_ddl_option(), and append_guc_value()
helper functions in a new ddlutils.c file that provide common option
parsing and output formatting for the pg_get_*_ddl family of functions
which will follow in later patches.  These accept VARIADIC text
arguments as alternating name/value pairs.

Author: Akshay Joshi <[email-masked]>
Co-authored-by: Andrew Dunstan <[email-masked]>
Co-authored-by: Euler Taveira <[email-masked]>
Discussion: https://postgr.es/m/CAKWEB6rmnmGKUA87Zmq-s=[email-masked]
Discussion: https://postgr.es/m/[email-masked]
Discussion: https://postgr.es/m/[email-masked]
Discussion: https://postgr.es/m/[email-masked]
---
 src/backend/utils/adt/Makefile    |   1 +
 src/backend/utils/adt/ddlutils.c  | 275 ++++++++++++++++++++++++++++++
 src/backend/utils/adt/meson.build |   1 +
 src/tools/pgindent/typedefs.list  |   2 +
 4 files changed, 279 insertions(+)
 create mode 100644 src/backend/utils/adt/ddlutils.c

index 00000000000..4bf7d9c38ae
@@ -0,0 +1,275 @@
+static void append_guc_value(StringInfo buf, const char *name,
+							 const char *value);
+ * append_guc_value
+ *		Append a GUC setting value to buf, handling GUC_LIST_QUOTE properly.
+ * Variables marked GUC_LIST_QUOTE were already fully quoted before they
+ * were stored in the setconfig array.  We break the list value apart
+ * and re-quote the elements as string literals.  For all other variables
+ * we simply quote the value as a single string literal.
+ * The caller has already appended "SET <name> TO " to buf.
+append_guc_value(StringInfo buf, const char *name, const char *value)
+	char	   *rawval;
+	rawval = pstrdup(value);
+	if (GetConfigOptionFlags(name, true) & GUC_LIST_QUOTE)
+			appendStringInfoString(buf, "NULL");
+				appendStringInfoString(buf, ", ");
+			appendStringInfoString(buf, quote_literal_cstr(curname));
+		list_free(namelist);
+		appendStringInfoString(buf, quote_literal_cstr(rawval));
+	pfree(rawval);
index 5bc517602b1..c1a5948b191 100644
@@ -626,6 +626,8 @@ DSMREntryType
 DSMRegistryCtxStruct
 DSMRegistryEntry
 DWORD
+DdlOptType
+DdlOption
 DataDirSyncMethod
 DataDumperPtr
 DataPageDeleteStack
-- 
2.43.0

From eab56e0ba2e06490cac4e654dc442d968deb96ac Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 09:52:25 -0400
Subject: [PATCH v4 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 options are supported: pretty (boolean) for
formatted output and memberships (boolean) to include GRANT statements
for role memberships and membership options. 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.
Password information is never included in the output.

The caller must have SELECT privilege on pg_authid.

Author: Mario Gonzalez <[email-masked]>
Author: Bryan Green <[email-masked]>
Co-authored-by: Andrew Dunstan <[email-masked]>
Co-authored-by: Euler Taveira <[email-masked]>
Reviewed-by: Japin Li <[email-masked]>
Reviewed-by: Quan Zongliang <[email-masked]>
Reviewed-by: jian he <[email-masked]>
Discussion: https://postgr.es/m/[email-masked]
Discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/func/func-info.sgml       |  56 ++++
 src/backend/utils/adt/ddlutils.c       | 361 +++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat        |   8 +
 src/test/regress/expected/role_ddl.out | 143 ++++++++++
 src/test/regress/parallel_schedule     |   2 +
 src/test/regress/sql/role_ddl.sql      |  96 +++++++
 6 files changed, 666 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..7cb020b6fd3 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3860,4 +3860,60 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+        The following options are supported: <literal>pretty</literal> (boolean)
+        for pretty-printed output and <literal>memberships</literal> (boolean,
+        default true) to include <command>GRANT</command> statements for
+        role memberships and their options.
index 4bf7d9c38ae..bcea1ec5981 100644
@@ -18,8 +18,25 @@
  */
 #include "postgres.h"
 
+#include "catalog/pg_auth_members.h"
 
 /* Option value types for DDL option parsing */
@@ -56,6 +73,8 @@ static void append_ddl_option(StringInfo buf, bool pretty, int indent,
 			pg_attribute_printf(4, 5);
 static void append_guc_value(StringInfo buf, const char *name,
 							 const char *value);
+static List *pg_get_role_ddl_internal(Oid roleid, bool pretty,
+									  bool memberships);

 /*
@@ -273,3 +292,345 @@ append_guc_value(StringInfo buf, const char *name, const char *value)
 
 	pfree(rawval);
 }
+
+/*
+ * 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.  If memberships is true,
+ * GRANT statements for role memberships are appended.
+ */
+static List *
+pg_get_role_ddl_internal(Oid roleid, bool pretty, bool memberships)
+{
+	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));
+
+	/* User must have SELECT privilege on pg_authid. */
+	if (pg_class_aclcheck(AuthIdRelationId, GetUserId(), ACL_SELECT) != ACLCHECK_OK)
+	{
+		ReleaseSysCache(tuple);
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied for role %s", 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, USE_ISO_DATES, 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  *role_settings;
+		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;
+
+		role_settings = DatumGetArrayTypeP(datum);
+
+		deconstruct_array_builtin(role_settings, 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));
+
+			append_guc_value(&buf, s, p);
+
+			appendStringInfoChar(&buf, ';');
+
+			statements = lappend(statements, pstrdup(buf.data));
+
+			pfree(s);
+		}
+
+		pfree(settings);
+		pfree(nulls);
+		pfree(role_settings);
+
+		if (datname != NULL)
+			pfree(datname);
+	}
+
+	systable_endscan(scan);
+	table_close(rel, AccessShareLock);
+
+	/*
+	 * Scan pg_auth_members for role memberships.  We look for rows where
+	 * member = roleid, meaning this role has been granted membership in other
+	 * roles.
+	 */
+	if (memberships)
+	{
+		rel = table_open(AuthMemRelationId, AccessShareLock);
+		ScanKeyInit(&scankey,
+					Anum_pg_auth_members_member,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(roleid));
+		scan = systable_beginscan(rel, AuthMemMemRoleIndexId, true,
+								  NULL, 1, &scankey);
+
+		while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+		{
+			Form_pg_auth_members memform = (Form_pg_auth_members) GETSTRUCT(tuple);
+			char	   *granted_role;
+			char	   *grantor;
+
+			granted_role = GetUserNameFromId(memform->roleid, false);
+			grantor = GetUserNameFromId(memform->grantor, false);
+
+			resetStringInfo(&buf);
+			appendStringInfo(&buf, "GRANT %s TO %s",
+							 quote_identifier(granted_role),
+							 quote_identifier(rolname));
+			appendStringInfo(&buf, " WITH ADMIN %s, INHERIT %s, SET %s",
+							 memform->admin_option ? "TRUE" : "FALSE",
+							 memform->inherit_option ? "TRUE" : "FALSE",
+							 memform->set_option ? "TRUE" : "FALSE");
+			appendStringInfo(&buf, " GRANTED BY %s;",
+							 quote_identifier(grantor));
+
+			statements = lappend(statements, pstrdup(buf.data));
+
+			pfree(granted_role);
+			pfree(grantor);
+		}
+
+		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
+ * and optionally GRANT statements for role memberships.
+ * Returns no rows if the role argument is NULL.
+ */
+Datum
+pg_get_role_ddl(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+	List	   *statements;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext oldcontext;
+		Oid			roleid;
+		DdlOption	opts[] = {
+			{"pretty", DDL_OPT_BOOL},
+			{"memberships", 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,
+											  !opts[1].isset || opts[1].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 = list_nth(statements, funcctx->call_cntr);
+
+		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 3579cec5744..d1985826263 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8603,6 +8603,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..575111da55c
--- /dev/null
+++ b/src/test/regress/expected/role_ddl.out
@@ -0,0 +1,143 @@
+-- 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
+-- Role with memberships
+CREATE ROLE regress_role_ddl_grantor CREATEROLE;
+CREATE ROLE regress_role_ddl_group1;
+CREATE ROLE regress_role_ddl_group2;
+CREATE ROLE regress_role_ddl_member;
+GRANT regress_role_ddl_group1 TO regress_role_ddl_grantor WITH ADMIN TRUE;
+GRANT regress_role_ddl_group2 TO regress_role_ddl_grantor WITH ADMIN TRUE;
+SET ROLE regress_role_ddl_grantor;
+GRANT regress_role_ddl_group1 TO regress_role_ddl_member WITH INHERIT TRUE, SET FALSE;
+GRANT regress_role_ddl_group2 TO regress_role_ddl_member WITH ADMIN TRUE;
+RESET ROLE;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_member');
+                                                             pg_get_role_ddl                                                             
+-----------------------------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_member NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+ GRANT regress_role_ddl_group1 TO regress_role_ddl_member WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_role_ddl_grantor;
+ GRANT regress_role_ddl_group2 TO regress_role_ddl_member WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_role_ddl_grantor;
+(3 rows)
+
+-- Role with memberships suppressed
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_member', 'memberships', 'false');
+                                                  pg_get_role_ddl                                                   
+--------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_member NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- Non-existent role (should error)
+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)
+
+-- Permission check: revoke SELECT on pg_authid
+CREATE ROLE regress_role_ddl_noaccess;
+REVOKE SELECT ON pg_authid FROM PUBLIC;
+SET ROLE regress_role_ddl_noaccess;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1');  -- should fail
+ERROR:  permission denied for role regress_role_ddl_test1
+RESET ROLE;
+GRANT SELECT ON pg_authid TO PUBLIC;
+DROP ROLE regress_role_ddl_noaccess;
+-- 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";
+SET ROLE regress_role_ddl_grantor;
+REVOKE regress_role_ddl_group1 FROM regress_role_ddl_member;
+REVOKE regress_role_ddl_group2 FROM regress_role_ddl_member;
+RESET ROLE;
+DROP ROLE regress_role_ddl_member;
+DROP ROLE regress_role_ddl_group1;
+DROP ROLE regress_role_ddl_group2;
+DROP ROLE regress_role_ddl_grantor;
+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 3a044ffd8bf..84efbf8c104 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..3d0142242ec
--- /dev/null
+++ b/src/test/regress/sql/role_ddl.sql
@@ -0,0 +1,96 @@
+-- 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
+
+-- Role with memberships
+CREATE ROLE regress_role_ddl_grantor CREATEROLE;
+CREATE ROLE regress_role_ddl_group1;
+CREATE ROLE regress_role_ddl_group2;
+CREATE ROLE regress_role_ddl_member;
+GRANT regress_role_ddl_group1 TO regress_role_ddl_grantor WITH ADMIN TRUE;
+GRANT regress_role_ddl_group2 TO regress_role_ddl_grantor WITH ADMIN TRUE;
+SET ROLE regress_role_ddl_grantor;
+GRANT regress_role_ddl_group1 TO regress_role_ddl_member WITH INHERIT TRUE, SET FALSE;
+GRANT regress_role_ddl_group2 TO regress_role_ddl_member WITH ADMIN TRUE;
+RESET ROLE;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_member');
+
+-- Role with memberships suppressed
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_member', 'memberships', 'false');
+
+-- Non-existent role (should error)
+SELECT * FROM pg_get_role_ddl(9999999::oid);
+
+-- NULL input (should return no rows)
+SELECT * FROM pg_get_role_ddl(NULL);
+
+-- Permission check: revoke SELECT on pg_authid
+CREATE ROLE regress_role_ddl_noaccess;
+REVOKE SELECT ON pg_authid FROM PUBLIC;
+SET ROLE regress_role_ddl_noaccess;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1');  -- should fail
+RESET ROLE;
+GRANT SELECT ON pg_authid TO PUBLIC;
+DROP ROLE regress_role_ddl_noaccess;
+
+-- 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";
+SET ROLE regress_role_ddl_grantor;
+REVOKE regress_role_ddl_group1 FROM regress_role_ddl_member;
+REVOKE regress_role_ddl_group2 FROM regress_role_ddl_member;
+RESET ROLE;
+DROP ROLE regress_role_ddl_member;
+DROP ROLE regress_role_ddl_group1;
+DROP ROLE regress_role_ddl_group2;
+DROP ROLE regress_role_ddl_grantor;
+
+DROP DATABASE regression_role_ddl_test;
+
+-- Reset timezone to default
+RESET timezone;
-- 
2.43.0

From b3ac20e3c091a3c3f21eccf73cde0421adaaeb36 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 09:55:16 -0400
Subject: [PATCH v4 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 options are supported: pretty
(boolean) for formatted output and owner (boolean) to include OWNER.
(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.

The caller must have SELECT privilege on pg_tablespace.

Author: Nishant Sharma <[email-masked]>
Author: Manni Wood <[email-masked]>
Co-authored-by: Andrew Dunstan <[email-masked]>
Co-authored-by: Euler Taveira <[email-masked]>
Reviewed-by: Jim Jones <[email-masked]>
Reviewed-by: Álvaro Herrera <[email-masked]>
Reviewed-by: Chao Li <[email-masked]>
Discussion: https://postgr.es/m/CAKWEB6rmnmGKUA87Zmq-s=[email-masked]
Discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/func/func-info.sgml             |  28 +++
 src/backend/utils/adt/ddlutils.c             | 206 ++++++++++++++++++-
 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 |  84 ++++++++
 src/test/regress/parallel_schedule           |   2 +-
 src/test/regress/sql/tablespace_ddl.sql      |  58 ++++++
 8 files changed, 395 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 7cb020b6fd3..e14c209bf14 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3910,6 +3910,34 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         role memberships and their options.
        </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 options are supported: <literal>pretty</literal> (boolean)
+        for formatted output and <literal>owner</literal> (boolean) to include
+        <literal>OWNER</literal>.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index bcea1ec5981..d953963a712 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -20,11 +20,13 @@
 
 #include "access/genam.h"
 #include "access/htup_details.h"
-#include "access/relation.h"
 #include "access/table.h"
 #include "catalog/pg_auth_members.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"
@@ -35,6 +37,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"
@@ -75,6 +78,8 @@ static void append_guc_value(StringInfo buf, const char *name,
 							 const char *value);
 static List *pg_get_role_ddl_internal(Oid roleid, bool pretty,
 									  bool memberships);
+static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner);
+static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);

 /*
@@ -634,3 +639,202 @@ pg_get_role_ddl(PG_FUNCTION_ARGS)
+pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner)
+	/* User must have SELECT privilege on pg_tablespace. */
+	if (pg_class_aclcheck(TableSpaceRelationId, GetUserId(), ACL_SELECT) != ACLCHECK_OK)
+		aclcheck_error(ACLCHECK_NO_PRIV, OBJECT_TABLESPACE, spcname);
+	pfree(spcname);
+													opts[1].isset && !opts[1].boolval);
index aec5556b008..35083fcc733 100644
@@ -547,7 +547,7 @@ static void add_cast_to(StringInfo buf, Oid typid);
@@ -14240,7 +14240,7 @@ string_to_text(char *str)
index d1985826263..984cf620bc8 100644
index 059e6474f3b..25c05e2f649 100644

diff --git a/src/test/regress/expected/tablespace_ddl.out b/src/test/regress/expected/tablespace_ddl.out
new file mode 100644
index 00000000000..e52043273a9
--- /dev/null
+++ b/src/test/regress/expected/tablespace_ddl.out
@@ -0,0 +1,84 @@
+--
+-- 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
+-- tablespace with owner suppressed
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'owner', 'false');
+                                                                       pg_get_tablespace_ddl                                                                       
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLESPACE regress_allopt_tblsp 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)
+
+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;
+-- Permission check: revoke SELECT on pg_tablespace
+CREATE TABLESPACE regress_acl_tblsp OWNER regress_tblspc_ddl_user LOCATION '';
+CREATE ROLE regress_tblspc_ddl_noaccess;
+REVOKE SELECT ON pg_tablespace FROM PUBLIC;
+SET ROLE regress_tblspc_ddl_noaccess;
+SELECT * FROM pg_get_tablespace_ddl('regress_acl_tblsp');  -- should fail
+ERROR:  permission denied for tablespace regress_acl_tblsp
+RESET ROLE;
+GRANT SELECT ON pg_tablespace TO PUBLIC;
+DROP TABLESPACE regress_acl_tblsp;
+DROP ROLE regress_tblspc_ddl_noaccess;
+DROP ROLE regress_tblspc_ddl_user;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 84efbf8c104..fabaebf2c78 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
 
 # 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..ee3cc6e2e1e
--- /dev/null
+++ b/src/test/regress/sql/tablespace_ddl.sql
@@ -0,0 +1,58 @@
+--
+-- 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
+
+-- tablespace with owner suppressed
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'owner', 'false');
+
+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;
+
+-- Permission check: revoke SELECT on pg_tablespace
+CREATE TABLESPACE regress_acl_tblsp OWNER regress_tblspc_ddl_user LOCATION '';
+CREATE ROLE regress_tblspc_ddl_noaccess;
+REVOKE SELECT ON pg_tablespace FROM PUBLIC;
+SET ROLE regress_tblspc_ddl_noaccess;
+SELECT * FROM pg_get_tablespace_ddl('regress_acl_tblsp');  -- should fail
+RESET ROLE;
+GRANT SELECT ON pg_tablespace TO PUBLIC;
+DROP TABLESPACE regress_acl_tblsp;
+DROP ROLE regress_tblspc_ddl_noaccess;
+
+DROP ROLE regress_tblspc_ddl_user;
-- 
2.43.0

From 3a0f3c76f1c35ffa56295bf1ea9a2d2c8efce928 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Thu, 19 Mar 2026 09:57:35 -0400
Subject: [PATCH v4 4/4] Add pg_get_database_ddl() function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

The caller must have CONNECT privilege on the target database.

Author: Akshay Joshi <[email-masked]>
Co-authored-by: Andrew Dunstan <[email-masked]>
Co-authored-by: Euler Taveira <[email-masked]>
Reviewed-by: Japin Li <[email-masked]>
Reviewed-by: Chao Li <[email-masked]>
Reviewed-by: Álvaro Herrera <[email-masked]>
Reviewed-by: Quan Zongliang <[email-masked]>
Discussion: https://postgr.es/m/[email-masked]
Discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/func/func-info.sgml           |  23 ++
 src/backend/utils/adt/ddlutils.c           | 330 +++++++++++++++++++++
 src/include/catalog/pg_proc.dat            |   8 +
 src/test/regress/expected/database_ddl.out |  88 ++++++
 src/test/regress/parallel_schedule         |   2 +-
 src/test/regress/sql/database_ddl.sql      |  66 +++++
 6 files changed, 516 insertions(+), 1 deletion(-)
 create mode 100644 src/test/regress/expected/database_ddl.out
 create mode 100644 src/test/regress/sql/database_ddl.sql

index e14c209bf14..80cf11083d6 100644
@@ -3938,6 +3938,29 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         <literal>OWNER</literal>.
index d953963a712..5ff15bc2cf1 100644
@@ -23,11 +23,14 @@
 #include "catalog/pg_auth_members.h"
@@ -36,6 +39,7 @@
@@ -80,6 +84,8 @@ static List *pg_get_role_ddl_internal(Oid roleid, bool pretty,
 									  bool memberships);
 static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner);

 /*
@@ -838,3 +844,327 @@ pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS)
 
+				 errmsg("database \"%s\" is a system database", dbname),
+			 errdetail("DDL generation is not supported for template0 and template1.")));
+	 * Always use template0: the target database already contains the catalog
+	 * data from whatever template was used originally, so we must start from
+	 * the pristine template to avoid duplication.
+	collate = isnull ? NULL : TextDatumGetCString(datum);
+	ctype = isnull ? NULL : TextDatumGetCString(datum);
+	if (collate != NULL && ctype != NULL && strcmp(collate, ctype) == 0)
+		if (collate != NULL)
+		if (ctype != NULL)
+		ArrayType  *dbconfig;
+		dbconfig = DatumGetArrayTypeP(datum);
+		deconstruct_array_builtin(dbconfig, TEXTOID, &settings, &nulls, &nsettings);
+			append_guc_value(&buf, s, p);
+		pfree(nulls);
+		pfree(dbconfig);
index 984cf620bc8..787deb82a3a 100644
index 00000000000..5081c1a2b53
@@ -0,0 +1,88 @@
+-- To produce stable regression test output, strip locale/collation details
+-- from the DDL output.  Uses a plain SQL function to avoid a PL/pgSQL
+-- dependency.
+RETURNS TEXT LANGUAGE sql AS $$
+SELECT regexp_replace(
+  regexp_replace(
+    regexp_replace(
+      regexp_replace(
+        regexp_replace(
+          '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)', '', 'gi'),
+        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1', '', 'gi'),
+      '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1', '', 'gi'),
+    '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1', '', 'gi'),
+  '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1', '', 'gi')
+$$;
+-- Permission check: revoke CONNECT on database
+CREATE ROLE regress_db_ddl_noaccess;
+REVOKE CONNECT ON DATABASE regress_database_ddl FROM PUBLIC;
+SET ROLE regress_db_ddl_noaccess;
+SELECT * FROM pg_get_database_ddl('regress_database_ddl');  -- should fail
+ERROR:  permission denied for database regress_database_ddl
+RESET ROLE;
+GRANT CONNECT ON DATABASE regress_database_ddl TO PUBLIC;
+DROP ROLE regress_db_ddl_noaccess;
index fabaebf2c78..cc365393bb7 100644

 # 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..093ccc0029e
--- /dev/null
+++ b/src/test/regress/sql/database_ddl.sql
@@ -0,0 +1,66 @@
+--
+-- Tests for pg_get_database_ddl()
+--
+
+-- To produce stable regression test output, strip locale/collation details
+-- from the DDL output.  Uses a plain SQL function to avoid a PL/pgSQL
+-- dependency.
+
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT LANGUAGE sql AS $$
+SELECT regexp_replace(
+  regexp_replace(
+    regexp_replace(
+      regexp_replace(
+        regexp_replace(
+          ddl_input,
+          '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)', '', 'gi'),
+        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1', '', 'gi'),
+      '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1', '', 'gi'),
+    '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1', '', 'gi'),
+  '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1', '', 'gi')
+$$;
+
+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;
+ALTER DATABASE regress_database_ddl SET random_page_cost = 2.0;
+ALTER ROLE regress_datdba IN DATABASE regress_database_ddl SET random_page_cost = 1.1;
+
+-- 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');
+
+-- With owner
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'true');
+
+-- 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
+
+-- Permission check: revoke CONNECT on database
+CREATE ROLE regress_db_ddl_noaccess;
+REVOKE CONNECT ON DATABASE regress_database_ddl FROM PUBLIC;
+SET ROLE regress_db_ddl_noaccess;
+SELECT * FROM pg_get_database_ddl('regress_database_ddl');  -- should fail
+RESET ROLE;
+GRANT CONNECT ON DATABASE regress_database_ddl TO PUBLIC;
+DROP ROLE regress_db_ddl_noaccess;
+
+DROP DATABASE regress_database_ddl;
+DROP FUNCTION ddl_filter(text);
+DROP ROLE regress_datdba;
-- 
2.43.0

---

From: Andrew Dunstan <[email-masked]>
Date: Sun, 5 Apr 2026 11:06:09 -0400

On 2026-04-02 Th 12:27 PM, Andrew Dunstan wrote:
>> catalog data that was found in the db01 database already exists in 
>> the db02 database when executing these DLL reconstruction functions 

Pushed. I have moved the remaining get_*_ddl items to PG20-1

cheers

andrew

---

From: Andres Freund <[email-masked]>
Date: Sun, 5 Apr 2026 11:40:33 -0400

Hi,

> Pushed. I have moved the remaining get_*_ddl items to PG20-1

https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=longfin&dt=2026-04-05%2015%3A04%3A04

diff -U3 /Users/buildfarm/bf-data/HEAD/pgsql.build/src/test/regress/expected/database_ddl.out /Users/buildfarm/bf-data/HEAD/pgsql.build/src/test/regress/results/database_ddl.out
--- /Users/buildfarm/bf-data/HEAD/pgsql.build/src/test/regress/expected/database_ddl.out	2026-04-05 11:04:08
+++ /Users/buildfarm/bf-data/HEAD/pgsql.build/src/test/regress/results/database_ddl.out	2026-04-05 11:05:57
@@ -22,6 +22,7 @@
 CREATE DATABASE regress_database_ddl
     ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0
     OWNER regress_datdba;
+WARNING:  databases created by regression test cases should have names including "regression"
 ALTER DATABASE regress_database_ddl CONNECTION_LIMIT 123;
 ALTER DATABASE regress_database_ddl SET random_page_cost = 2.0;
 ALTER ROLE regress_datdba IN DATABASE regress_database_ddl SET random_page_cost = 1.1;

But do we really have to create a new database and a new tablespace for these?
Database and tablespace creations are quite heavyweight operations.

We already have an existing tablespace and an existing database as part of the
regression tests. Couldn't you make do with those?

Greetings,

Anres

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Sun, 5 Apr 2026 18:35:34 +0200

> Pushed. I have moved the remaining get_*_ddl items to PG20-1

+1 on having  this feature in general. But I'm not sure I understand
why it needs the whole bespoke string-based option parsing in the
first commit. Why not use named arguments for this, i.e. have the
usage syntax be:

SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3', pretty => true);

Instead of the current:

SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3', 'pretty', 'true');

---

From: Andres Freund <[email-masked]>
Date: Sun, 5 Apr 2026 16:03:49 -0400

Hi,

> +++ /Users/buildfarm/bf-data/HEAD/pgsql.build/src/test/regress/results/database_ddl.out	2026-04-05 11:05:57

Pushed a fixup for this and the pgindent failure, as it doesn't seem like a
great time to have CI/BF fail.

It is pretty odd that the naming restrictions for databases (regression*) is
different than for all the other object types...

> regression tests. Couldn't you make do with those?

Didn't do anything about that.

Greetings,

Andres Freund

---

From: Andrew Dunstan <[email-masked]>
Date: Mon, 6 Apr 2026 07:39:44 -0400

> great time to have CI/BF fail.

Thanks for that. I'm not sure how my test regime managed to miss either. 
I will work on that.

> different than for all the other object types...

Yeah.

> Didn't do anything about that.

Well, the trouble is that the database test runs a bunch of alter and 
revoke statements on the created database, that we probably don't want 
to persist on the existing regression database. I could see an argument 
for converting this to a TAP test that would only be run once, given our 
current very profligate running of the core regression suite. That goes 
doubly for the tablespace test, which could also probably use ALTER 
TABLESPACE instead of creating a bunch of tablespaces and then dropping 
them.

cheers

andrew

---

From: Andrew Dunstan <[email-masked]>
Date: Mon, 6 Apr 2026 07:55:02 -0400

> SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3', 'pretty', 'true');

There was quite a deal of discussion around this mechanism. See Euler's 
review at [1] and follow-up at [2] for the original discussion of the 
VARIADIC option-parsing design and the use cases it was meant to 
address. I'm prepared to revisit it is there's a strong consensus on the 
point.

cheers

andrew

[1] 
https://www.postgresql.org/message-id/4e60bcae-8222-4e1f-8e5b-d73b59c93304%40app.fastmail.com 

[2] 
https://www.postgresql.org/message-id/4c695e76-5ab7-449f-8060-76518dd41468%40app.fastmail.com

---

From: "Jelte Fennema-Nio" <[email-masked]>
Date: Mon, 06 Apr 2026 17:24:06 +0200

> address. I'm prepared to revisit it is there's a strong consensus on the

Thanks for those links. I had not seen that part of the discussion. But
I only see an explanation of why these functions are configurable with
optional key+value pairs in their arguments. I think that makes sense,
and I totally agree that we should do that.

The thing I'm questioning is whether we need a new way of providing
key+value pairs as optional arguments to functions. IMO we already had a
perfectly fine one. Introducing another adds complexity (both to the
code and to the user) and I don't see any compelling reason to do so.

Attached is a patch with roughly what I have in mind instead. By doing
this we can also make the functinos STRICT, so that we don't have to
worry about handling NULL values for the first argument.

Afaict this named parameter approach only has benefits over the VARIADIC
argument one. But if I'm wrong about that, please let me know.
From 72c580c6ec1e8e5bc29c6edfb11afb8e062de2b5 Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 6 Apr 2026 17:05:27 +0200
Subject: [PATCH v5] Use named arguments for pg_get_*_ddl() functions

In 4881981f92024 some infrastructure was introduced to allow optional
key-value pairs as arguments. This removes that infrastructure in favor
of named arguments. No custom parsing and typechecking logic is needed
this way.
---
 doc/src/sgml/func/func-info.sgml             |  53 ++--
 src/backend/utils/adt/ddlutils.c             | 256 ++-----------------
 src/include/catalog/pg_proc.dat              |  36 ++-
 src/test/regress/expected/database_ddl.out   |  10 +-
 src/test/regress/expected/role_ddl.out       |   4 +-
 src/test/regress/expected/tablespace_ddl.out |   4 +-
 src/test/regress/sql/database_ddl.sql        |   9 +-
 src/test/regress/sql/role_ddl.sql            |   4 +-
 src/test/regress/sql/tablespace_ddl.sql      |   4 +-
 src/tools/pgindent/typedefs.list             |   2 -
 10 files changed, 76 insertions(+), 306 deletions(-)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 80cf11083d6..adf2bb93c0e 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3895,8 +3895,10 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         </indexterm>
         <function>pg_get_role_ddl</function>
         ( <parameter>role</parameter> <type>regrole</type>
-        <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
-        <type>text</type> </optional> )
+        <optional>, <parameter>pretty</parameter> <type>boolean</type>
+        <literal>DEFAULT false</literal> </optional>
+        <optional>, <parameter>memberships</parameter> <type>boolean</type>
+        <literal>DEFAULT true</literal> </optional> )
         <returnvalue>setof text</returnvalue>
        </para>
        <para>
@@ -3904,10 +3906,11 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         <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 options are supported: <literal>pretty</literal> (boolean)
-        for pretty-printed output and <literal>memberships</literal> (boolean,
-        default true) to include <command>GRANT</command> statements for
-        role memberships and their options.
+        If <parameter>pretty</parameter> is true, the output is formatted with
+        newlines and indentation.
+        If <parameter>memberships</parameter> is true (the default),
+        <command>GRANT</command> statements for role memberships and their
+        options are included.
        </para></entry>
       </row>
       <row>
@@ -3917,15 +3920,19 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         </indexterm>
         <function>pg_get_tablespace_ddl</function>
         ( <parameter>tablespace</parameter> <type>oid</type>
-        <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
-        <type>text</type> </optional> )
+        <optional>, <parameter>pretty</parameter> <type>boolean</type>
+        <literal>DEFAULT false</literal> </optional>
+        <optional>, <parameter>owner</parameter> <type>boolean</type>
+        <literal>DEFAULT true</literal> </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> )
+        <optional>, <parameter>pretty</parameter> <type>boolean</type>
+        <literal>DEFAULT false</literal> </optional>
+        <optional>, <parameter>owner</parameter> <type>boolean</type>
+        <literal>DEFAULT true</literal> </optional> )
         <returnvalue>setof text</returnvalue>
        </para>
        <para>
@@ -3933,9 +3940,10 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         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 options are supported: <literal>pretty</literal> (boolean)
-        for formatted output and <literal>owner</literal> (boolean) to include
-        <literal>OWNER</literal>.
+        If <parameter>pretty</parameter> is true, the output is formatted with
+        newlines and indentation.
+        If <parameter>owner</parameter> is true (the default), an
+        <literal>OWNER</literal> clause is included.
        </para></entry>
       </row>
       <row>
@@ -3945,8 +3953,12 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         </indexterm>
         <function>pg_get_database_ddl</function>
         ( <parameter>database</parameter> <type>regdatabase</type>
-        <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
-        <type>text</type> </optional> )
+        <optional>, <parameter>pretty</parameter> <type>boolean</type>
+        <literal>DEFAULT false</literal> </optional>
+        <optional>, <parameter>owner</parameter> <type>boolean</type>
+        <literal>DEFAULT true</literal> </optional>
+        <optional>, <parameter>tablespace</parameter> <type>boolean</type>
+        <literal>DEFAULT true</literal> </optional> )
         <returnvalue>setof text</returnvalue>
        </para>
        <para>
@@ -3954,11 +3966,12 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         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>.
+        If <parameter>pretty</parameter> is true, the output is formatted with
+        newlines and indentation.
+        If <parameter>owner</parameter> is true (the default), an
+        <literal>OWNER</literal> clause is included.
+        If <parameter>tablespace</parameter> is true (the default), a
+        <literal>TABLESPACE</literal> clause is included.
        </para></entry>
       </row>
      </tbody>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index b16c277d000..f19901d7523 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -39,42 +39,12 @@
 #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"
 #include "utils/timestamp.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 */
-	/* fields for specific option types */
-	union
-	{
-		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);
@@ -83,150 +53,11 @@ static void append_guc_value(StringInfo buf, const char *name,
 static List *pg_get_role_ddl_internal(Oid roleid, bool pretty,
 									  bool memberships);
 static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner);
-static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);
+static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid);
 static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
 										  bool no_owner, bool no_tablespace);

-/*
- * 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;
-		switch (opts[i].type)
-		{
-			case DDL_OPT_BOOL:
-				opts[i].boolval = false;
-				break;
-			case DDL_OPT_TEXT:
-				opts[i].textval = NULL;
-				break;
-			case DDL_OPT_INT:
-				opts[i].intval = 0;
-				break;
-		}
-	}
-
-	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.
  */
@@ -601,27 +432,13 @@ pg_get_role_ddl(PG_FUNCTION_ARGS)
 	if (SRF_IS_FIRSTCALL())
 	{
 		MemoryContext oldcontext;
-		Oid			roleid;
-		DdlOption	opts[] = {
-			{"pretty", DDL_OPT_BOOL},
-			{"memberships", 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,
-											  !opts[1].isset || opts[1].boolval);
+		statements = pg_get_role_ddl_internal(PG_GETARG_OID(0),
+											  PG_GETARG_BOOL(1),
+											  PG_GETARG_BOOL(2));
 		funcctx->user_fctx = statements;
 		funcctx->max_calls = list_length(statements);
 
@@ -755,7 +572,7 @@ pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner)
  * pg_get_tablespace_ddl_srf - common SRF logic for tablespace DDL
  */
 static Datum
-pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull)
+pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid)
 {
 	FuncCallContext *funcctx;
 	List	   *statements;
@@ -763,25 +580,13 @@ pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull)
 	if (SRF_IS_FIRSTCALL())
 	{
 		MemoryContext oldcontext;
-		DdlOption	opts[] = {
-			{"pretty", DDL_OPT_BOOL},
-			{"owner", 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,
-													opts[1].isset && !opts[1].boolval);
+													PG_GETARG_BOOL(1),
+													!PG_GETARG_BOOL(2));
 		funcctx->user_fctx = statements;
 		funcctx->max_calls = list_length(statements);
 
@@ -813,14 +618,7 @@ pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull)
 Datum
 pg_get_tablespace_ddl_oid(PG_FUNCTION_ARGS)
 {
-	Oid			tsid = InvalidOid;
-	bool		isnull;
-
-	isnull = PG_ARGISNULL(0);
-	if (!isnull)
-		tsid = PG_GETARG_OID(0);
-
-	return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull);
+	return pg_get_tablespace_ddl_srf(fcinfo, PG_GETARG_OID(0));
 }
 
 /*
@@ -830,19 +628,10 @@ pg_get_tablespace_ddl_oid(PG_FUNCTION_ARGS)
 Datum
 pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS)
 {
-	Oid			tsid = InvalidOid;
-	Name		tspname;
-	bool		isnull;
+	Name		tspname = PG_GETARG_NAME(0);
 
-	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);
+	return pg_get_tablespace_ddl_srf(fcinfo,
+									 get_tablespace_oid(NameStr(*tspname), false));
 }
 
 /*
@@ -1122,29 +911,14 @@ pg_get_database_ddl(PG_FUNCTION_ARGS)
 	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);
+		statements = pg_get_database_ddl_internal(PG_GETARG_OID(0),
+												  PG_GETARG_BOOL(1),
+												  !PG_GETARG_BOOL(2),
+												  !PG_GETARG_BOOL(3));
 		funcctx->user_fctx = statements;
 		funcctx->max_calls = list_length(statements);
 
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3ea17fc5629..1b5b6a683d3 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8610,36 +8610,32 @@
   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',
+  proname => 'pg_get_role_ddl',
   provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
-  proargtypes => 'regrole text',
-  proargmodes => '{i,v}',
-  proallargtypes => '{regrole,text}',
-  pronargdefaults => '1', proargdefaults => '{NULL}',
+  proargtypes => 'regrole bool bool',
+  proargnames => '{roleid,pretty,memberships}',
+  pronargdefaults => '2', proargdefaults => '{false,true}',
   prosrc => 'pg_get_role_ddl' },
 { oid => '8758', descr => 'get DDL to recreate a tablespace',
-  proname => 'pg_get_tablespace_ddl', provariadic => 'text', proisstrict => 'f',
+  proname => 'pg_get_tablespace_ddl',
   provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
-  proargtypes => 'oid text',
-  proargmodes => '{i,v}',
-  proallargtypes => '{oid,text}',
-  pronargdefaults => '1', proargdefaults => '{NULL}',
+  proargtypes => 'oid bool bool',
+  proargnames => '{tablespace_oid,pretty,owner}',
+  pronargdefaults => '2', proargdefaults => '{false,true}',
   prosrc => 'pg_get_tablespace_ddl_oid' },
 { oid => '8759', descr => 'get DDL to recreate a tablespace',
-  proname => 'pg_get_tablespace_ddl', provariadic => 'text', proisstrict => 'f',
+  proname => 'pg_get_tablespace_ddl',
   provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
-  proargtypes => 'name text',
-  proargmodes => '{i,v}',
-  proallargtypes => '{name,text}',
-  pronargdefaults => '1', proargdefaults => '{NULL}',
+  proargtypes => 'name bool bool',
+  proargnames => '{tablespace_name,pretty,owner}',
+  pronargdefaults => '2', proargdefaults => '{false,true}',
   prosrc => 'pg_get_tablespace_ddl_name' },
 { oid => '8762', descr => 'get DDL to recreate a database',
-  proname => 'pg_get_database_ddl', provariadic => 'text', proisstrict => 'f',
+  proname => 'pg_get_database_ddl',
   provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
-  proargtypes => 'regdatabase text',
-  proargmodes => '{i,v}',
-  proallargtypes => '{regdatabase,text}',
-  pronargdefaults => '1', proargdefaults => '{NULL}',
+  proargtypes => 'regdatabase bool bool bool',
+  proargnames => '{database,pretty,owner,tablespace}',
+  pronargdefaults => '3', proargdefaults => '{false,true,true}',
   prosrc => 'pg_get_database_ddl' },
 { oid => '2509',
   descr => 'deparse an encoded expression with pretty-print option',
diff --git a/src/test/regress/expected/database_ddl.out b/src/test/regress/expected/database_ddl.out
index 97657e52cfa..67332212f56 100644
--- a/src/test/regress/expected/database_ddl.out
+++ b/src/test/regress/expected/database_ddl.out
@@ -36,12 +36,6 @@ SELECT * FROM pg_get_database_ddl(NULL);
 ---------------------
 (0 rows)
 
--- Invalid option value (should error)
-SELECT * FROM pg_get_database_ddl('regression_database_ddl', 'owner', 'invalid');
-ERROR:  invalid value for boolean option "owner": invalid
--- Duplicate option (should error)
-SELECT * FROM pg_get_database_ddl('regression_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('regression_database_ddl');
                                       ddl_filter                                      
@@ -53,7 +47,7 @@ SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_data
 (4 rows)
 
 -- With owner
-SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl', 'owner', 'true');
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl', owner => true);
                                       ddl_filter                                      
 --------------------------------------------------------------------------------------
  CREATE DATABASE regression_database_ddl WITH TEMPLATE = template0 ENCODING = 'UTF8';
@@ -64,7 +58,7 @@ SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_data
 
 -- Pretty-printed output
 \pset format unaligned
-SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl', 'pretty', 'true', 'tablespace', 'false');
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl', pretty => true, tablespace => false);
 ddl_filter
 CREATE DATABASE regression_database_ddl
     WITH TEMPLATE = template0
diff --git a/src/test/regress/expected/role_ddl.out b/src/test/regress/expected/role_ddl.out
index 575111da55c..e87e168e1f5 100644
--- a/src/test/regress/expected/role_ddl.out
+++ b/src/test/regress/expected/role_ddl.out
@@ -65,7 +65,7 @@ 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');
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3', pretty => true);
 pg_get_role_ddl
 CREATE ROLE regress_role_ddl_test3
     SUPERUSER
@@ -99,7 +99,7 @@ SELECT * FROM pg_get_role_ddl('regress_role_ddl_member');
 (3 rows)
 
 -- Role with memberships suppressed
-SELECT * FROM pg_get_role_ddl('regress_role_ddl_member', 'memberships', 'false');
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_member', memberships => false);
                                                   pg_get_role_ddl                                                   
 --------------------------------------------------------------------------------------------------------------------
  CREATE ROLE regress_role_ddl_member NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
diff --git a/src/test/regress/expected/tablespace_ddl.out b/src/test/regress/expected/tablespace_ddl.out
index e52043273a9..4f0f4401d2f 100644
--- a/src/test/regress/expected/tablespace_ddl.out
+++ b/src/test/regress/expected/tablespace_ddl.out
@@ -43,7 +43,7 @@ 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');
+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
@@ -52,7 +52,7 @@ ALTER TABLESPACE regress_allopt_tblsp SET (seq_page_cost='1.5', random_page_cost
 (2 rows)
 \pset format aligned
 -- tablespace with owner suppressed
-SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'owner', 'false');
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', owner => false);
                                                                        pg_get_tablespace_ddl                                                                       
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------
  CREATE TABLESPACE regress_allopt_tblsp LOCATION '';
diff --git a/src/test/regress/sql/database_ddl.sql b/src/test/regress/sql/database_ddl.sql
index 89753ac6411..547e4d0f800 100644
--- a/src/test/regress/sql/database_ddl.sql
+++ b/src/test/regress/sql/database_ddl.sql
@@ -35,21 +35,16 @@ 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('regression_database_ddl', 'owner', 'invalid');
-
--- Duplicate option (should error)
-SELECT * FROM pg_get_database_ddl('regression_database_ddl', 'owner', 'false', 'owner', 'true');
 
 -- Without options
 SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl');
 
 -- With owner
-SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl', 'owner', 'true');
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl', owner => true);
 
 -- Pretty-printed output
 \pset format unaligned
-SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl', 'pretty', 'true', 'tablespace', 'false');
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl', pretty => true, tablespace => false);
 \pset format aligned
 
 -- Permission check: revoke CONNECT on database
diff --git a/src/test/regress/sql/role_ddl.sql b/src/test/regress/sql/role_ddl.sql
index 3d0142242ec..1667a1f6e10 100644
--- a/src/test/regress/sql/role_ddl.sql
+++ b/src/test/regress/sql/role_ddl.sql
@@ -40,7 +40,7 @@ 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');
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3', pretty => true);
 \pset format aligned
 
 -- Role with memberships
@@ -57,7 +57,7 @@ RESET ROLE;
 SELECT * FROM pg_get_role_ddl('regress_role_ddl_member');
 
 -- Role with memberships suppressed
-SELECT * FROM pg_get_role_ddl('regress_role_ddl_member', 'memberships', 'false');
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_member', memberships => false);
 
 -- Non-existent role (should error)
 SELECT * FROM pg_get_role_ddl(9999999::oid);
diff --git a/src/test/regress/sql/tablespace_ddl.sql b/src/test/regress/sql/tablespace_ddl.sql
index ee3cc6e2e1e..fece93df251 100644
--- a/src/test/regress/sql/tablespace_ddl.sql
+++ b/src/test/regress/sql/tablespace_ddl.sql
@@ -30,11 +30,11 @@ 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');
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', pretty => true);
 \pset format aligned
 
 -- tablespace with owner suppressed
-SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'owner', 'false');
+SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', owner => false);
 
 DROP TABLESPACE regress_allopt_tblsp;
 
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e9430e07b36..fbd1f499aec 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -637,8 +637,6 @@ DataChecksumsWorkerResult
 DataDirSyncMethod
 DataDumperPtr
 DataPageDeleteStack
-DdlOptType
-DdlOption
 DataTypesUsageChecks
 DataTypesUsageVersionCheck
 DatabaseInfo

base-commit: 93dc1ace2007fe7c1103ccda3d7bc13b4eb4352f
-- 
2.53.0

---

From: "Euler Taveira" <[email-masked]>
Date: Mon, 06 Apr 2026 14:09:55 -0300

> code and to the user) and I don't see any compelling reason to do so.

I did the same question when reviewing one of these patches. My first reaction
was if we want flexibility to cover various use cases and maintainability to
add/deprecate new options, we need a mechanism to avoid breaking compatibility
or even overloading the function (complexity). My natural choice was key-value
pair arguments. It needs new support code (although we already use this style
in some of the backend functions -- e.g. pg_logical_slot_*_changes()).

> worry about handling NULL values for the first argument.

That's a good point.

> argument one. But if I'm wrong about that, please let me know.

I also consider your approach but decided not to use it. The argument against
named arguments is that you cannot add new argument *without* a DEFAULT value;
if you do, all existing functions will fail. You also need to create another
function with a different list of arguments to support a new option. If we are
fine with this restriction, your proposal seems ok to me.

One point in favor of your proposal is that the named arguments seems more
intuitive than the key-value pair arguments. The first impression is that
interchanging key and value is harder to figure out that the named argument
notation. Of course, documentation and some examples can help the user to write
these function calls. That's not the first function that uses this key-value
argument approach.

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 6 Apr 2026 22:24:32 +0200

On Mon, 6 Apr 2026 at 19:10, Euler Taveira <[email-masked]> wrote:
> although we already use this style

Thanks for the additional context. I didn't know about
pg_logical_slot_*_changes using this style. I searched the docs
locally and cannot find any other functions that use this style. I
think what makes pg_logical_slot_*_changes special, is that it passes
these options to the plugin. The plugin can define any valid options,
and postgres core cannot know what they are. I think this approach
makes sense for those functions because of that, but the ddl functions
don't pass the options to a plugin, so that argument does not apply
here.

> if you do, all existing functions will fail.

I'm not sure what kind of change you're referring to here. I don't
understand how variadic options allow you to add a required argument
to an existing function without breaking existing callers. Could you
give a concrete example of a change that the VARIADIC allows, but the
named arguments don't?

> You also need to create another
> function with a different list of arguments to support a new option.

I don't understand this either. We often add new optional arguments to
existing functions in a new major release. e.g. pg_start_backup got
the exclusive argument in PG9.6. Or do you mean something else here?

---

From: "Euler Taveira" <[email-masked]>
Date: Tue, 07 Apr 2026 00:43:43 -0300

> don't pass the options to a plugin, so that argument does not apply

There are other functions. See pg_restore_extended_stats() [1] and related
functions. If you are looking for flexibility, this key-value pair arguments is
one of the ways to achieve it.

> give a concrete example of a change that the VARIADIC allows, but the

Indeed. My sentence was confused. I want to say that the regular argument
list is not as flexible as the VARIADIC argument. Once you have an argument
with DEFAULT, you cannot have a next argument *without* DEFAULT. For VARIADIC
arguments, this restriction does not exist; there is no need to change the
function signature. The argument manipulation (default value, non null)
happens inside the function.

postgres=# create function foo(arg1 int default 0, arg2 int) returns int as $$ begin return arg1 + arg2; end; $$ language plpgsql;
ERROR:  input parameters after one with a default value must also have defaults
LINE 1: create function foo(arg1 int default 0, arg2 int) returns in...

It means these functions cannot add a new required argument (without DEFAULT).
Unless you change the current order of the arguments and put arg2 argument
before arg1. Doing that you could silently break existing function calls (if
argument type is that same as the existing one).

postgres=# create function foo(arg1 int default 0, arg2 int default 0) returns int as $$ begin return arg1 + arg2; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# select foo(arg1 => 5, arg2 => 8);
 foo 
-----
  13
(1 row)

postgres=# select foo(5, 8);
 foo 
-----
  13
(1 row)

postgres=# -- include new argument 
postgres=# drop function foo(int, int);
DROP FUNCTION
postgres=# create function foo(arg3 int, arg1 int default 0, arg2 int default 0) returns int as $$ begin return (arg1 + arg2) * arg3; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# select foo(5, 8);
 foo 
-----
  40
(1 row)

Of course, if you are using named arguments an error is emitted.

postgres=# select foo(arg1 => 5, arg2 => 8);
ERROR:  function foo(arg1 => integer, arg2 => integer) does not exist
LINE 1: select foo(arg1 => 5, arg2 => 8);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The VARIADIC argument forces you to always specify the argument name; that's a
good thing. The regular argument list requires you to remember the order of the
arguments (unless you are using named arguments).

It is just a few arguments for the current functions but I predict that
pg_get_table_dll may have a dozen of arguments. IMO the VARIADIC approach is
superior when you want several options. The function call is smaller in
comparison to your proposal. (Let's say you want to specify the last argument
value. Inform all the other default arguments plus the argument you want to
change. For VARIADIC, specify only the argument you want to change.)

> the exclusive argument in PG9.6. Or do you mean something else here?

I meant modifying the pg_proc.dat every time a new argument is added.

[1] https://www.postgresql.org/docs/18/functions-admin.html#FUNCTIONS-ADMIN-STATSMOD

---

From: "Jelte Fennema-Nio" <[email-masked]>
Date: Tue, 07 Apr 2026 14:36:02 +0200

> one of the ways to achieve it.

Ah, I missed that one indeed my search didn't find it because it used
type "any" for the variadic. That makes the usage nicer imo than the one
used here, because you can actually give booleans to it (not only 'true'
& 'false' as strings).

Looking at those examples I foresee another big downside to the VARIADIC
approach. It's impossible for an SQL autoformatter to make the function
call look nice, because it does not know the two arguments are supposed
to be together. They will all be put on a single line. The only way to
get a nicely looking function call is hand-formatting the code.

> LINE 1: create function foo(arg1 int default 0, arg2 int) returns in...

You don't give an example how VARIADIC gives you the ability to make
that behave differently. But I guess you mean that:

SELECT foo();

would start erroring "with 'arg2' is required" and users would have to do

SELECT foo('arg2', 123);

There's a pretty simple way to get that same behaviour for the named
arguments approach though. Simply use DEFAULT NULL as the default for
arg2, and make it nonstrict. Then you can check for NULL in the
implementation and throw an error, just like you would do for the
VARIADIC version. With that

SELECT foo(); --errors
SELECT foo(arg2 => 123) -- works

So I don't see how this VARIADIC differs in this case.

In any case it seems unlikely to me that we want to ever add new
required arguments to these functions. Simply for backwards
compatibility reasons that sounds like a huge hassle that we'll probably
want to avoid by giving any new arguments a default. So even if there
was a difference, I don't really consider that a useful benefit of the
VARIADIC approach.

> arguments (unless you are using named arguments).

I definitely agree with this. But I think that's solvable in practice by
having examples in the docs showing how to use named arguments for these
functions (see attached v6). That way most users will use that named
argument syntax as opposed to the positional one.

> change. For VARIADIC, specify only the argument you want to change.)

If you use named arguments to call the function, then all of this
doesn't matter. And actually the VARIADIC can be more confusing.
Especially with many arguments because it can be unclear which of the
arguments is a key and which one is a value.

And even for few arguments a reader can be confused, if the reader
doesn't realize that the arguments are interpreted as key value pairs.
For instance, I'd say that for a call like below, it's not obvious that
'foreign_keys' and 'all' are a pair. I'd have to look at the function
docs to realize that these are not two separate arguments (one set to
'foreign_keys', and the other to 'all'):

select pg_get_table_ddl('mytable', 'foreign_keys', 'all');

while with the named argument syntax makes that's immediately clearer:

select pg_get_table_ddl('mytable', foreign_keys => 'all');

> I meant modifying the pg_proc.dat every time a new argument is added.

Sure, but I don't understand why that would be problem. We do that all
the time in major releases. Even with the VARIADIC approach, I don't
think we should be adding optional arguments in minor releases.

So to summarize (from my biased viewpoint) I think the downsides are:
1. Uncommon calling convention: only pg_restore_*_stats and
   pg_logical_slot_*_changes use it, while all other functions support
   named parameters.
2. Needs custom option parsing logic
3. More characters to type because you have to quote booleans, integers
   and argument names.
4. Requires functions to be marked as NOSTRICT, which then needs
   additional NULL handling
5. It can be unclear to a reader of a query that the function arguments
   should be interpreted as key-value pair
6. Breaks auto formatting

And the benefit:
1. Forces people to specify the argument name

I don't think those benefits outweigh the downsides.
From aed0f64b2159c9f83ae1321442c4e8c861afde7a Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <[email-masked]>
Date: Mon, 6 Apr 2026 17:05:27 +0200
Subject: [PATCH v6] Use named arguments for pg_get_*_ddl() functions

 doc/src/sgml/func/func-info.sgml             |  65 +++--
 10 files changed, 88 insertions(+), 306 deletions(-)

index 80cf11083d6..179f2492845 100644
@@ -3904,10 +3906,15 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
+        For example:
+<programlisting>
+SELECT * FROM pg_get_role_ddl('myrole', memberships =&gt; false, pretty =&gt; true);
+</programlisting>
@@ -3917,15 +3924,19 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
@@ -3933,9 +3944,14 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
+        For example:
+<programlisting>
+SELECT * FROM pg_get_tablespace_ddl('pg_default', pretty =&gt; true);
+</programlisting>
@@ -3945,8 +3961,12 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
@@ -3954,11 +3974,16 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
+        For example:
+<programlisting>
+SELECT * FROM pg_get_database_ddl('mydb', pretty =&gt; true, tablespace =&gt; false);
+</programlisting>

-/*
- * parse_ddl_options

-		if (PG_ARGISNULL(0))
-		{

 
-		if (isnull)
-		{

 /*

 /*

-		if (PG_ARGISNULL(0))
-		{

 
 -- With owner
 
 -- Pretty-printed output
 \pset format unaligned
 
 -- Pretty-printed output
 \pset format unaligned

 -- pretty-printed output
 \pset format unaligned

 -- Without options
 
 -- With owner
 
 -- Pretty-printed output
 \pset format unaligned

 -- Pretty-printed output
 \pset format unaligned
 
 -- Role with memberships

 
 -- pretty-printed output
 \pset format unaligned

---

From: Jeff Davis <[email-masked]>
Date: Fri, 10 Apr 2026 13:03:36 -0700

> Pushed. I have moved the remaining get_*_ddl items to PG20-1

The line:

  role_settings = DatumGetArrayTypeP(datum);

should be DatumGetArrayTypePCopy(), because it's being pfree()d later.
The existing code will sometimes make a copy and sometimes not, e.g.:

  -- settings are contrived to make the datum inline
  CREATE USER u1;
  ALTER ROLE u1 SET search_path = 'public, pg_catalog, pg_temp';
  ALTER ROLE u1 SET work_mem='64MB';
  ALTER ROLE u1 SET statement_timeout='30s';
  ALTER ROLE u1 SET lock_timeout='10s';
  ALTER ROLE u1 SET idle_in_transaction_session_timeout = '60s';
  SELECT pg_get_role_ddl('u1');
  ERROR:  pfree called with invalid pointer 0x7986dd0c7cc8 (header
0x0000400600000000)

Also, it looks like the scan key in pg_get_role_ddl_internal() uses
only the second attribute of the index, which might be worth a comment.

Regards,
	Jeff Davis

---

From: SATYANARAYANA NARLAPURAM <[email-masked]>
Date: Mon, 13 Apr 2026 12:41:31 -0700

Hi,

>   ERROR:  pfree called with invalid pointer 0x7986dd0c7cc8 (header

Yes, it appears to be a bug. Attached a patch to fix this. Tested with the
attached patch and don't see server crashing after that.

postgres=#   CREATE DATABASE crashtest TEMPLATE template0 LC_COLLATE 'C'
LC_CTYPE 'C';
  ALTER DATABASE crashtest SET search_path = 'public, pg_catalog';
  ALTER DATABASE crashtest SET work_mem = '64MB';
  ALTER DATABASE crashtest SET statement_timeout = '30s';
  ALTER DATABASE crashtest SET random_page_cost = 1.5;
  SELECT pg_get_database_ddl('crashtest');
CREATE DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
                                            pg_get_database_ddl

------------------------------------------------------------------------------------------------------------
 CREATE DATABASE crashtest WITH TEMPLATE = template0 ENCODING = 'UTF8'
LOCALE_PROVIDER = libc LOCALE = 'C';
 ALTER DATABASE crashtest OWNER TO azureuser;
 ALTER DATABASE crashtest SET search_path TO 'public, pg_catalog';
 ALTER DATABASE crashtest SET work_mem TO '64MB';
 ALTER DATABASE crashtest SET statement_timeout TO '30s';
 ALTER DATABASE crashtest SET random_page_cost TO '1.5';
(6 rows)

Thanks,
Satya

---

From: Andrew Dunstan <[email-masked]>
Date: Tue, 14 Apr 2026 18:52:04 -0400

> attached patch and don't see server crashing after that.

Thanks, pushed.

cheers

andrew

---

From: Andrew Dunstan <[email-masked]>
Date: Sun, 26 Apr 2026 10:07:55 -0400

On 2026-04-06 Mo 7:39 AM, Andrew Dunstan wrote:
> to persist on the existing regression database. I could see an 
> argument for converting this to a TAP test that would only be run 
> once, given our current very profligate running of the core regression 
> suite. That goes doubly for the tablespace test, which could also 
> probably use ALTER TABLESPACE instead of creating a bunch of 
> tablespaces and then dropping them.
>
>
>

Here's a patch that converts all these into a single TAP test, and 
reduces the number of tablespace creations.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com
From c92b9b3cc8726ce252193c0fffe9250d461ddad7 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email-masked]>
Date: Sun, 26 Apr 2026 09:43:57 -0400
Subject: [PATCH] Convert ddlutils regression tests to TAP tests.

The regression tests for pg_get_role_ddl(), pg_get_database_ddl(),
and pg_get_tablespace_ddl() created databases and tablespaces, which
are heavyweight operations.  As noted by Andres Freund, this is
wasteful in the core regression suite which gets run repeatedly.

Convert the three test files (role_ddl.sql, database_ddl.sql,
tablespace_ddl.sql) into a single TAP test that runs once, covering
all the same functionality: basic DDL generation, pretty-printing,
option handling, error cases, permission checks, and edge cases like
quoted names and role memberships.

Discussion: https://postgr.es/m/[email-masked]
---
 src/test/modules/test_misc/meson.build       |   1 +
 src/test/modules/test_misc/t/012_ddlutils.pl | 286 +++++++++++++++++++
 src/test/regress/expected/database_ddl.out   |  88 ------
 src/test/regress/expected/role_ddl.out       | 143 ----------
 src/test/regress/expected/tablespace_ddl.out |  84 ------
 src/test/regress/parallel_schedule           |   1 -
 src/test/regress/sql/database_ddl.sql        |  66 -----
 src/test/regress/sql/role_ddl.sql            |  96 -------
 src/test/regress/sql/tablespace_ddl.sql      |  58 ----
 9 files changed, 287 insertions(+), 536 deletions(-)
 create mode 100644 src/test/modules/test_misc/t/012_ddlutils.pl
 delete mode 100644 src/test/regress/expected/database_ddl.out
 delete mode 100644 src/test/regress/expected/role_ddl.out
 delete mode 100644 src/test/regress/expected/tablespace_ddl.out
 delete mode 100644 src/test/regress/sql/database_ddl.sql
 delete mode 100644 src/test/regress/sql/role_ddl.sql
 delete mode 100644 src/test/regress/sql/tablespace_ddl.sql

diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 1b25d98f7f3..356d8454b39 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -20,6 +20,7 @@ tests += {
       't/009_log_temp_files.pl',
       't/010_index_concurrently_upsert.pl',
       't/011_lock_stats.pl',
+      't/012_ddlutils.pl',
     ],
     # The injection points are cluster-wide, so disable installcheck
     'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/012_ddlutils.pl b/src/test/modules/test_misc/t/012_ddlutils.pl
new file mode 100644
index 00000000000..b11d4777ccc
--- /dev/null
+++ b/src/test/modules/test_misc/t/012_ddlutils.pl
@@ -0,0 +1,286 @@
+
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Tests for pg_get_database_ddl(), pg_get_tablespace_ddl(), and
+# pg_get_role_ddl().  These are TAP tests rather than plain regression
+# tests because they create databases and tablespaces, which are
+# heavyweight operations that should run only once rather than being
+# repeated with every invocation of the core regression suite.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+# Perl helper that strips locale/collation details from DDL output so
+# that results are stable across platforms.
+sub ddl_filter
+{
+	my ($text) = @_;
+	$text =~ s/\s*\bLOCALE_PROVIDER\b\s*=\s*(?:'[^']*'|"[^"]*"|\S+)//gi;
+	$text =~ s/\s*LC_COLLATE\s*=\s*(['"])[^'"]*\1//gi;
+	$text =~ s/\s*LC_CTYPE\s*=\s*(['"])[^'"]*\1//gi;
+	$text =~ s/\s*\S*LOCALE\S*\s*=?\s*(['"])[^'"]*\1//gi;
+	$text =~ s/\s*\S*COLLATION\S*\s*=?\s*(['"])[^'"]*\1//gi;
+	return $text;
+}
+
+
+########################################################################
+# pg_get_role_ddl tests
+########################################################################
+
+# Basic role
+$node->safe_psql('postgres', 'CREATE ROLE regress_role_ddl_test1');
+my $result = $node->safe_psql('postgres',
+	q{SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1')});
+like($result,
+	qr/CREATE ROLE regress_role_ddl_test1 .* NOLOGIN/,
+	'basic role DDL');
+
+# Role with multiple privileges
+$node->safe_psql('postgres', q{
+	CREATE ROLE regress_role_ddl_test2
+	  LOGIN SUPERUSER CREATEDB CREATEROLE
+	  CONNECTION LIMIT 5
+	  VALID UNTIL '2030-12-31 23:59:59+00'});
+$result = $node->safe_psql('postgres',
+	q{SELECT * FROM pg_get_role_ddl('regress_role_ddl_test2')});
+like($result, qr/SUPERUSER/, 'role with SUPERUSER');
+like($result, qr/CREATEDB/, 'role with CREATEDB');
+like($result, qr/CONNECTION LIMIT 5/, 'role with CONNECTION LIMIT');
+like($result, qr/VALID UNTIL '2030-12-31/, 'role with VALID UNTIL');
+
+# Role with configuration parameters
+$node->safe_psql('postgres', q{
+	ALTER ROLE regress_role_ddl_test1 SET work_mem TO '256MB';
+	ALTER ROLE regress_role_ddl_test1 SET search_path TO myschema, public});
+$result = $node->safe_psql('postgres',
+	q{SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1')});
+like($result, qr/SET work_mem TO '256MB'/, 'role with work_mem setting');
+like($result, qr/SET search_path TO/, 'role with search_path setting');
+
+# Role with database-specific configuration (needs a real database)
+$node->safe_psql('postgres', q{
+	CREATE DATABASE regression_ddlutils_test
+	  TEMPLATE template0 ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C';
+	ALTER ROLE regress_role_ddl_test2
+	  IN DATABASE regression_ddlutils_test SET work_mem TO '128MB'});
+$result = $node->safe_psql('postgres',
+	q{SELECT * FROM pg_get_role_ddl('regress_role_ddl_test2')});
+like($result,
+	qr/IN DATABASE regression_ddlutils_test SET work_mem TO '128MB'/,
+	'role with database-specific setting');
+
+# Role with special characters (requires quoting)
+$node->safe_psql('postgres', q{CREATE ROLE "regress_role-with-dash"});
+$result = $node->safe_psql('postgres',
+	q{SELECT * FROM pg_get_role_ddl('regress_role-with-dash')});
+like($result, qr/"regress_role-with-dash"/,
+	'role name requiring quoting');
+
+# Pretty-printed output
+$result = $node->safe_psql('postgres',
+	q{SELECT * FROM pg_get_role_ddl('regress_role_ddl_test2', 'pretty', 'true')});
+like($result, qr/\n\s+SUPERUSER/, 'role pretty-print indents attributes');
+
+# Role with memberships
+$node->safe_psql('postgres', q{
+	CREATE ROLE regress_role_ddl_grantor CREATEROLE;
+	CREATE ROLE regress_role_ddl_group1;
+	CREATE ROLE regress_role_ddl_group2;
+	CREATE ROLE regress_role_ddl_member;
+	GRANT regress_role_ddl_group1 TO regress_role_ddl_grantor WITH ADMIN TRUE;
+	GRANT regress_role_ddl_group2 TO regress_role_ddl_grantor WITH ADMIN TRUE;
+	SET ROLE regress_role_ddl_grantor;
+	GRANT regress_role_ddl_group1 TO regress_role_ddl_member
+	  WITH INHERIT TRUE, SET FALSE;
+	GRANT regress_role_ddl_group2 TO regress_role_ddl_member
+	  WITH ADMIN TRUE;
+	RESET ROLE});
+$result = $node->safe_psql('postgres',
+	q{SELECT * FROM pg_get_role_ddl('regress_role_ddl_member')});
+like($result, qr/GRANT regress_role_ddl_group1 TO regress_role_ddl_member/,
+	'role with memberships includes GRANT');
+like($result, qr/SET FALSE/, 'membership includes SET FALSE');
+like($result, qr/ADMIN TRUE/, 'membership includes ADMIN TRUE');
+
+# Memberships suppressed
+$result = $node->safe_psql('postgres',
+	q{SELECT * FROM pg_get_role_ddl('regress_role_ddl_member', 'memberships', 'false')});
+unlike($result, qr/GRANT/, 'memberships suppressed');
+
+# Non-existent role (should error)
+my ($ret, $stdout, $stderr) = $node->psql('postgres',
+	q{SELECT * FROM pg_get_role_ddl(9999999::oid)});
+isnt($ret, 0, 'non-existent role errors');
+like($stderr, qr/does not exist/, 'non-existent role error message');
+
+# NULL input (should return no rows)
+$result = $node->safe_psql('postgres',
+	q{SELECT count(*) FROM pg_get_role_ddl(NULL)});
+is($result, '0', 'NULL role returns no rows');
+
+# Permission check: revoke SELECT on pg_authid
+$node->safe_psql('postgres', q{
+	CREATE ROLE regress_role_ddl_noaccess;
+	REVOKE SELECT ON pg_authid FROM PUBLIC});
+($ret, $stdout, $stderr) = $node->psql('postgres',
+	q{SET ROLE regress_role_ddl_noaccess;
+	  SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1')});
+isnt($ret, 0, 'role DDL denied without pg_authid access');
+$node->safe_psql('postgres', q{
+	GRANT SELECT ON pg_authid TO PUBLIC});
+
+
+########################################################################
+# pg_get_database_ddl tests
+########################################################################
+
+# Set up: the test database was already created above for role tests.
+$node->safe_psql('postgres', q{
+	ALTER DATABASE regression_ddlutils_test OWNER TO regress_role_ddl_test2;
+	ALTER DATABASE regression_ddlutils_test CONNECTION LIMIT 123;
+	ALTER DATABASE regression_ddlutils_test SET random_page_cost = 2.0;
+	ALTER ROLE regress_role_ddl_test2
+	  IN DATABASE regression_ddlutils_test SET random_page_cost = 1.1});
+
+# Non-existent database
+($ret, $stdout, $stderr) = $node->psql('postgres',
+	q{SELECT * FROM pg_get_database_ddl('regression_no_such_db')});
+isnt($ret, 0, 'non-existent database errors');
+
+# NULL input
+$result = $node->safe_psql('postgres',
+	q{SELECT count(*) FROM pg_get_database_ddl(NULL)});
+is($result, '0', 'NULL database returns no rows');
+
+# Invalid option
+($ret, $stdout, $stderr) = $node->psql('postgres',
+	q{SELECT * FROM pg_get_database_ddl('regression_ddlutils_test', 'owner', 'invalid')});
+isnt($ret, 0, 'invalid boolean option errors');
+like($stderr, qr/invalid value/, 'invalid option error message');
+
+# Duplicate option
+($ret, $stdout, $stderr) = $node->psql('postgres',
+	q{SELECT * FROM pg_get_database_ddl('regression_ddlutils_test',
+	  'owner', 'false', 'owner', 'true')});
+isnt($ret, 0, 'duplicate option errors');
+
+# Basic output (without locale details)
+$result = ddl_filter($node->safe_psql('postgres',
+	q{SELECT pg_get_database_ddl
+	  FROM pg_get_database_ddl('regression_ddlutils_test')}));
+like($result, qr/CREATE DATABASE regression_ddlutils_test/,
+	'database DDL includes CREATE');
+like($result, qr/TEMPLATE = template0/, 'database DDL includes TEMPLATE');
+like($result, qr/ENCODING = 'UTF8'/, 'database DDL includes ENCODING');
+like($result, qr/OWNER TO regress_role_ddl_test2/, 'database DDL includes OWNER');
+like($result, qr/CONNECTION LIMIT = 123/, 'database DDL includes CONNLIMIT');
+like($result, qr/SET random_page_cost TO '2.0'/,
+	'database DDL includes GUC setting');
+
+# Pretty-printed output
+$result = ddl_filter($node->safe_psql('postgres',
+	q{SELECT pg_get_database_ddl
+	  FROM pg_get_database_ddl('regression_ddlutils_test',
+	    'pretty', 'true', 'tablespace', 'false')}));
+like($result, qr/\n\s+WITH TEMPLATE/, 'database DDL pretty-prints WITH');
+
+# Permission check
+$node->safe_psql('postgres', q{
+	REVOKE CONNECT ON DATABASE regression_ddlutils_test FROM PUBLIC});
+($ret, $stdout, $stderr) = $node->psql('postgres',
+	q{SET ROLE regress_role_ddl_noaccess;
+	  SELECT * FROM pg_get_database_ddl('regression_ddlutils_test')});
+isnt($ret, 0, 'database DDL denied without CONNECT');
+$node->safe_psql('postgres', q{
+	GRANT CONNECT ON DATABASE regression_ddlutils_test TO PUBLIC});
+
+
+########################################################################
+# pg_get_tablespace_ddl tests
+########################################################################
+
+# Non-existent tablespace by name
+($ret, $stdout, $stderr) = $node->psql('postgres',
+	q{SELECT * FROM pg_get_tablespace_ddl('regress_nonexistent_tblsp')});
+isnt($ret, 0, 'non-existent tablespace errors');
+
+# Non-existent tablespace by OID
+($ret, $stdout, $stderr) = $node->psql('postgres',
+	q{SELECT * FROM pg_get_tablespace_ddl(0::oid)});
+isnt($ret, 0, 'non-existent tablespace OID errors');
+
+# NULL input (name and OID variants)
+$result = $node->safe_psql('postgres',
+	q{SELECT count(*) FROM pg_get_tablespace_ddl(NULL::name)});
+is($result, '0', 'NULL tablespace name returns no rows');
+$result = $node->safe_psql('postgres',
+	q{SELECT count(*) FROM pg_get_tablespace_ddl(NULL::oid)});
+is($result, '0', 'NULL tablespace OID returns no rows');
+
+# Tablespace name requiring quoting
+$node->safe_psql('postgres', q{
+	SET allow_in_place_tablespaces = true;
+	CREATE TABLESPACE "regress_ tblsp" OWNER regress_role_ddl_test1
+	  LOCATION ''});
+$result = $node->safe_psql('postgres',
+	q{SELECT * FROM pg_get_tablespace_ddl('regress_ tblsp')});
+like($result, qr/"regress_ tblsp"/, 'tablespace name is quoted');
+
+# Rename and add options; reuse this tablespace for the remaining tests
+$node->safe_psql('postgres', q{
+	ALTER TABLESPACE "regress_ tblsp" RENAME TO regress_allopt_tblsp;
+	ALTER TABLESPACE regress_allopt_tblsp
+	  SET (seq_page_cost = '1.5', random_page_cost = '1.1234567890',
+	       effective_io_concurrency = '17', maintenance_io_concurrency = '18')});
+
+# Tablespace with multiple options
+$result = $node->safe_psql('postgres',
+	q{SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp')});
+like($result, qr/CREATE TABLESPACE regress_allopt_tblsp/,
+	'tablespace DDL includes CREATE');
+like($result, qr/OWNER regress_role_ddl_test1/,
+	'tablespace DDL includes OWNER');
+like($result, qr/seq_page_cost='1.5'/, 'tablespace DDL includes options');
+
+# Pretty-printed output
+$result = $node->safe_psql('postgres',
+	q{SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp',
+	  'pretty', 'true')});
+like($result, qr/\n\s+OWNER/, 'tablespace DDL pretty-prints OWNER');
+
+# Owner suppressed
+$result = $node->safe_psql('postgres',
+	q{SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp',
+	  'owner', 'false')});
+unlike($result, qr/OWNER/, 'tablespace DDL owner suppressed');
+
+# Lookup by OID
+$result = $node->safe_psql('postgres', q{
+	SELECT pg_get_tablespace_ddl
+	FROM pg_get_tablespace_ddl(
+	  (SELECT oid FROM pg_tablespace
+	   WHERE spcname = 'regress_allopt_tblsp'))});
+like($result, qr/CREATE TABLESPACE regress_allopt_tblsp/,
+	'tablespace DDL by OID');
+
+# Permission check
+$node->safe_psql('postgres',
+	q{REVOKE SELECT ON pg_tablespace FROM PUBLIC});
+($ret, $stdout, $stderr) = $node->psql('postgres',
+	q{SET ROLE regress_role_ddl_noaccess;
+	  SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp')});
+isnt($ret, 0, 'tablespace DDL denied without pg_tablespace access');
+$node->safe_psql('postgres', q{
+	GRANT SELECT ON pg_tablespace TO PUBLIC});
+
+$node->stop;
+
+done_testing();
diff --git a/src/test/regress/expected/database_ddl.out b/src/test/regress/expected/database_ddl.out
deleted file mode 100644
index 97657e52cfa..00000000000
--- a/src/test/regress/expected/database_ddl.out
+++ /dev/null
@@ -1,88 +0,0 @@
---
--- Tests for pg_get_database_ddl()
---
--- To produce stable regression test output, strip locale/collation details
--- from the DDL output.  Uses a plain SQL function to avoid a PL/pgSQL
--- dependency.
-CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
-RETURNS TEXT LANGUAGE sql AS $$
-SELECT regexp_replace(
-  regexp_replace(
-    regexp_replace(
-      regexp_replace(
-        regexp_replace(
-          ddl_input,
-          '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)', '', 'gi'),
-        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1', '', 'gi'),
-      '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1', '', 'gi'),
-    '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1', '', 'gi'),
-  '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1', '', 'gi')
-$$;
-CREATE ROLE regress_datdba;
-CREATE DATABASE regression_database_ddl
-    ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0
-    OWNER regress_datdba;
-ALTER DATABASE regression_database_ddl CONNECTION_LIMIT 123;
-ALTER DATABASE regression_database_ddl SET random_page_cost = 2.0;
-ALTER ROLE regress_datdba IN DATABASE regression_database_ddl SET random_page_cost = 1.1;
--- 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('regression_database_ddl', 'owner', 'invalid');
-ERROR:  invalid value for boolean option "owner": invalid
--- Duplicate option (should error)
-SELECT * FROM pg_get_database_ddl('regression_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('regression_database_ddl');
-                                      ddl_filter                                      
---------------------------------------------------------------------------------------
- CREATE DATABASE regression_database_ddl WITH TEMPLATE = template0 ENCODING = 'UTF8';
- ALTER DATABASE regression_database_ddl OWNER TO regress_datdba;
- ALTER DATABASE regression_database_ddl CONNECTION LIMIT = 123;
- ALTER DATABASE regression_database_ddl SET random_page_cost TO '2.0';
-(4 rows)
-
--- With owner
-SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl', 'owner', 'true');
-                                      ddl_filter                                      
---------------------------------------------------------------------------------------
- CREATE DATABASE regression_database_ddl WITH TEMPLATE = template0 ENCODING = 'UTF8';
- ALTER DATABASE regression_database_ddl OWNER TO regress_datdba;
- ALTER DATABASE regression_database_ddl CONNECTION LIMIT = 123;
- ALTER DATABASE regression_database_ddl SET random_page_cost TO '2.0';
-(4 rows)
-
--- Pretty-printed output
-\pset format unaligned
-SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl', 'pretty', 'true', 'tablespace', 'false');
-ddl_filter
-CREATE DATABASE regression_database_ddl
-    WITH TEMPLATE = template0
-    ENCODING = 'UTF8';
-ALTER DATABASE regression_database_ddl OWNER TO regress_datdba;
-ALTER DATABASE regression_database_ddl CONNECTION LIMIT = 123;
-ALTER DATABASE regression_database_ddl SET random_page_cost TO '2.0';
-(4 rows)
-\pset format aligned
--- Permission check: revoke CONNECT on database
-CREATE ROLE regress_db_ddl_noaccess;
-REVOKE CONNECT ON DATABASE regression_database_ddl FROM PUBLIC;
-SET ROLE regress_db_ddl_noaccess;
-SELECT * FROM pg_get_database_ddl('regression_database_ddl');  -- should fail
-ERROR:  permission denied for database regression_database_ddl
-RESET ROLE;
-GRANT CONNECT ON DATABASE regression_database_ddl TO PUBLIC;
-DROP ROLE regress_db_ddl_noaccess;
-DROP DATABASE regression_database_ddl;
-DROP FUNCTION ddl_filter(text);
-DROP ROLE regress_datdba;
diff --git a/src/test/regress/expected/role_ddl.out b/src/test/regress/expected/role_ddl.out
deleted file mode 100644
index 575111da55c..00000000000
--- a/src/test/regress/expected/role_ddl.out
+++ /dev/null
@@ -1,143 +0,0 @@
--- 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
--- Role with memberships
-CREATE ROLE regress_role_ddl_grantor CREATEROLE;
-CREATE ROLE regress_role_ddl_group1;
-CREATE ROLE regress_role_ddl_group2;
-CREATE ROLE regress_role_ddl_member;
-GRANT regress_role_ddl_group1 TO regress_role_ddl_grantor WITH ADMIN TRUE;
-GRANT regress_role_ddl_group2 TO regress_role_ddl_grantor WITH ADMIN TRUE;
-SET ROLE regress_role_ddl_grantor;
-GRANT regress_role_ddl_group1 TO regress_role_ddl_member WITH INHERIT TRUE, SET FALSE;
-GRANT regress_role_ddl_group2 TO regress_role_ddl_member WITH ADMIN TRUE;
-RESET ROLE;
-SELECT * FROM pg_get_role_ddl('regress_role_ddl_member');
-                                                             pg_get_role_ddl                                                             
------------------------------------------------------------------------------------------------------------------------------------------
- CREATE ROLE regress_role_ddl_member NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
- GRANT regress_role_ddl_group1 TO regress_role_ddl_member WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_role_ddl_grantor;
- GRANT regress_role_ddl_group2 TO regress_role_ddl_member WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_role_ddl_grantor;
-(3 rows)
-
--- Role with memberships suppressed
-SELECT * FROM pg_get_role_ddl('regress_role_ddl_member', 'memberships', 'false');
-                                                  pg_get_role_ddl                                                   
---------------------------------------------------------------------------------------------------------------------
- CREATE ROLE regress_role_ddl_member NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
-(1 row)
-
--- Non-existent role (should error)
-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)
-
--- Permission check: revoke SELECT on pg_authid
-CREATE ROLE regress_role_ddl_noaccess;
-REVOKE SELECT ON pg_authid FROM PUBLIC;
-SET ROLE regress_role_ddl_noaccess;
-SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1');  -- should fail
-ERROR:  permission denied for role regress_role_ddl_test1
-RESET ROLE;
-GRANT SELECT ON pg_authid TO PUBLIC;
-DROP ROLE regress_role_ddl_noaccess;
--- 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";
-SET ROLE regress_role_ddl_grantor;
-REVOKE regress_role_ddl_group1 FROM regress_role_ddl_member;
-REVOKE regress_role_ddl_group2 FROM regress_role_ddl_member;
-RESET ROLE;
-DROP ROLE regress_role_ddl_member;
-DROP ROLE regress_role_ddl_group1;
-DROP ROLE regress_role_ddl_group2;
-DROP ROLE regress_role_ddl_grantor;
-DROP DATABASE regression_role_ddl_test;
--- Reset timezone to default
-RESET timezone;
diff --git a/src/test/regress/expected/tablespace_ddl.out b/src/test/regress/expected/tablespace_ddl.out
deleted file mode 100644
index e52043273a9..00000000000
--- a/src/test/regress/expected/tablespace_ddl.out
+++ /dev/null
@@ -1,84 +0,0 @@
---
--- 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
--- tablespace with owner suppressed
-SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'owner', 'false');
-                                                                       pg_get_tablespace_ddl                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
- CREATE TABLESPACE regress_allopt_tblsp 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)
-
-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;
--- Permission check: revoke SELECT on pg_tablespace
-CREATE TABLESPACE regress_acl_tblsp OWNER regress_tblspc_ddl_user LOCATION '';
-CREATE ROLE regress_tblspc_ddl_noaccess;
-REVOKE SELECT ON pg_tablespace FROM PUBLIC;
-SET ROLE regress_tblspc_ddl_noaccess;
-SELECT * FROM pg_get_tablespace_ddl('regress_acl_tblsp');  -- should fail
-ERROR:  permission denied for tablespace regress_acl_tblsp
-RESET ROLE;
-GRANT SELECT ON pg_tablespace TO PUBLIC;
-DROP TABLESPACE regress_acl_tblsp;
-DROP ROLE regress_tblspc_ddl_noaccess;
-DROP ROLE regress_tblspc_ddl_user;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 288e94dc408..02a595e3367 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -135,7 +135,6 @@ test: compression compression_lz4 compression_pglz
 # oidjoins is read-only, though, and should run late for best coverage
 test: oidjoins event_trigger
 
-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
deleted file mode 100644
index 89753ac6411..00000000000
--- a/src/test/regress/sql/database_ddl.sql
+++ /dev/null
@@ -1,66 +0,0 @@
---
--- Tests for pg_get_database_ddl()
---
-
--- To produce stable regression test output, strip locale/collation details
--- from the DDL output.  Uses a plain SQL function to avoid a PL/pgSQL
--- dependency.
-
-CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
-RETURNS TEXT LANGUAGE sql AS $$
-SELECT regexp_replace(
-  regexp_replace(
-    regexp_replace(
-      regexp_replace(
-        regexp_replace(
-          ddl_input,
-          '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)', '', 'gi'),
-        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1', '', 'gi'),
-      '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1', '', 'gi'),
-    '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1', '', 'gi'),
-  '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1', '', 'gi')
-$$;
-
-CREATE ROLE regress_datdba;
-CREATE DATABASE regression_database_ddl
-    ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0
-    OWNER regress_datdba;
-ALTER DATABASE regression_database_ddl CONNECTION_LIMIT 123;
-ALTER DATABASE regression_database_ddl SET random_page_cost = 2.0;
-ALTER ROLE regress_datdba IN DATABASE regression_database_ddl SET random_page_cost = 1.1;
-
--- 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('regression_database_ddl', 'owner', 'invalid');
-
--- Duplicate option (should error)
-SELECT * FROM pg_get_database_ddl('regression_database_ddl', 'owner', 'false', 'owner', 'true');
-
--- Without options
-SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl');
-
--- With owner
-SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl', 'owner', 'true');
-
--- Pretty-printed output
-\pset format unaligned
-SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regression_database_ddl', 'pretty', 'true', 'tablespace', 'false');
-\pset format aligned
-
--- Permission check: revoke CONNECT on database
-CREATE ROLE regress_db_ddl_noaccess;
-REVOKE CONNECT ON DATABASE regression_database_ddl FROM PUBLIC;
-SET ROLE regress_db_ddl_noaccess;
-SELECT * FROM pg_get_database_ddl('regression_database_ddl');  -- should fail
-RESET ROLE;
-GRANT CONNECT ON DATABASE regression_database_ddl TO PUBLIC;
-DROP ROLE regress_db_ddl_noaccess;
-
-DROP DATABASE regression_database_ddl;
-DROP FUNCTION ddl_filter(text);
-DROP ROLE regress_datdba;
diff --git a/src/test/regress/sql/role_ddl.sql b/src/test/regress/sql/role_ddl.sql
deleted file mode 100644
index 3d0142242ec..00000000000
--- a/src/test/regress/sql/role_ddl.sql
+++ /dev/null
@@ -1,96 +0,0 @@
--- 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
-
--- Role with memberships
-CREATE ROLE regress_role_ddl_grantor CREATEROLE;
-CREATE ROLE regress_role_ddl_group1;
-CREATE ROLE regress_role_ddl_group2;
-CREATE ROLE regress_role_ddl_member;
-GRANT regress_role_ddl_group1 TO regress_role_ddl_grantor WITH ADMIN TRUE;
-GRANT regress_role_ddl_group2 TO regress_role_ddl_grantor WITH ADMIN TRUE;
-SET ROLE regress_role_ddl_grantor;
-GRANT regress_role_ddl_group1 TO regress_role_ddl_member WITH INHERIT TRUE, SET FALSE;
-GRANT regress_role_ddl_group2 TO regress_role_ddl_member WITH ADMIN TRUE;
-RESET ROLE;
-SELECT * FROM pg_get_role_ddl('regress_role_ddl_member');
-
--- Role with memberships suppressed
-SELECT * FROM pg_get_role_ddl('regress_role_ddl_member', 'memberships', 'false');
-
--- Non-existent role (should error)
-SELECT * FROM pg_get_role_ddl(9999999::oid);
-
--- NULL input (should return no rows)
-SELECT * FROM pg_get_role_ddl(NULL);
-
--- Permission check: revoke SELECT on pg_authid
-CREATE ROLE regress_role_ddl_noaccess;
-REVOKE SELECT ON pg_authid FROM PUBLIC;
-SET ROLE regress_role_ddl_noaccess;
-SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1');  -- should fail
-RESET ROLE;
-GRANT SELECT ON pg_authid TO PUBLIC;
-DROP ROLE regress_role_ddl_noaccess;
-
--- 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";
-SET ROLE regress_role_ddl_grantor;
-REVOKE regress_role_ddl_group1 FROM regress_role_ddl_member;
-REVOKE regress_role_ddl_group2 FROM regress_role_ddl_member;
-RESET ROLE;
-DROP ROLE regress_role_ddl_member;
-DROP ROLE regress_role_ddl_group1;
-DROP ROLE regress_role_ddl_group2;
-DROP ROLE regress_role_ddl_grantor;
-
-DROP DATABASE regression_role_ddl_test;
-
--- Reset timezone to default
-RESET timezone;
diff --git a/src/test/regress/sql/tablespace_ddl.sql b/src/test/regress/sql/tablespace_ddl.sql
deleted file mode 100644
index ee3cc6e2e1e..00000000000
--- a/src/test/regress/sql/tablespace_ddl.sql
+++ /dev/null
@@ -1,58 +0,0 @@
---
--- 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
-
--- tablespace with owner suppressed
-SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'owner', 'false');
-
-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;
-
--- Permission check: revoke SELECT on pg_tablespace
-CREATE TABLESPACE regress_acl_tblsp OWNER regress_tblspc_ddl_user LOCATION '';
-CREATE ROLE regress_tblspc_ddl_noaccess;
-REVOKE SELECT ON pg_tablespace FROM PUBLIC;
-SET ROLE regress_tblspc_ddl_noaccess;
-SELECT * FROM pg_get_tablespace_ddl('regress_acl_tblsp');  -- should fail
-RESET ROLE;
-GRANT SELECT ON pg_tablespace TO PUBLIC;
-DROP TABLESPACE regress_acl_tblsp;
-DROP ROLE regress_tblspc_ddl_noaccess;
-
-DROP ROLE regress_tblspc_ddl_user;
-- 
2.43.0

---

From: Andrew Dunstan <[email-masked]>
Date: Wed, 29 Apr 2026 11:35:07 -0400

> reduces the number of tablespace creations.
pushed.

cheers

andrew