public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: Japin Li <[email protected]>
Cc: Rafia Sabih <[email protected]>
Cc: Álvaro Herrera <[email protected]>
Cc: Euler Taveira <[email protected]>
Cc: Amul Sul <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: Chao Li <[email protected]>
Cc: Quan Zongliang <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
Date: Mon, 9 Mar 2026 17:38:01 +0530
Message-ID: <CANxoLDf+Ak=S+V1Rx25Eqh7r6cuWZtHYiEo_AZsn9eqiWbVGEg@mail.gmail.com> (raw)
In-Reply-To: <SY7PR01MB1092148A0ADF42B45E8EFB7B7B67AA@SY7PR01MB10921.ausprd01.prod.outlook.com>
References: <SY7PR01MB10921BB04879E2BDE787AA5EFB67CA@SY7PR01MB10921.ausprd01.prod.outlook.com>
<[email protected]>
<CANxoLDcW2iyOq8JJYQXkzXkK6PtQX-z7Hxe=Ri3nQJX6AaPLjw@mail.gmail.com>
<SY7PR01MB10921B6F9D478C3F1B2639901B67CA@SY7PR01MB10921.ausprd01.prod.outlook.com>
<CANxoLDdNsuXxL7yLgqWNaR=VCjqMgJ77oU3OvJ6KwBVKi7NVEw@mail.gmail.com>
<CA+FpmFeMfmRx3fNE97X5VZpLpiausHCKsRG5aEosfTwq_idPNg@mail.gmail.com>
<CANxoLDfA7gVFhpYPOJSScfh=O8Ohj0m_uDM9G=L0SUW+2bfKeg@mail.gmail.com>
<CA+FpmFfTSO5jC9_UYjR1G8ofayijYXubvauHj0TCHUnqaJ0xaA@mail.gmail.com>
<CANxoLDeCXmzMnmhYSqL+kBEVynGQ99TyJp74UWwj_jeGS0J_6g@mail.gmail.com>
<SY7PR01MB1092148A0ADF42B45E8EFB7B7B67AA@SY7PR01MB10921.ausprd01.prod.outlook.com>
I have resolved the review comments. Removed the hardcoded 'utf-8'
encoding, the logic now retrieves the encoding dynamically. This ensures
accuracy because the default encoding for CREATE DATABASE is derived from
the template database (template1) rather than being a static value.
Attached is the *v13 patch*, now ready for further review.
On Fri, Mar 6, 2026 at 8:13 PM Japin Li <[email protected]> wrote:
>
> Hi, Akshay
>
> On Fri, 06 Mar 2026 at 17:21, Akshay Joshi <[email protected]>
> wrote:
> > I have updated the documentation.
> > Attached is the v12 patch, now ready for further review.
> >
>
> Thanks for updating the patch.
>
> I might not have expressed myself clearly in my last email — apologies.
> Here's a diff that should make it clearer.
>
> diff --git a/src/backend/utils/adt/ruleutils.c
> b/src/backend/utils/adt/ruleutils.c
> index bbf82c1d6c0..1ed56ee71ab 100644
> --- a/src/backend/utils/adt/ruleutils.c
> +++ b/src/backend/utils/adt/ruleutils.c
> @@ -13859,7 +13859,6 @@ parse_ddl_options(FunctionCallInfo fcinfo, int
> variadic_start)
> bool *nulls;
> Oid *types;
> int nargs;
> - bool found = false;
>
> /* Extract variadic arguments */
> nargs = extract_variadic_args(fcinfo, variadic_start, true,
> @@ -13887,8 +13886,7 @@ parse_ddl_options(FunctionCallInfo fcinfo, int
> variadic_start)
> {
> char *name;
> bool bval;
> -
> - found = false;
> + bool found = false;
>
> /* Key must not be null */
> if (nulls[i])
> @@ -13925,8 +13923,8 @@ parse_ddl_options(FunctionCallInfo fcinfo, int
> variadic_start)
> if (!parse_bool(valstr, &bval))
> ereport(ERROR,
>
> (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> - errmsg("argument %d:
> invalid value \"%s\" for key \"%s\"",
> - i + 2,
> valstr, name),
> + errmsg("value for option
> \"%s\" at position %d has invalid value \"%s\"",
> + name, i +
> 2, valstr),
> errhint("Valid values
> are: true, false, yes, no, 1, 0, on, off.")));
> pfree(valstr);
> }
> @@ -13934,8 +13932,8 @@ parse_ddl_options(FunctionCallInfo fcinfo, int
> variadic_start)
> {
> ereport(ERROR,
>
> (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> - errmsg("argument %d: value for
> key \"%s\" must be boolean or text type",
> - i + 2, name)));
> + errmsg("value for option \"%s\"
> at position %d has type %s, expected type boolean or text",
> + name, i + 2,
> format_type_be(types[i + 1]))));
> }
>
> /*
> @@ -13983,7 +13981,7 @@ parse_ddl_options(FunctionCallInfo fcinfo, int
> variadic_start)
> /*
> * pg_get_database_ddl
> *
> - * Generate a CREATE DATABASE statement for the specified database name
> or oid.
> + * Generate a CREATE DATABASE statement for the specified database oid.
> *
> * db_oid - OID of the database for which to generate the DDL.
> * options - Variadic name/value pairs to modify the output.
>
> --
> Regards,
> Japin Li
> ChengDu WenWu Information Technology Co., Ltd.
>
Attachments:
[application/octet-stream] v13-0001-Add-pg_get_database_ddl-function-to-reconstruct-ddl.patch (34.3K, 3-v13-0001-Add-pg_get_database_ddl-function-to-reconstruct-ddl.patch)
download | inline diff:
From f0e001b44e85944cf479987f5545fa88a0b56677 Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Fri, 6 Mar 2026 16:46:02 +0530
Subject: [PATCH v13] Add pg_get_database_ddl() function to reconstruct CREATE
DATABASE statements.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This adds a new system function, pg_get_database_ddl(database_name/database_oid, ddl_options),
which reconstructs the CREATE DATABASE statement for a given database name or OID.
Supported ddl_options are 'pretty', 'owner', 'tablespace' and 'defaults' and respective
values could be 'yes'/'on'/true/'1' or 'no'/'off'/false/'0'.
Usage:
SELECT pg_get_database_ddl('regression_utf8', 'owner', 'no', 'defaults', 'yes');
SELECT pg_get_database_ddl('regression_utf8', 'owner', '0', 'tablespace', '0', 'defaults', '1');
SELECT pg_get_database_ddl('regression_utf8', 'owner', 'off', 'pretty', 'on');
SELECT pg_get_database_ddl('regression_utf8', 'owner', false, 'defaults', 'true', 'tablespace', 'no');
Reference: PG-150
Author: Akshay Joshi <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
Reviewed-by: Quan Zongliang <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Chao Li <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 91 +++++
src/backend/catalog/system_functions.sql | 6 +
src/backend/utils/adt/ruleutils.c | 413 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 7 +
src/include/utils/ddl_defaults.h | 35 ++
src/test/regress/expected/database.out | 186 ++++++++++
src/test/regress/sql/database.sql | 120 +++++++
7 files changed, 858 insertions(+)
create mode 100644 src/include/utils/ddl_defaults.h
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 294f45e82a3..6915408ae30 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3845,4 +3845,95 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</sect2>
+ <sect2 id="functions-get-object-ddl">
+ <title>Get Object DDL Functions</title>
+
+ <para>
+ The functions described in <xref linkend="functions-get-object-ddl-table"/>
+ return the Data Definition Language (DDL) statement for any given database object.
+ This feature is implemented as a set of distinct functions, one for each object type.
+ </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_database_ddl</primary>
+ </indexterm>
+ <function>pg_get_database_ddl</function>
+ ( <parameter>database_id</parameter> <type>regdatabase</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>"any"</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE DATABASE</command> statement for the
+ specified database (identified by name or OID) from the system
+ catalogs. The optional variadic arguments are name/value pairs that
+ control the output
+ formatting and content (e.g., <literal>'pretty', true, 'owner', false</literal>).
+ Supported options are explained below.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <parameter>options</parameter> for <function>pg_get_database_ddl</function>
+ provide fine-grained control over the generated SQL. Options are passed as
+ alternating key/value pairs where the key is a text string and the
+ value is either a boolean or a text string representing a boolean
+ (<literal>true</literal>, <literal>false</literal>, <literal>yes</literal>,
+ <literal>no</literal>, <literal>1</literal>, <literal>0</literal>,
+ <literal>on</literal>, <literal>off</literal>):
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>'pretty', true</literal> (or <literal>'pretty', 'yes'</literal>):
+ Formats the output with newlines and indentation for better readability.
+ This option defaults to false.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>'owner', false</literal> (or <literal>'owner', 'no'</literal>):
+ Omits the <literal>OWNER</literal> clause from the reconstructed statement.
+ This option defaults to true.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>'tablespace', false</literal> (or <literal>'tablespace', '0'</literal>):
+ Omits the <literal>TABLESPACE</literal> clause from the reconstructed statement.
+ This option defaults to true.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>'defaults', true</literal> (or <literal>'defaults', '1'</literal>):
+ Includes clauses for parameters that are currently at their default values
+ (e.g., <literal>CONNECTION LIMIT -1</literal>), which are normally omitted for brevity.
+ This option defaults to false.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ </sect2>
+
</sect1>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 1c5b6d6df05..fa48e2f0775 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -372,3 +372,9 @@ CREATE OR REPLACE FUNCTION ts_debug(document text,
BEGIN ATOMIC
SELECT * FROM ts_debug(get_current_ts_config(), $1);
END;
+
+CREATE OR REPLACE FUNCTION
+ pg_get_database_ddl(database_id regdatabase, VARIADIC options "any" DEFAULT NULL)
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl';
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index f16f1535785..e762a628f8c 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -28,6 +28,7 @@
#include "catalog/pg_authid.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_constraint.h"
+#include "catalog/pg_database.h"
#include "catalog/pg_depend.h"
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
@@ -57,8 +58,10 @@
#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
#include "rewrite/rewriteSupport.h"
+#include "utils/acl.h"
#include "utils/array.h"
#include "utils/builtins.h"
+#include "utils/ddl_defaults.h"
#include "utils/fmgroids.h"
#include "utils/guc.h"
#include "utils/hsearch.h"
@@ -89,11 +92,45 @@
#define PRETTYFLAG_INDENT 0x0002
#define PRETTYFLAG_SCHEMA 0x0004
+/* DDL Options flags */
+#define PG_DDL_PRETTY_INDENT 0x00000001
+#define PG_DDL_WITH_DEFAULTS 0x00000002
+#define PG_DDL_NO_OWNER 0x00000004
+#define PG_DDL_NO_TABLESPACE 0x00000008
+
+/*
+ * Structure to define DDL options for parse_ddl_options().
+ * This allows easy addition of new options in the future.
+ */
+typedef struct DDLOptionDef
+{
+ const char *name; /* Option name (case-insensitive) */
+ uint32 flag; /* Flag to set */
+ bool set_on_true; /* If true, set flag when value is true; if
+ * false, set flag when value is false */
+} DDLOptionDef;
+
+/*
+ * Array of supported DDL options.
+ * To add a new option, simply add an entry to this array.
+ */
+static const DDLOptionDef ddl_option_defs[] = {
+ {"pretty", PG_DDL_PRETTY_INDENT, true},
+ {"defaults", PG_DDL_WITH_DEFAULTS, true},
+ {"owner", PG_DDL_NO_OWNER, false},
+ {"tablespace", PG_DDL_NO_TABLESPACE, false},
+};
+
+
/* Standard conversion of a "bool pretty" option to detailed flags */
#define GET_PRETTY_FLAGS(pretty) \
((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
: PRETTYFLAG_INDENT)
+#define GET_DDL_PRETTY_FLAGS(pretty) \
+ ((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
+ : 0)
+
/* Default line length for pretty-print wrapping: 0 means wrap always */
#define WRAP_COLUMN_DEFAULT 0
@@ -547,6 +584,11 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
deparse_context *context,
bool showimplicit,
bool needcomma);
+static void get_formatted_string(StringInfo buf,
+ int prettyFlags,
+ int nSpaces,
+ const char *fmt,...) pg_attribute_printf(4, 5);
+static char *pg_get_database_ddl_worker(Oid db_oid, uint32 ddl_flags);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -13760,3 +13802,374 @@ get_range_partbound_string(List *bound_datums)
return buf.data;
}
+
+/*
+ * get_formatted_string
+ *
+ * Return a formatted version of the string.
+ *
+ * prettyFlags - Based on prettyFlags the output includes spaces and
+ * newlines (\n).
+ * nSpaces - indent with specified number of space characters.
+ * fmt - printf-style format string used by appendStringInfoVA.
+ */
+static void
+get_formatted_string(StringInfo buf, int prettyFlags, int nSpaces, const char *fmt,...)
+{
+ va_list args;
+
+ if (prettyFlags & PRETTYFLAG_INDENT)
+ {
+ appendStringInfoChar(buf, '\n');
+ /* Indent with spaces */
+ appendStringInfoSpaces(buf, nSpaces);
+ }
+ else
+ appendStringInfoChar(buf, ' ');
+
+ for (;;)
+ {
+ int needed;
+
+ va_start(args, fmt);
+ needed = appendStringInfoVA(buf, fmt, args);
+ va_end(args);
+ if (needed == 0)
+ break;
+ enlargeStringInfo(buf, needed);
+ }
+}
+
+/*
+ * parse_ddl_options - Generic helper to parse variadic name/value options
+ * fcinfo: The FunctionCallInfo from the calling function
+ * variadic_start: The argument position where variadic arguments start
+ *
+ * Returns: Bitmask of flags based on the parsed options.
+ *
+ * Options are passed as name/value pairs.
+ * For example: pg_get_database_ddl('mydb', 'owner', false, 'pretty', true)
+ */
+static uint32
+parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start)
+{
+ uint32 flags = 0;
+ uint32 seen_flags = 0;
+ Datum *args;
+ bool *nulls;
+ Oid *types;
+ int nargs;
+
+ /* Extract variadic arguments */
+ nargs = extract_variadic_args(fcinfo, variadic_start, true,
+ &args, &types, &nulls);
+
+ /* If no options provided (VARIADIC NULL), return the empty bitmask */
+ if (nargs <= 0)
+ return flags;
+
+ /*
+ * Handle the case where DEFAULT NULL was used and no explicit variadic
+ * arguments were provided. In this case, we get a single NULL argument.
+ */
+ if (nargs == 1 && nulls[0])
+ return flags;
+
+ /* Arguments must come in name/value pairs */
+ 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 (int i = 0; i < nargs; i += 2)
+ {
+ char *name;
+ bool bval;
+ bool found = false;
+
+ /* Key must not be null */
+ if (nulls[i])
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("name at variadic position %d is null", i + 1)));
+
+ /* Key must be text type */
+ if (types[i] != TEXTOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("name at variadic position %d has type %s, expected type %s",
+ i + 1, format_type_be(types[i]),
+ format_type_be(TEXTOID))));
+
+ name = TextDatumGetCString(args[i]);
+
+ /* Value must not be null */
+ if (nulls[i + 1])
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("value for option \"%s\" must not be null",
+ name)));
+
+ /* Value must be boolean or text type */
+ if (types[i + 1] == BOOLOID)
+ {
+ bval = DatumGetBool(args[i + 1]);
+ }
+ else if (types[i + 1] == TEXTOID)
+ {
+ char *valstr = TextDatumGetCString(args[i + 1]);
+
+ if (!parse_bool(valstr, &bval))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("value for option \"%s\" at position %d has invalid value \"%s\"",
+ name, i + 2, valstr),
+ errhint("Valid values are: true, false, yes, no, 1, 0, on, off.")));
+ pfree(valstr);
+ }
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("value for option \"%s\" at position %d has type %s, expected type boolean or text",
+ name, i + 2, format_type_be(types[i + 1]))));
+ }
+
+ /*
+ * Look up the option in the ddl_option_defs array and set the
+ * appropriate flag based on the value.
+ */
+ for (int j = 0; j < lengthof(ddl_option_defs); j++)
+ {
+ const DDLOptionDef *opt = &ddl_option_defs[j];
+
+ if (pg_strcasecmp(name, opt->name) == 0)
+ {
+ /* Error if this option was already specified */
+ if (seen_flags & opt->flag)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("option \"%s\" is specified more than once", name)));
+
+ seen_flags |= opt->flag;
+
+ /*
+ * Set the flag if the value matches the set_on_true
+ * condition: if set_on_true is true, set flag when bval is
+ * true; if set_on_true is false, set flag when bval is false.
+ */
+ if (bval == opt->set_on_true)
+ flags |= opt->flag;
+
+ found = true;
+ break;
+ }
+ }
+
+ if (!found)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unrecognized option: \"%s\"", name)));
+
+ pfree(name);
+ }
+
+ return flags;
+}
+
+/*
+ * pg_get_database_ddl
+ *
+ * Generate a CREATE DATABASE statement for the specified database oid.
+ *
+ * db_oid - OID of the database for which to generate the DDL.
+ * options - Variadic name/value pairs to modify the output.
+ */
+Datum
+pg_get_database_ddl(PG_FUNCTION_ARGS)
+{
+ Oid db_oid;
+ uint32 ddl_flags;
+ char *res;
+
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ db_oid = PG_GETARG_OID(0);
+
+ /* Parse variadic options starting from argument 1 */
+ ddl_flags = parse_ddl_options(fcinfo, 1);
+
+ res = pg_get_database_ddl_worker(db_oid, ddl_flags);
+
+ PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+static char *
+pg_get_database_ddl_worker(Oid db_oid, uint32 ddl_flags)
+{
+ const char *encoding;
+ bool attr_isnull;
+ Datum dbvalue;
+ HeapTuple tuple_database;
+ Form_pg_database dbform;
+ StringInfoData buf;
+ AclResult aclresult;
+ HeapTuple tmpl_tuple;
+ int tmpl_encoding = -1;
+
+ /* Variables for ddl_options parsing */
+ int pretty_flags = 0;
+ bool is_with_defaults = false;
+
+ /* Set the appropriate flags */
+ if (ddl_flags & PG_DDL_PRETTY_INDENT)
+ pretty_flags = GET_DDL_PRETTY_FLAGS(1);
+
+ is_with_defaults = (ddl_flags & PG_DDL_WITH_DEFAULTS) != 0;
+
+ /*
+ * User must have connect privilege for target database.
+ */
+ aclresult = object_aclcheck(DatabaseRelationId, db_oid, GetUserId(),
+ ACL_CONNECT);
+ if (aclresult != ACLCHECK_OK &&
+ !has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_STATS))
+ {
+ aclcheck_error(aclresult, OBJECT_DATABASE,
+ get_database_name(db_oid));
+ }
+
+ /* Look up the database in pg_database */
+ tuple_database = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(db_oid));
+ if (!HeapTupleIsValid(tuple_database))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("database with oid %u does not exist", db_oid));
+
+ dbform = (Form_pg_database) GETSTRUCT(tuple_database);
+
+ initStringInfo(&buf);
+
+ /* Build the CREATE DATABASE statement */
+ appendStringInfo(&buf, "CREATE DATABASE %s",
+ quote_identifier(dbform->datname.data));
+ get_formatted_string(&buf, pretty_flags, 4, "WITH");
+
+ /* Set the OWNER in the DDL if owner is not omitted */
+ if (OidIsValid(dbform->datdba) && !(ddl_flags & PG_DDL_NO_OWNER))
+ {
+ char *dbowner = GetUserNameFromId(dbform->datdba, false);
+
+ get_formatted_string(&buf, pretty_flags, 8, "OWNER = %s",
+ quote_identifier(dbowner));
+ }
+
+ /*
+ * Emit ENCODING if it differs from template1's encoding, or if defaults
+ * are requested. The default encoding for CREATE DATABASE comes from the
+ * template database (template1), not a fixed value.
+ */
+ encoding = pg_encoding_to_char(dbform->encoding);
+
+
+ tmpl_tuple = SearchSysCache1(DATABASEOID,
+ ObjectIdGetDatum(Template1DbOid));
+ if (HeapTupleIsValid(tmpl_tuple))
+ {
+ Form_pg_database tmplform = (Form_pg_database) GETSTRUCT(tmpl_tuple);
+
+ tmpl_encoding = tmplform->encoding;
+ ReleaseSysCache(tmpl_tuple);
+ }
+
+ if (is_with_defaults || dbform->encoding != tmpl_encoding)
+ get_formatted_string(&buf, pretty_flags, 8, "ENCODING = %s",
+ quote_literal_cstr(encoding));
+
+
+ /* Fetch the value of LC_COLLATE */
+ dbvalue = SysCacheGetAttr(DATABASEOID, tuple_database,
+ Anum_pg_database_datcollate, &attr_isnull);
+ if (!attr_isnull)
+ get_formatted_string(&buf, pretty_flags, 8, "LC_COLLATE = %s",
+ quote_literal_cstr(TextDatumGetCString(dbvalue)));
+ /* Fetch the value of LC_CTYPE */
+ dbvalue = SysCacheGetAttr(DATABASEOID, tuple_database,
+ Anum_pg_database_datctype, &attr_isnull);
+ if (!attr_isnull)
+ get_formatted_string(&buf, pretty_flags, 8, "LC_CTYPE = %s",
+ quote_literal_cstr(TextDatumGetCString(dbvalue)));
+ /* Fetch the value of LOCALE */
+ dbvalue = SysCacheGetAttr(DATABASEOID, tuple_database,
+ Anum_pg_database_datlocale, &attr_isnull);
+ if (!attr_isnull && dbform->datlocprovider == COLLPROVIDER_BUILTIN)
+ get_formatted_string(&buf, pretty_flags, 8, "BUILTIN_LOCALE = %s",
+ quote_literal_cstr(TextDatumGetCString(dbvalue)));
+ else if (!attr_isnull && dbform->datlocprovider == COLLPROVIDER_ICU)
+ get_formatted_string(&buf, pretty_flags, 8, "ICU_LOCALE = %s",
+ quote_literal_cstr(TextDatumGetCString(dbvalue)));
+ else if (!attr_isnull)
+ get_formatted_string(&buf, pretty_flags, 8, "LOCALE = %s",
+ quote_literal_cstr(TextDatumGetCString(dbvalue)));
+
+ /* Fetch the value of ICU_RULES */
+ dbvalue = SysCacheGetAttr(DATABASEOID, tuple_database,
+ Anum_pg_database_daticurules, &attr_isnull);
+ if (!attr_isnull && dbform->datlocprovider == COLLPROVIDER_ICU)
+ get_formatted_string(&buf, pretty_flags, 8, "ICU_RULES = %s",
+ quote_literal_cstr(TextDatumGetCString(dbvalue)));
+
+ /* Fetch the value of COLLATION_VERSION */
+ dbvalue = SysCacheGetAttr(DATABASEOID, tuple_database,
+ Anum_pg_database_datcollversion, &attr_isnull);
+ if (!attr_isnull)
+ get_formatted_string(&buf, pretty_flags, 8, "COLLATION_VERSION = %s",
+ quote_literal_cstr(TextDatumGetCString(dbvalue)));
+
+ /* Set the appropriate LOCALE_PROVIDER */
+ if (dbform->datlocprovider == COLLPROVIDER_BUILTIN)
+ get_formatted_string(&buf, pretty_flags, 8, "LOCALE_PROVIDER = builtin");
+ else if (dbform->datlocprovider == COLLPROVIDER_ICU)
+ get_formatted_string(&buf, pretty_flags, 8, "LOCALE_PROVIDER = icu");
+ else if (is_with_defaults)
+ get_formatted_string(&buf, pretty_flags, 8, "LOCALE_PROVIDER = libc");
+
+ /* Set the TABLESPACE in the DDL if tablespace is not omitted */
+ if (OidIsValid(dbform->dattablespace) && !(ddl_flags & PG_DDL_NO_TABLESPACE))
+ {
+ /* Get the tablespace name respective to the given tablespace oid */
+ char *dbTablespace = get_tablespace_name(dbform->dattablespace);
+
+ /* If it's with defaults, we skip default tablespace check */
+ if (is_with_defaults ||
+ (pg_strcasecmp(dbTablespace, DDL_DEFAULTS.DATABASE.TABLESPACE) != 0))
+ get_formatted_string(&buf, pretty_flags, 8, "TABLESPACE = %s",
+ quote_identifier(dbTablespace));
+ }
+
+ if (is_with_defaults ||
+ (dbform->datallowconn != DDL_DEFAULTS.DATABASE.ALLOW_CONN))
+ {
+ get_formatted_string(&buf, pretty_flags, 8, "ALLOW_CONNECTIONS = %s",
+ dbform->datallowconn ? "true" : "false");
+ }
+
+ if (is_with_defaults ||
+ (dbform->datconnlimit != DDL_DEFAULTS.DATABASE.CONN_LIMIT))
+ {
+ get_formatted_string(&buf, pretty_flags, 8, "CONNECTION LIMIT = %d",
+ dbform->datconnlimit);
+ }
+
+ if (is_with_defaults || dbform->datistemplate)
+ get_formatted_string(&buf, pretty_flags, 8, "IS_TEMPLATE = %s",
+ dbform->datistemplate ? "true" : "false");
+
+ appendStringInfoChar(&buf, ';');
+
+ ReleaseSysCache(tuple_database);
+
+ return buf.data;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 361e2cfffeb..1e2f7d3ac35 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4034,6 +4034,13 @@
proname => 'pg_get_function_sqlbody', provolatile => 's',
prorettype => 'text', proargtypes => 'oid',
prosrc => 'pg_get_function_sqlbody' },
+{ oid => '9492', descr => 'get CREATE statement for database name and oid',
+ proname => 'pg_get_database_ddl', provariadic => 'any', proisstrict => 'f',
+ provolatile => 's', prorettype => 'text',
+ proargtypes => 'regdatabase any',
+ proargmodes => '{i,v}',
+ proallargtypes => '{regdatabase,any}',
+ prosrc => 'pg_get_database_ddl' },
{ oid => '1686', descr => 'list of SQL keywords',
proname => 'pg_get_keywords', procost => '10', prorows => '500',
diff --git a/src/include/utils/ddl_defaults.h b/src/include/utils/ddl_defaults.h
new file mode 100644
index 00000000000..eb9c7750651
--- /dev/null
+++ b/src/include/utils/ddl_defaults.h
@@ -0,0 +1,35 @@
+/*-------------------------------------------------------------------------
+ *
+ * ddl_defaults.h
+ * Declarations for DDL defaults.
+ *
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/utils/ddl_defaults.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef DDL_DEFAULTS_H
+#define DDL_DEFAULTS_H
+
+static const struct
+{
+ struct
+ {
+ const char *TABLESPACE;
+ int CONN_LIMIT;
+ bool ALLOW_CONN;
+ } DATABASE;
+
+ /* Add more object types as needed */
+} DDL_DEFAULTS = {
+
+ .DATABASE = {
+ .TABLESPACE = "pg_default",
+ .CONN_LIMIT = -1,
+ .ALLOW_CONN = true,
+ }
+};
+
+#endif /* DDL_DEFAULTS_H */
diff --git a/src/test/regress/expected/database.out b/src/test/regress/expected/database.out
index 6b879b0f62a..76eca22cf3a 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -1,3 +1,65 @@
+--
+-- Reconstruct DDL
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- functions 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'
+ );
+
+ -- Remove ENCODING assignments
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*ENCODING\s*=\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
CREATE DATABASE regression_tbd
ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
ALTER DATABASE regression_tbd RENAME TO regression_utf8;
@@ -16,6 +78,130 @@ CREATE ROLE regress_datdba_before;
CREATE ROLE regress_datdba_after;
ALTER DATABASE regression_utf8 OWNER TO regress_datdba_before;
REASSIGN OWNED BY regress_datdba_before TO regress_datdba_after;
+-- Test pg_get_database_ddl
+-- Database doesn't exists
+SELECT pg_get_database_ddl('regression_database');
+ERROR: database "regression_database" does not exist
+LINE 1: SELECT pg_get_database_ddl('regression_database');
+ ^
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+ pg_get_database_ddl
+---------------------
+
+(1 row)
+
+-- Without options
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+ ddl_filter
+-------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Owner
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', false));
+ ddl_filter
+--------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults', true, 'tablespace', false));
+ ddl_filter
+----------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123 IS_TEMPLATE = false;
+(1 row)
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults', true));
+ ddl_filter
+----------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123 IS_TEMPLATE = false;
+(1 row)
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', false, 'tablespace', false, 'defaults', true));
+ ddl_filter
+-----------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123 IS_TEMPLATE = false;
+(1 row)
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ OWNER = regress_datdba_after
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With No Owner and No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true, 'owner', false, 'tablespace', false));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true, 'defaults', true));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ OWNER = regress_datdba_after
+ TABLESPACE = pg_default
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123
+ IS_TEMPLATE = false;
+(1 row)
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true, 'owner', false, 'tablespace', false, 'defaults', true));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123
+ IS_TEMPLATE = false;
+(1 row)
+-- Test with text values: 'yes', 'no', '1', '0', 'on', 'off'
+\pset format aligned
+-- Using 'yes' and 'no'
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', 'no', 'defaults', 'yes'));
+ ddl_filter
+-----------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123 IS_TEMPLATE = false;
+(1 row)
+
+-- Using '1' and '0'
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', '0', 'tablespace', '0', 'defaults', '1'));
+ ddl_filter
+-----------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123 IS_TEMPLATE = false;
+(1 row)
+
+-- Using 'on' and 'off'
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', 'off', 'pretty', 'on'));
+ ddl_filter
+---------------------------------
+ CREATE DATABASE regression_utf8+
+ WITH +
+ CONNECTION LIMIT = 123;
+(1 row)
+
+-- Mixed boolean and text values
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', false, 'defaults', 'true', 'tablespace', 'no'));
+ ddl_filter
+-----------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123 IS_TEMPLATE = false;
+(1 row)
+
+-- Test duplicate option (should error)
+SELECT pg_get_database_ddl('regression_utf8', 'owner', false, 'owner', true);
+ERROR: option "owner" is specified more than once
+-- Test invalid text value (should error)
+SELECT pg_get_database_ddl('regression_utf8', 'owner', 'invalid');
+ERROR: value for option "owner" at position 2 has invalid value "invalid"
+HINT: Valid values are: true, false, yes, no, 1, 0, on, off.
DROP DATABASE regression_utf8;
+DROP FUNCTION ddl_filter(text);
DROP ROLE regress_datdba_before;
DROP ROLE regress_datdba_after;
diff --git a/src/test/regress/sql/database.sql b/src/test/regress/sql/database.sql
index 4ef36127291..9ef926fac9c 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -1,3 +1,67 @@
+--
+-- Reconstruct DDL
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- functions 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'
+ );
+
+ -- Remove ENCODING assignments
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*ENCODING\s*=\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
+
CREATE DATABASE regression_tbd
ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
ALTER DATABASE regression_tbd RENAME TO regression_utf8;
@@ -19,6 +83,62 @@ CREATE ROLE regress_datdba_after;
ALTER DATABASE regression_utf8 OWNER TO regress_datdba_before;
REASSIGN OWNED BY regress_datdba_before TO regress_datdba_after;
+-- Test pg_get_database_ddl
+-- Database doesn't exists
+SELECT pg_get_database_ddl('regression_database');
+
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+
+-- Without options
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+
+-- With No Owner
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', false));
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults', true, 'tablespace', false));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults', true));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', false, 'tablespace', false, 'defaults', true));
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true));
+
+-- With No Owner and No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true, 'owner', false, 'tablespace', false));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true, 'defaults', true));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true, 'owner', false, 'tablespace', false, 'defaults', true));
+
+-- Test with text values: 'yes', 'no', '1', '0', 'on', 'off'
+\pset format aligned
+-- Using 'yes' and 'no'
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', 'no', 'defaults', 'yes'));
+
+-- Using '1' and '0'
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', '0', 'tablespace', '0', 'defaults', '1'));
+
+-- Using 'on' and 'off'
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', 'off', 'pretty', 'on'));
+
+-- Mixed boolean and text values
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', false, 'defaults', 'true', 'tablespace', 'no'));
+
+-- Test duplicate option (should error)
+SELECT pg_get_database_ddl('regression_utf8', 'owner', false, 'owner', true);
+
+-- Test invalid text value (should error)
+SELECT pg_get_database_ddl('regression_utf8', 'owner', 'invalid');
+
DROP DATABASE regression_utf8;
+DROP FUNCTION ddl_filter(text);
DROP ROLE regress_datdba_before;
DROP ROLE regress_datdba_after;
--
2.51.0
view thread (41+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
In-Reply-To: <CANxoLDf+Ak=S+V1Rx25Eqh7r6cuWZtHYiEo_AZsn9eqiWbVGEg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox