0004-Add-pg_get_database_ddl-function.patch

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

Add a new SQL-callable function that returns the DDL statements needed
to recreate a database. It takes a regdatabase argument and an optional
VARIADIC text argument for options that are specified as alternating
name/value pairs. The following options are supported: pretty (boolean)
for formatted output, owner (boolean) to include OWNER and tablespace
(boolean) to include TABLESPACE. The return is one or multiple rows
where the first row is a CREATE DATABASE statement and subsequent rows are
ALTER DATABASE statements to set some database properties.

Author: Akshay Joshi <akshay.joshi@enterprisedb.com>
Co-authored-by: Andrew Dunstan <andrew@dunslane.net>
Reviewed-by: Japin Li <japinli@hotmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Reviewed-by: Euler Taveira <euler@eulerto.com>
Reviewed-by: Quan Zongliang <quanzongliang@yeah.net>
Discussion: https://www.postgresql.org/message-id/flat/CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml           |  23 ++
 src/backend/utils/adt/ddlutils.c           | 349 +++++++++++++++++++++
 src/include/catalog/pg_proc.dat            |   8 +
 src/test/regress/expected/database_ddl.out | 107 +++++++
 src/test/regress/parallel_schedule         |   2 +-
 src/test/regress/sql/database_ddl.sql      |  89 ++++++
 6 files changed, 577 insertions(+), 1 deletion(-)
 create mode 100644 src/test/regress/expected/database_ddl.out
 create mode 100644 src/test/regress/sql/database_ddl.sql

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index f44bd0d0f8b..ce6194f606d 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3935,6 +3935,29 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         for formatted output.
        </para></entry>
       </row>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_database_ddl</primary>
+        </indexterm>
+        <function>pg_get_database_ddl</function>
+        ( <parameter>database</parameter> <type>regdatabase</type>
+        <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+        <type>text</type> </optional> )
+        <returnvalue>setof text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the <command>CREATE DATABASE</command> statement for the
+        specified database, followed by <command>ALTER DATABASE</command>
+        statements for connection limit, template status, and configuration
+        settings.  Each statement is returned as a separate row.
+        The following options are supported:
+        <literal>pretty</literal> (boolean) for formatted output,
+        <literal>owner</literal> (boolean) to include <literal>OWNER</literal>,
+        and <literal>tablespace</literal> (boolean) to include
+        <literal>TABLESPACE</literal>.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index 65df3c723cc..41838e403d3 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -22,11 +22,14 @@
 #include "access/htup_details.h"
 #include "access/table.h"
 #include "catalog/pg_authid.h"
+#include "catalog/pg_collation.h"
+#include "catalog/pg_database.h"
 #include "catalog/pg_db_role_setting.h"
 #include "catalog/pg_tablespace.h"
 #include "commands/tablespace.h"
 #include "common/relpath.h"
 #include "funcapi.h"
+#include "mb/pg_wchar.h"
 #include "miscadmin.h"
 #include "utils/acl.h"
 #include "utils/array.h"
@@ -35,6 +38,7 @@
 #include "utils/fmgroids.h"
 #include "utils/guc.h"
 #include "utils/lsyscache.h"
+#include "utils/pg_locale.h"
 #include "utils/rel.h"
 #include "utils/ruleutils.h"
 #include "utils/syscache.h"
@@ -72,6 +76,8 @@ static void append_ddl_option(StringInfo buf, bool pretty, int indent,
 static List *pg_get_role_ddl_internal(Oid roleid, bool pretty);
 static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty);
 static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);
