On Fri, Mar 20, 2026, at 10:31 AM, Andrew Dunstan wrote:
>
> 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 protected]>
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 protected]>
Co-authored-by: Andrew Dunstan <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3scsnj02c0kobqjnbl2ajfpw...@mail.gmail.com
Discussion: https://www.postgresql.org/message-id/flat/[email protected]
Discussion: https://www.postgresql.org/message-id/flat/canxoldc6fhbyjvcgonzys+jf0nuo3lq_83-rttbujgs9id_...@mail.gmail.com
---
src/backend/utils/adt/Makefile | 1 +
src/backend/utils/adt/ddlutils.c | 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 protected]>
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 protected]>
Author: Bryan Green <[email protected]>
Co-authored-by: Andrew Dunstan <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Quan Zongliang <[email protected]>
Reviewed-by: jian he <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/[email protected]
---
doc/src/sgml/func/func-info.sgml | 54 ++++
src/backend/utils/adt/ddlutils.c | 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 protected]>
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 protected]>
Author: Manni Wood <[email protected]>
Co-authored-by: Andrew Dunstan <[email protected]>
Reviewed-by: Jim Jones <[email protected]>
Reviewed-by: Ãlvaro Herrera <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3scsnj02c0kobqjnbl2ajfpw...@mail.gmail.com
---
doc/src/sgml/func/func-info.sgml | 27 +++
src/backend/utils/adt/ddlutils.c | 190 ++++++++++++++++++-
src/backend/utils/adt/ruleutils.c | 4 +-
src/include/catalog/pg_proc.dat | 16 ++
src/include/utils/ruleutils.h | 1 +
src/test/regress/expected/tablespace_ddl.out | 65 +++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/tablespace_ddl.sql | 43 +++++
8 files changed, 344 insertions(+), 4 deletions(-)
create mode 100644 src/test/regress/expected/tablespace_ddl.out
create mode 100644 src/test/regress/sql/tablespace_ddl.sql
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index acd1a7cfeed..f44bd0d0f8b 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3908,6 +3908,33 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
for pretty-printed output.
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_tablespace_ddl</primary>
+ </indexterm>
+ <function>pg_get_tablespace_ddl</function>
+ ( <parameter>tablespace</parameter> <type>oid</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ <function>pg_get_tablespace_ddl</function>
+ ( <parameter>tablespace</parameter> <type>name</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE TABLESPACE</command> statement for
+ the specified tablespace (by OID or name). If the tablespace has
+ options set, an <command>ALTER TABLESPACE ... SET</command> statement
+ is also returned. Each statement is returned as a separate row.
+ The following option is supported: <literal>pretty</literal> (boolean)
+ for formatted output.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index 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 protected]>
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 protected]>
Co-authored-by: Andrew Dunstan <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: Ãlvaro Herrera <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
Reviewed-by: Quan Zongliang <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/canxoldc6fhbyjvcgonzys+jf0nuo3lq_83-rttbujgs9id_...@mail.gmail.com
---
doc/src/sgml/func/func-info.sgml | 23 ++
src/backend/utils/adt/ddlutils.c | 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