+static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
+										  bool no_owner, bool no_tablespace);
 
 
 /*
@@ -729,3 +735,346 @@ pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS)
 
 	return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull);
 }
+
+/*
+ * pg_get_database_ddl_internal
+ *		Generate DDL statements to recreate a database.
+ *
+ * Returns a List of palloc'd strings.  The first element is the
+ * CREATE DATABASE statement; subsequent elements are ALTER DATABASE
+ * statements for properties and configuration settings.
+ */
+static List *
+pg_get_database_ddl_internal(Oid dbid, bool pretty,
+							 bool no_owner, bool no_tablespace)
+{
+	HeapTuple	tuple;
+	Form_pg_database dbform;
+	StringInfoData buf;
+	bool		isnull;
+	Datum		datum;
+	const char *encoding;
+	const char *dbname;
+	char	   *collate;
+	char	   *ctype;
+	Relation	rel;
+	ScanKeyData scankey;
+	SysScanDesc scan;
+	List	   *statements = NIL;
+
+	tuple = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbid));
+	if (!HeapTupleIsValid(tuple))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("database with OID %u does not exist", dbid)));
+
+	dbform = (Form_pg_database) GETSTRUCT(tuple);
+	dbname = quote_identifier(NameStr(dbform->datname));
+
+	/*
+	 * We don't support generating DDL for system databases.  The primary
+	 * reason for this is that users shouldn't be recreating them.
+	 */
+	if (strcmp(dbname, "template0") == 0 || strcmp(dbname, "template1") == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_RESERVED_NAME),
+				 errmsg("database \"%s\" is a system database", dbname)));
+
+	initStringInfo(&buf);
+
+	/* --- Build CREATE DATABASE statement --- */
+	appendStringInfo(&buf, "CREATE DATABASE %s", dbname);
+
+	append_ddl_option(&buf, pretty, 4, "WITH TEMPLATE = template0");
+
+	/* ENCODING */
+	encoding = pg_encoding_to_char(dbform->encoding);
+	if (strlen(encoding) > 0)
+		append_ddl_option(&buf, pretty, 4, "ENCODING = %s",
+						  quote_literal_cstr(encoding));
+
+	/* LOCALE_PROVIDER */
+	if (dbform->datlocprovider == COLLPROVIDER_BUILTIN ||
+		dbform->datlocprovider == COLLPROVIDER_ICU ||
+		dbform->datlocprovider == COLLPROVIDER_LIBC)
+		append_ddl_option(&buf, pretty, 4, "LOCALE_PROVIDER = %s",
+						  collprovider_name(dbform->datlocprovider));
+	else
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+				 errmsg("unrecognized locale provider: %c",
+						dbform->datlocprovider)));
+
+	/* LOCALE, LC_COLLATE, LC_CTYPE */
+	datum = SysCacheGetAttr(DATABASEOID, tuple,
+							Anum_pg_database_datcollate, &isnull);
+	if (!isnull)
+		collate = TextDatumGetCString(datum);
+	else
+		collate = "";
+	datum = SysCacheGetAttr(DATABASEOID, tuple,
+							Anum_pg_database_datctype, &isnull);
+	if (!isnull)
+		ctype = TextDatumGetCString(datum);
+	else
+		ctype = "";
+	if (strlen(collate) > 0 && strcmp(collate, ctype) == 0)
+	{
+		append_ddl_option(&buf, pretty, 4, "LOCALE = %s",
+						  quote_literal_cstr(collate));
+	}
+	else
+	{
+		if (strlen(collate) > 0)
+			append_ddl_option(&buf, pretty, 4, "LC_COLLATE = %s",
+							  quote_literal_cstr(collate));
+		if (strlen(ctype) > 0)
+			append_ddl_option(&buf, pretty, 4, "LC_CTYPE = %s",
+							  quote_literal_cstr(ctype));
+	}
+
+	/* LOCALE (provider-specific) */
+	datum = SysCacheGetAttr(DATABASEOID, tuple,
+							Anum_pg_database_datlocale, &isnull);
+	if (!isnull)
+	{
+		const char *locale = TextDatumGetCString(datum);
+
+		if (dbform->datlocprovider == COLLPROVIDER_BUILTIN)
+			append_ddl_option(&buf, pretty, 4, "BUILTIN_LOCALE = %s",
+							  quote_literal_cstr(locale));
+		else if (dbform->datlocprovider == COLLPROVIDER_ICU)
+			append_ddl_option(&buf, pretty, 4, "ICU_LOCALE = %s",
+							  quote_literal_cstr(locale));
+	}
+
+	/* ICU_RULES */
+	datum = SysCacheGetAttr(DATABASEOID, tuple,
+							Anum_pg_database_daticurules, &isnull);
+	if (!isnull && dbform->datlocprovider == COLLPROVIDER_ICU)
+		append_ddl_option(&buf, pretty, 4, "ICU_RULES = %s",
+						  quote_literal_cstr(TextDatumGetCString(datum)));
+
+	/* TABLESPACE */
+	if (!no_tablespace && OidIsValid(dbform->dattablespace))
+	{
+		char	   *spcname = get_tablespace_name(dbform->dattablespace);
+
+		if (pg_strcasecmp(spcname, "pg_default") != 0)
+			append_ddl_option(&buf, pretty, 4, "TABLESPACE = %s",
+							  quote_identifier(spcname));
+	}
+
+	appendStringInfoChar(&buf, ';');
+	statements = lappend(statements, pstrdup(buf.data));
+
+	/* OWNER */
+	if (!no_owner && OidIsValid(dbform->datdba))
+	{
+		char	   *owner = GetUserNameFromId(dbform->datdba, false);
+
+		resetStringInfo(&buf);
+		appendStringInfo(&buf, "ALTER DATABASE %s OWNER = %s;",
+						 dbname, quote_identifier(owner));
+		pfree(owner);
+		statements = lappend(statements, pstrdup(buf.data));
+	}
+
+	/* CONNECTION LIMIT */
+	if (dbform->datconnlimit != -1)
+	{
+		resetStringInfo(&buf);
+		appendStringInfo(&buf, "ALTER DATABASE %s CONNECTION LIMIT = %d;",
+						 dbname, dbform->datconnlimit);
+		statements = lappend(statements, pstrdup(buf.data));
+	}
+
+	/* IS_TEMPLATE */
+	if (dbform->datistemplate)
+	{
+		resetStringInfo(&buf);
+		appendStringInfo(&buf, "ALTER DATABASE %s IS_TEMPLATE = true;",
+						 dbname);
+		statements = lappend(statements, pstrdup(buf.data));
+	}
+
+	/* ALLOW_CONNECTIONS */
+	if (!dbform->datallowconn)
+	{
+		resetStringInfo(&buf);
+		appendStringInfo(&buf, "ALTER DATABASE %s ALLOW_CONNECTIONS = false;",
+						 dbname);
+		statements = lappend(statements, pstrdup(buf.data));
+	}
+
+	ReleaseSysCache(tuple);
+
+	/*
+	 * Now scan pg_db_role_setting for ALTER DATABASE SET configurations.
+	 *
+	 * It is only database-wide (setrole = 0). It generates one ALTER
+	 * statement per setting.
+	 */
+	rel = table_open(DbRoleSettingRelationId, AccessShareLock);
+	ScanKeyInit(&scankey,
+				Anum_pg_db_role_setting_setdatabase,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(dbid));
+
+	scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,
+							  NULL, 1, &scankey);
+
+	while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+	{
+		ArrayType  *reloptions;
+		Datum	   *settings;
+		bool	   *nulls;
+		int			nsettings;
+
+		/*
+		 * The setconfig column is a text array in "name=value" format. It
+		 * should never be null for a valid row, but be defensive.
+		 */
+		datum = heap_getattr(tuple, Anum_pg_db_role_setting_setconfig,
+							 RelationGetDescr(rel), &isnull);
+		if (isnull)
+			continue;
+
+		reloptions = DatumGetArrayTypeP(datum);
+
+		deconstruct_array_builtin(reloptions, TEXTOID, &settings, &nulls, &nsettings);
+
+		for (int i = 0; i < nsettings; i++)
+		{
+			char	   *s,
+					   *p;
+
+			if (nulls[i])
+				continue;
+
+			s = TextDatumGetCString(settings[i]);
+			p = strchr(s, '=');
+			if (p == NULL)
+			{
+				pfree(s);
+				continue;
+			}
+			*p++ = '\0';
+
+			resetStringInfo(&buf);
+			appendStringInfo(&buf, "ALTER DATABASE %s SET %s TO ",
+							 dbname,
+							 quote_identifier(s));
+
+			/*
+			 * Variables that are marked GUC_LIST_QUOTE were already fully
+			 * quoted before they were put into the setconfig array.  Break
+			 * the list value apart and then quote the elements as string
+			 * literals.
+			 */
+			if (GetConfigOptionFlags(s, true) & GUC_LIST_QUOTE)
+			{
+				List	   *namelist;
+				ListCell   *lc;
+
+				/* Parse string into list of identifiers */
+				if (!SplitGUCList(p, ',', &namelist))
+				{
+					/* this shouldn't fail really */
+					elog(ERROR, "invalid list syntax in setconfig item");
+				}
+				/* Special case: represent an empty list as NULL */
+				if (namelist == NIL)
+					appendStringInfoString(&buf, "NULL");
+				foreach(lc, namelist)
+				{
+					char	   *curname = (char *) lfirst(lc);
+
+					appendStringInfoString(&buf, quote_literal_cstr(curname));
+					if (lnext(namelist, lc))
+						appendStringInfoString(&buf, ", ");
+				}
+			}
+			else
+				appendStringInfoString(&buf, quote_literal_cstr(p));
+
+			appendStringInfoChar(&buf, ';');
+
+			statements = lappend(statements, pstrdup(buf.data));
+
+			pfree(s);
+		}
+
+		pfree(settings);
+		pfree(reloptions);
+	}
+
+	systable_endscan(scan);
+	table_close(rel, AccessShareLock);
+
+	pfree(buf.data);
+
+	return statements;
+}
+
+/*
+ * pg_get_database_ddl
+ *		Return DDL to recreate a database as a set of text rows.
+ */
+Datum
+pg_get_database_ddl(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+	List	   *statements;
+	ListCell   *lc;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext oldcontext;
+		Oid			dbid;
+		DdlOption	opts[] = {
+			{"pretty", DDL_OPT_BOOL},
+			{"owner", DDL_OPT_BOOL},
+			{"tablespace", DDL_OPT_BOOL},
+		};
+
+		funcctx = SRF_FIRSTCALL_INIT();
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		if (PG_ARGISNULL(0))
+		{
+			MemoryContextSwitchTo(oldcontext);
+			SRF_RETURN_DONE(funcctx);
+		}
+
+		dbid = PG_GETARG_OID(0);
+		parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+		statements = pg_get_database_ddl_internal(dbid,
+												  opts[0].isset && opts[0].boolval,
+												  opts[1].isset && !opts[1].boolval,
+												  opts[2].isset && !opts[2].boolval);
+		funcctx->user_fctx = statements;
+		funcctx->max_calls = list_length(statements);
+
+		MemoryContextSwitchTo(oldcontext);
+	}
+
+	funcctx = SRF_PERCALL_SETUP();
+	statements = (List *) funcctx->user_fctx;
+
+	if (funcctx->call_cntr < funcctx->max_calls)
+	{
+		char	   *stmt;
+
+		lc = list_nth_cell(statements, funcctx->call_cntr);
+		stmt = (char *) lfirst(lc);
+
+		SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+	}
+	else
+	{
+		list_free_deep(statements);
+		SRF_RETURN_DONE(funcctx);
+	}
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d25b6966d5b..b5ad34ccf2c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8612,6 +8612,14 @@
   proallargtypes => '{name,text}',
   pronargdefaults => '1', proargdefaults => '{NULL}',
   prosrc => 'pg_get_tablespace_ddl_name' },
+{ oid => '8762', descr => 'get DDL to recreate a database',
+  proname => 'pg_get_database_ddl', provariadic => 'text', proisstrict => 'f',
+  provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
+  proargtypes => 'regdatabase text',
+  proargmodes => '{i,v}',
+  proallargtypes => '{regdatabase,text}',
+  pronargdefaults => '1', proargdefaults => '{NULL}',
+  prosrc => 'pg_get_database_ddl' },
 { oid => '2509',
   descr => 'deparse an encoded expression with pretty-print option',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/test/regress/expected/database_ddl.out b/src/test/regress/expected/database_ddl.out
new file mode 100644
index 00000000000..60f933f57f1
--- /dev/null
+++ b/src/test/regress/expected/database_ddl.out
@@ -0,0 +1,107 @@
+--
+-- Tests for pg_get_database_ddl()
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- function removes collation and locale related details.
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+    cleaned_ddl TEXT;
+BEGIN
+    -- Remove LOCALE_PROVIDER placeholders
+    cleaned_ddl := regexp_replace(
+        ddl_input,
+        '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)',
+        '',
+        'gi'
+    );
+
+	-- Remove LC_COLLATE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove LC_CTYPE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove LOCALE placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+		'',
+		'gi'
+	);
+
+    -- Remove COLLATION placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
+CREATE ROLE regress_datdba;
+CREATE DATABASE regress_database_ddl
+	ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0
+	OWNER regress_datdba;
+ALTER DATABASE regress_database_ddl CONNECTION_LIMIT 123;
+-- Database doesn't exist
+SELECT * FROM pg_get_database_ddl('regression_database');
+ERROR:  database "regression_database" does not exist
+LINE 1: SELECT * FROM pg_get_database_ddl('regression_database');
+                                          ^
+-- NULL value
+SELECT * FROM pg_get_database_ddl(NULL);
+ pg_get_database_ddl 
+---------------------
+(0 rows)
+
+-- Invalid option value (should error)
+SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'invalid');
+ERROR:  invalid value for boolean option "owner": invalid
+-- Duplicate option (should error)
+SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false', 'owner', 'true');
+ERROR:  option "owner" is specified more than once
+-- Without options
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl');
+                                    ddl_filter                                     
+-----------------------------------------------------------------------------------
+ CREATE DATABASE regress_database_ddl WITH TEMPLATE = template0 ENCODING = 'UTF8';
+ ALTER DATABASE regress_database_ddl OWNER = regress_datdba;
+ ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123;
+(3 rows)
+
+-- Without owner
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false');
+                                    ddl_filter                                     
+-----------------------------------------------------------------------------------
+ CREATE DATABASE regress_database_ddl WITH TEMPLATE = template0 ENCODING = 'UTF8';
+ ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123;
+(2 rows)
+
+-- Pretty-printed output
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'pretty', 'true', 'tablespace', 'false');
+ddl_filter
+CREATE DATABASE regress_database_ddl
+    WITH TEMPLATE = template0
+    ENCODING = 'UTF8';
+ALTER DATABASE regress_database_ddl OWNER = regress_datdba;
+ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123;
+(3 rows)
+\pset format aligned
+DROP DATABASE regress_database_ddl;
+DROP FUNCTION ddl_filter(text);
+DROP ROLE regress_datdba;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index f3a01aecf04..d97b9f16908 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -130,7 +130,7 @@ test: partition_merge partition_split partition_join partition_prune reloptions
 # oidjoins is read-only, though, and should run late for best coverage
 test: oidjoins event_trigger
 
-test: role_ddl tablespace_ddl
+test: role_ddl tablespace_ddl database_ddl
 
 # event_trigger_login cannot run concurrently with any other tests because
 # on-login event handling could catch connection of a concurrent test.
diff --git a/src/test/regress/sql/database_ddl.sql b/src/test/regress/sql/database_ddl.sql
new file mode 100644
index 00000000000..4460dfedcd7
--- /dev/null
+++ b/src/test/regress/sql/database_ddl.sql
@@ -0,0 +1,89 @@
+--
+-- Tests for pg_get_database_ddl()
+--
+
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- function removes collation and locale related details.
+
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+    cleaned_ddl TEXT;
+BEGIN
+    -- Remove LOCALE_PROVIDER placeholders
+    cleaned_ddl := regexp_replace(
+        ddl_input,
+        '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)',
+        '',
+        'gi'
+    );
+
+	-- Remove LC_COLLATE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove LC_CTYPE assignments
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    -- Remove LOCALE placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+		'',
+		'gi'
+	);
+
+    -- Remove COLLATION placeholders
+    cleaned_ddl := regexp_replace(
+        cleaned_ddl,
+        '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+        '',
+        'gi'
+    );
+
+    RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE ROLE regress_datdba;
+CREATE DATABASE regress_database_ddl
+	ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0
+	OWNER regress_datdba;
+ALTER DATABASE regress_database_ddl CONNECTION_LIMIT 123;
+
+-- Database doesn't exist
+SELECT * FROM pg_get_database_ddl('regression_database');
+
+-- NULL value
+SELECT * FROM pg_get_database_ddl(NULL);
+
+-- Invalid option value (should error)
+SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'invalid');
+
+-- Duplicate option (should error)
+SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false', 'owner', 'true');
+
+-- Without options
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl');
+
+-- Without owner
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false');
+
+-- Pretty-printed output
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'pretty', 'true', 'tablespace', 'false');
+\pset format aligned
+
+DROP DATABASE regress_database_ddl;
+DROP FUNCTION ddl_filter(text);
+DROP ROLE regress_datdba;
-- 
2.43.0