public inbox for [email protected]
help / color / mirror / Atom feedRe: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
6+ messages / 3 participants
[nested] [flat]
* Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
@ 2026-01-07 23:27 Andrew Dunstan <[email protected]>
2026-01-14 15:48 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Andrew Dunstan @ 2026-01-07 23:27 UTC (permalink / raw)
To: Euler Taveira <[email protected]>; Akshay Joshi <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; Chao Li <[email protected]>; japin <[email protected]>; Quan Zongliang <[email protected]>; pgsql-hackers
On 2025-12-12 Fr 10:19 AM, Euler Taveira wrote:
> On Fri, Dec 12, 2025, at 7:52 AM, Akshay Joshi wrote:
>> On Thu, Dec 11, 2025 at 7:29 PM Euler Taveira<[email protected]> wrote:
>> Is there any way to obtain the default values directly from the source
>> code itself, or do I need to refer to the documentation? If we rely on
>> the documentation and compare against that, then in the future, if the
>> default values change, we would also need to update our logic
>> accordingly.
>>
> No, you need to check the documentation. If you are changing the default value,
> you are breaking compatibility; that rarely happens. If we are really concern
> about this fact, you can add a test case that creates the object without
> properties (all default values) and another with all default properties and
> then compare the output.
Maybe the function should have a VERBOSE option that emits all the defaults.
>
>> Constantly having to check the documentation for default values may
>> feel annoying to some users. Some users run queries with parameters
>> such as encoding, connection limit, and locale using their default
>> values. When they call the pg_get_database_ddl function, it
>> reconstructs the short command based on those defaults.
>>
> Encoding and locale, ok but I doubt about connection limit.
>
> postgres=# SELECT current_user;
> current_user
> --------------
> euler
> (1 row)
>
> postgres=# CREATE DATABASE foo;
> CREATE DATABASE
> postgres=# CREATE DATABASE bar OWNER euler;
> CREATE DATABASE
>
> When you are learning a new command, you generally don't set the default value
> for a property just to be correct. I'm not saying this function shouldn't
> include OWNER. I'm just suggesting it to be optional. See some arguments
> below.
>
>>> * OWNER. There is no guarantee that the owner exists in the cluster you will
>>> use this output. That's something that pg_dumpall treats separately (see
>>> above). Does it mean we should include the owner? No. We can make it an
>>> option.
>>>
>> If I understand correctly, the owner should be an option provided by
>> the caller of the function, and we reconstruct the Database DDL using
>> that specified owner. Is that right?
>> If so, then in my humble opinion, this is not truly a reconstruction
>> of the existing database object.
>>
> No. My idea is to have something like the pg_dump --no-owner option. This is
> important if you are transporting the objects from one cluster to another one.
> Owner might be different. That's why I'm suggesting it should be optional. It
> means flexibility. See pg_dump output format that always apply the OWNER as a
> separate ALTER command.
+1
>
>>> * options. Since I mentioned options for some properties (owner, strategy,
>>> template), these properties can be accommodated as a VARIADIC argument. The
>>> function signature can be something like
>>>
>>> pg_get_database_ddl(oid, VARIADIC options text[])
>>>
>>> I would include the pretty print into options too.
>>>
>> Same comment as the one I gave for the Owner, if you are referring to
>> these as options to the function.
>>
> Let me elaborate a bit. As I suggested you can control the output with options.
> Why? Flexibility.
>
> Why am I suggesting such a general purpose implementation? See some of the use
> cases.
>
> 1. object DDL. Check DDL to recreate the object. It is not the exact DDL that
> the user informed but it produces the same result.
> 2. clone tool. Clone the objects to recreate the environment for another
> customer. These objects can be created in the same cluster or in another one.
> (Of course, global objects don't apply for the same cluster.)
> 3. dump tool. Dump the commands to recreate the existing objects.
> 4. diff tool. There are tools like pgquarrel [1] that queries the catalog and
> compare the results to create commands to turn the target database into the
> source database. The general purpose functions can be used if the object
> doesn't exist in the target database. (Of course, it doesn't apply for global
> objects but again it is a good UI to have all of these pg_get_OBJECT_ddl
> functions using the same approach.)
> 5. logical replication. These pg_get_OBJECT_ddl functions can be good
> candidates to be used in the initial schema replication and even in the DDL
> replication (if the object doesn't exist in the target database).
>
> The "options" parameter is to get the DDL command to serve any of these use
> cases. There are some properties in a certain object that you *don't* want for
> whatever reason. See some --no-OBJECT options in pg_dump. Let's say you don't
> want the TABLESPACE or the table access method while getting the CREATE TABLE
> DDL because it is different in the other database.
+1
>
>> I received a review comment suggesting the use of tabs. I also looked
>> up PostgreSQL best practices on google, which recommend using tabs for
>> indentation and spaces for alignment. I’m open to updating my code
>> accordingly.
>>
> I didn't check all of the possible output but the majority uses space instead
> of tabs. Check psql. If you check the git history (git log --grep=tabs), you
> will notice that tabs are removed from source code.
We should follow the pretty printing style in ruleutils.c, which uses
spaces.
>
>>> * permission. I don't think you need to check for permissions inside the
>>> function. I wouldn't want a different behavior than pg_dump(all). You can
>>> always adjust it in system_functions.sql.
>>>
>> We’ve already had extensive discussions on this topic in the same
>> email thread, and ultimately we decided to add the permission check.
>>
> That's fair. Again, I expect that all of these pg_get_OBJECT_ddl functions use
> the same approach. We can always relax this restriction in the future.
+1
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
2026-01-07 23:27 Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Andrew Dunstan <[email protected]>
@ 2026-01-14 15:48 ` Akshay Joshi <[email protected]>
2026-01-16 07:23 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Akshay Joshi @ 2026-01-14 15:48 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: Euler Taveira <[email protected]>; Álvaro Herrera <[email protected]>; Chao Li <[email protected]>; japin <[email protected]>; Quan Zongliang <[email protected]>; pgsql-hackers
I have incorporated Euler’s changes, which modify the declaration and
definition of the *pg_get_database_ddl* function. Please find the attached
v6 patch, which is now ready for review. The following updates have been
made:
1.
Function signature updated to: pg_get_database_ddl(database_id
regdatabase, VARIADIC ddl_options text[])
2.
Added options *--no-owner* and *--no-tablespace* to omit the OWNER and
TABLESPACE clauses from the reconstructed DDL.
3.
Moved the "*pretty*" parameter into ddl_options for formatted output.
4.
Properties such as ENCODING, LOCALE_PROVIDER, TABLESPACE, CONNECTION
LIMIT, and ALLOW_CONNECTIONS are omitted if they are set to default values.
5.
Introduced the *--with-defaults* option to include clauses for
parameters even when they are at their default values.
6.
Standardized formatting to use spaces instead of tabs.
*Usage examples:*
1. SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
2. SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
3. SELECT pg_get_database_ddl('postgres', 'pretty'); // pretty-formatted
DDL
4. SELECT pg_get_database_ddl('postgres', '--no-owner',
'--no-tablespace'); // Omits the Owner and Tablespace clause from the DDL.
5. SELECT pg_get_database_ddl('postgres', 'pretty', '--with-defaults');
// Includes clauses for parameters that are currently at their default
values.
On Thu, Jan 8, 2026 at 4:57 AM Andrew Dunstan <[email protected]> wrote:
>
> On 2025-12-12 Fr 10:19 AM, Euler Taveira wrote:
>
> On Fri, Dec 12, 2025, at 7:52 AM, Akshay Joshi wrote:
>
> On Thu, Dec 11, 2025 at 7:29 PM Euler Taveira <[email protected]> <[email protected]> wrote:
>
> Is there any way to obtain the default values directly from the source
> code itself, or do I need to refer to the documentation? If we rely on
> the documentation and compare against that, then in the future, if the
> default values change, we would also need to update our logic
> accordingly.
>
>
> No, you need to check the documentation. If you are changing the default value,
> you are breaking compatibility; that rarely happens. If we are really concern
> about this fact, you can add a test case that creates the object without
> properties (all default values) and another with all default properties and
> then compare the output.
>
>
> Maybe the function should have a VERBOSE option that emits all the
> defaults.
>
>
> Constantly having to check the documentation for default values may
> feel annoying to some users. Some users run queries with parameters
> such as encoding, connection limit, and locale using their default
> values. When they call the pg_get_database_ddl function, it
> reconstructs the short command based on those defaults.
>
>
> Encoding and locale, ok but I doubt about connection limit.
>
> postgres=# SELECT current_user;
> current_user
> --------------
> euler
> (1 row)
>
> postgres=# CREATE DATABASE foo;
> CREATE DATABASE
> postgres=# CREATE DATABASE bar OWNER euler;
> CREATE DATABASE
>
> When you are learning a new command, you generally don't set the default value
> for a property just to be correct. I'm not saying this function shouldn't
> include OWNER. I'm just suggesting it to be optional. See some arguments
> below.
>
>
> * OWNER. There is no guarantee that the owner exists in the cluster you will
> use this output. That's something that pg_dumpall treats separately (see
> above). Does it mean we should include the owner? No. We can make it an
> option.
>
>
> If I understand correctly, the owner should be an option provided by
> the caller of the function, and we reconstruct the Database DDL using
> that specified owner. Is that right?
> If so, then in my humble opinion, this is not truly a reconstruction
> of the existing database object.
>
>
> No. My idea is to have something like the pg_dump --no-owner option. This is
> important if you are transporting the objects from one cluster to another one.
> Owner might be different. That's why I'm suggesting it should be optional. It
> means flexibility. See pg_dump output format that always apply the OWNER as a
> separate ALTER command.
>
>
> +1
>
>
> * options. Since I mentioned options for some properties (owner, strategy,
> template), these properties can be accommodated as a VARIADIC argument. The
> function signature can be something like
>
> pg_get_database_ddl(oid, VARIADIC options text[])
>
> I would include the pretty print into options too.
>
>
> Same comment as the one I gave for the Owner, if you are referring to
> these as options to the function.
>
>
> Let me elaborate a bit. As I suggested you can control the output with options.
> Why? Flexibility.
>
> Why am I suggesting such a general purpose implementation? See some of the use
> cases.
>
> 1. object DDL. Check DDL to recreate the object. It is not the exact DDL that
> the user informed but it produces the same result.
> 2. clone tool. Clone the objects to recreate the environment for another
> customer. These objects can be created in the same cluster or in another one.
> (Of course, global objects don't apply for the same cluster.)
> 3. dump tool. Dump the commands to recreate the existing objects.
> 4. diff tool. There are tools like pgquarrel [1] that queries the catalog and
> compare the results to create commands to turn the target database into the
> source database. The general purpose functions can be used if the object
> doesn't exist in the target database. (Of course, it doesn't apply for global
> objects but again it is a good UI to have all of these pg_get_OBJECT_ddl
> functions using the same approach.)
> 5. logical replication. These pg_get_OBJECT_ddl functions can be good
> candidates to be used in the initial schema replication and even in the DDL
> replication (if the object doesn't exist in the target database).
>
> The "options" parameter is to get the DDL command to serve any of these use
> cases. There are some properties in a certain object that you *don't* want for
> whatever reason. See some --no-OBJECT options in pg_dump. Let's say you don't
> want the TABLESPACE or the table access method while getting the CREATE TABLE
> DDL because it is different in the other database.
>
>
> +1
>
>
> I received a review comment suggesting the use of tabs. I also looked
> up PostgreSQL best practices on google, which recommend using tabs for
> indentation and spaces for alignment. I’m open to updating my code
> accordingly.
>
>
> I didn't check all of the possible output but the majority uses space instead
> of tabs. Check psql. If you check the git history (git log --grep=tabs), you
> will notice that tabs are removed from source code.
>
>
>
> We should follow the pretty printing style in ruleutils.c, which uses
> spaces.
>
>
> * permission. I don't think you need to check for permissions inside the
> function. I wouldn't want a different behavior than pg_dump(all). You can
> always adjust it in system_functions.sql.
>
>
> We’ve already had extensive discussions on this topic in the same
> email thread, and ultimately we decided to add the permission check.
>
>
> That's fair. Again, I expect that all of these pg_get_OBJECT_ddl functions use
> the same approach. We can always relax this restriction in the future.
>
>
>
> +1
>
>
> cheers
>
>
> andrew
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
>
Attachments:
[application/octet-stream] v6-0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch (26.4K, 3-v6-0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From 380108794f704e34971c5899aa4b63502b494c0a Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Wed, 24 Sep 2025 17:47:59 +0530
Subject: [PATCH v6] 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 database oid.
Supported ddl_options are 'pretty', '--no-owner', '--no-tablespace' and '--with-defaults'.
Usage:
SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
SELECT pg_get_database_ddl('postgres', 'pretty'); // pretty-formatted DDL
SELECT pg_get_database_ddl('postgres', '--no-owner', '--no-tablespace'); // Omits the Owner and Tablespace clause from the DDL.
SELECT pg_get_database_ddl('postgres', 'pretty', '--with-defaults'); // Includes clauses for parameters that are currently at their default values.
Reference: PG-150
Author: Akshay Joshi <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Reviewed-by: Quan Zongliang <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 77 ++++++
src/backend/catalog/system_functions.sql | 6 +
src/backend/utils/adt/ruleutils.c | 291 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +
src/include/utils/ddl_defaults.h | 37 +++
src/test/regress/expected/database.out | 134 +++++++++++
src/test/regress/sql/database.sql | 91 +++++++
7 files changed, 642 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 175f18315cd..2250031bb83 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3830,4 +3830,81 @@ 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 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>ddl_options</parameter> <type>text[]</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE DATABASE</command> statement from the
+ system catalogs for a specified database. The first argument is the OID or
+ name of the database. The optional variadic argument is an array of text
+ flags to control the output. Supported options include
+ <literal>pretty</literal>, <literal>--no-owner</literal>,
+ <literal>--no-tablespace</literal>, and <literal>--with-defaults</literal>.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <parameter>ddl_options</parameter> for <function>pg_get_database_ddl</function>
+ provide fine-grained control over the generated SQL:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>pretty</literal>: Adds newlines and indentation for better readability.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>--no-owner</literal>: Omits the <literal>OWNER</literal> clause from
+ the reconstructed statement.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>--no-tablespace</literal>: Omits the <literal>TABLESPACE</literal> clause.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>--with-defaults</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.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ </sect2>
+
</sect1>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index eb9e31ae1bf..16c0d52479a 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -657,6 +657,12 @@ LANGUAGE INTERNAL
STRICT VOLATILE PARALLEL UNSAFE
AS 'pg_replication_origin_session_setup';
+CREATE OR REPLACE FUNCTION
+ pg_get_database_ddl(database_id regdatabase, VARIADIC ddl_options text[] DEFAULT '{}')
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl';
+
--
-- The default permissions for functions mean that anyone can execute them.
-- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 033b625f3fc..4c6c8532bf3 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,22 @@
#define PRETTYFLAG_INDENT 0x0002
#define PRETTYFLAG_SCHEMA 0x0004
+/* DDL Options flags */
+#define PG_DDL_PRETTY_INDENT 0x0001
+#define PG_DDL_WITH_DEFAULTS 0x0002
+#define PG_DDL_NO_OWNER 0x0004
+#define PG_DDL_NO_TABLESPACE 0x0008
+
+
/* 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
@@ -546,6 +560,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, ArrayType *ddl_options);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -13743,3 +13762,275 @@ 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 */
+ for (int i = 0; i < nSpaces; i++)
+ {
+ appendStringInfoChar(buf, ' ');
+ }
+ }
+ else
+ appendStringInfoChar(buf, ' ');
+
+ va_start(args, fmt);
+ appendStringInfoVA(buf, fmt, args);
+ va_end(args);
+}
+
+/**
+ * parse_ddl_options - Generic helper to parse variadic text options
+ * ddl_options: The ArrayType from PG_GETARG_ARRAYTYPE_P
+ * flags: Bitmask to set options while parsing DDL options.
+ */
+static void
+parse_ddl_options(ArrayType *ddl_options, int *flags)
+{
+ Datum *options;
+ bool *nulls;
+ int n_options;
+ int i;
+
+ /* Default to 0 (all options off) */
+ if (flags)
+ *flags = 0;
+
+ if (ddl_options == NULL || flags == NULL)
+ return;
+
+ deconstruct_array(ddl_options,
+ TEXTOID, -1, false, 'i',
+ &options, &nulls, &n_options);
+
+ for (i = 0; i < n_options; i++)
+ {
+ char *opt;
+
+ if (nulls[i])
+ continue;
+
+ opt = TextDatumGetCString(options[i]);
+
+ /* Map all strings to the single bitmask */
+ if (strcmp(opt, "pretty") == 0)
+ *flags |= PG_DDL_PRETTY_INDENT;
+ else if (strcmp(opt, "--no-owner") == 0)
+ *flags |= PG_DDL_NO_OWNER;
+ else if (strcmp(opt, "--no-tablespace") == 0)
+ *flags |= PG_DDL_NO_TABLESPACE;
+ else if (strcmp(opt, "--with-defaults") == 0)
+ *flags |= PG_DDL_WITH_DEFAULTS;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unrecognized option: %s", opt)));
+
+ pfree(opt);
+ }
+
+ pfree(options);
+ pfree(nulls);
+}
+
+/*
+ * pg_get_database_ddl
+ *
+ * Generate a CREATE DATABASE statement for the specified database name or oid.
+ *
+ * db_oid - OID/Name of the database for which to generate the DDL.
+ * ddl_options - Array of text options to modify the output.
+ */
+Datum
+pg_get_database_ddl(PG_FUNCTION_ARGS)
+{
+ Oid db_oid = PG_GETARG_OID(0);
+ ArrayType *ddl_options = PG_GETARG_ARRAYTYPE_P(1);
+ char *res;
+
+ res = pg_get_database_ddl_worker(db_oid, ddl_options);
+
+ if (res == NULL)
+ PG_RETURN_NULL();
+
+ PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+static char *
+pg_get_database_ddl_worker(Oid db_oid, ArrayType *ddl_options)
+{
+ char *dbowner = NULL;
+ bool attr_isnull;
+ Datum dbvalue;
+ HeapTuple tuple_database;
+ Form_pg_database dbform;
+ StringInfoData buf;
+ AclResult aclresult;
+
+ /* Variables for ddl_options parsing */
+ int pretty_flags = 0;
+ int ddl_flags = 0;
+ bool is_with_defaults = false;
+
+ /* Call DDL options parser */
+ parse_ddl_options(ddl_options, &ddl_flags);
+
+ /* 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) ? true : false;
+
+ /*
+ * 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);
+
+ /* Look up the owner in the system catalog */
+ if (OidIsValid(dbform->datdba))
+ dbowner = GetUserNameFromId(dbform->datdba, false);
+
+ /* 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 --no-owner is not specified */
+ if (OidIsValid(dbform->datdba) && !(ddl_flags & PG_DDL_NO_OWNER))
+ {
+ get_formatted_string(&buf, pretty_flags, 8, "OWNER = %s",
+ quote_identifier(dbowner));
+ }
+
+ /* Set the ENCODING in the DDL */
+ if (dbform->encoding != 0)
+ {
+ /* If it's with defaults, we skip default encoding check */
+ if (is_with_defaults ||
+ (pg_strcasecmp(pg_encoding_to_char(dbform->encoding),
+ DDL_DEFAULTS.DATABASE.ENCODING) != 0))
+ {
+ get_formatted_string(&buf, pretty_flags, 8, "ENCODING = %s",
+ quote_literal_cstr(
+ pg_encoding_to_char(dbform->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 --no-tablespace is not specified */
+ 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 (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 2ac69bf2df5..181f3e76877 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4030,6 +4030,12 @@
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', proisstrict => 'f', prorettype => 'text',
+ proargtypes => 'regdatabase _text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{regdatabase,_text}',
+ 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..312fdc1376d
--- /dev/null
+++ b/src/include/utils/ddl_defaults.h
@@ -0,0 +1,37 @@
+/*-------------------------------------------------------------------------
+ *
+ * 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 *ENCODING;
+ const char *TABLESPACE;
+ int CONN_LIMIT;
+ bool ALLOW_CONN;
+ } DATABASE;
+
+ /* Add more object types as needed */
+} DDL_DEFAULTS = {
+
+ .DATABASE = {
+ .ENCODING = "UTF8",
+ .TABLESPACE = "pg_default",
+ .CONN_LIMIT = -1,
+ .ALLOW_CONN = true,
+ }
+};
+
+#endif /* DDL_DEFAULTS_H */
\ No newline at end of file
diff --git a/src/test/regress/expected/database.out b/src/test/regress/expected/database.out
index 6b879b0f62a..eb3a13bcf9e 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -1,3 +1,57 @@
+--
+-- 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'
+ );
+
+ 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 +70,86 @@ 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);
+ERROR: database with oid 0 does not exist
+-- Without pretty
+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', '--no-owner'));
+ ddl_filter
+--------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-tablespace'));
+ ddl_filter
+-------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--with-defaults'));
+ ddl_filter
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ENCODING = 'UTF8' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner', '--no-tablespace', '--with-defaults'));
+ ddl_filter
+---------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH ENCODING = 'UTF8' ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty'));
+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', '--no-owner', '--no-tablespace'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--with-defaults'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ OWNER = regress_datdba_after
+ ENCODING = 'UTF8'
+ TABLESPACE = pg_default
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace', '--with-defaults'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ ENCODING = 'UTF8'
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123;
+(1 row)
DROP DATABASE regression_utf8;
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..918b28c47da 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -1,3 +1,59 @@
+--
+-- 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'
+ );
+
+ 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 +75,41 @@ 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 pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+
+-- With No Owner
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner'));
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-tablespace'));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--with-defaults'));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner', '--no-tablespace', '--with-defaults'));
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty'));
+
+-- With No Owner and No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace'));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--with-defaults'));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace', '--with-defaults'));
+
DROP DATABASE regression_utf8;
DROP ROLE regress_datdba_before;
DROP ROLE regress_datdba_after;
--
2.51.0
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
2026-01-07 23:27 Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Andrew Dunstan <[email protected]>
2026-01-14 15:48 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
@ 2026-01-16 07:23 ` Akshay Joshi <[email protected]>
2026-01-20 11:36 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Akshay Joshi @ 2026-01-16 07:23 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: Euler Taveira <[email protected]>; Álvaro Herrera <[email protected]>; Chao Li <[email protected]>; japin <[email protected]>; Quan Zongliang <[email protected]>; pgsql-hackers
-
1) Implement *uint64* for the flags to provide capacity for up to 64
distinct options.
2) Refactor parse_ddl_options to return the flag set directly rather than
using an *out parameter*.
Please find the attached *v7* patch, which is now ready for review.
On Wed, Jan 14, 2026 at 9:18 PM Akshay Joshi <[email protected]>
wrote:
> I have incorporated Euler’s changes, which modify the declaration and
> definition of the *pg_get_database_ddl* function. Please find the
> attached v6 patch, which is now ready for review. The following updates
> have been made:
>
> 1.
>
> Function signature updated to: pg_get_database_ddl(database_id
> regdatabase, VARIADIC ddl_options text[])
> 2.
>
> Added options *--no-owner* and *--no-tablespace* to omit the OWNER and
> TABLESPACE clauses from the reconstructed DDL.
> 3.
>
> Moved the "*pretty*" parameter into ddl_options for formatted output.
> 4.
>
> Properties such as ENCODING, LOCALE_PROVIDER, TABLESPACE, CONNECTION
> LIMIT, and ALLOW_CONNECTIONS are omitted if they are set to default values.
> 5.
>
> Introduced the *--with-defaults* option to include clauses for
> parameters even when they are at their default values.
> 6.
>
> Standardized formatting to use spaces instead of tabs.
>
> *Usage examples:*
>
> 1. SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
> 2. SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
> 3. SELECT pg_get_database_ddl('postgres', 'pretty'); //
> pretty-formatted DDL
> 4. SELECT pg_get_database_ddl('postgres', '--no-owner',
> '--no-tablespace'); // Omits the Owner and Tablespace clause from the DDL.
> 5. SELECT pg_get_database_ddl('postgres', 'pretty',
> '--with-defaults'); // Includes clauses for parameters that are currently
> at their default values.
>
>
> On Thu, Jan 8, 2026 at 4:57 AM Andrew Dunstan <[email protected]> wrote:
>
>>
>> On 2025-12-12 Fr 10:19 AM, Euler Taveira wrote:
>>
>> On Fri, Dec 12, 2025, at 7:52 AM, Akshay Joshi wrote:
>>
>> On Thu, Dec 11, 2025 at 7:29 PM Euler Taveira <[email protected]> <[email protected]> wrote:
>>
>> Is there any way to obtain the default values directly from the source
>> code itself, or do I need to refer to the documentation? If we rely on
>> the documentation and compare against that, then in the future, if the
>> default values change, we would also need to update our logic
>> accordingly.
>>
>>
>> No, you need to check the documentation. If you are changing the default value,
>> you are breaking compatibility; that rarely happens. If we are really concern
>> about this fact, you can add a test case that creates the object without
>> properties (all default values) and another with all default properties and
>> then compare the output.
>>
>>
>> Maybe the function should have a VERBOSE option that emits all the
>> defaults.
>>
>>
>> Constantly having to check the documentation for default values may
>> feel annoying to some users. Some users run queries with parameters
>> such as encoding, connection limit, and locale using their default
>> values. When they call the pg_get_database_ddl function, it
>> reconstructs the short command based on those defaults.
>>
>>
>> Encoding and locale, ok but I doubt about connection limit.
>>
>> postgres=# SELECT current_user;
>> current_user
>> --------------
>> euler
>> (1 row)
>>
>> postgres=# CREATE DATABASE foo;
>> CREATE DATABASE
>> postgres=# CREATE DATABASE bar OWNER euler;
>> CREATE DATABASE
>>
>> When you are learning a new command, you generally don't set the default value
>> for a property just to be correct. I'm not saying this function shouldn't
>> include OWNER. I'm just suggesting it to be optional. See some arguments
>> below.
>>
>>
>> * OWNER. There is no guarantee that the owner exists in the cluster you will
>> use this output. That's something that pg_dumpall treats separately (see
>> above). Does it mean we should include the owner? No. We can make it an
>> option.
>>
>>
>> If I understand correctly, the owner should be an option provided by
>> the caller of the function, and we reconstruct the Database DDL using
>> that specified owner. Is that right?
>> If so, then in my humble opinion, this is not truly a reconstruction
>> of the existing database object.
>>
>>
>> No. My idea is to have something like the pg_dump --no-owner option. This is
>> important if you are transporting the objects from one cluster to another one.
>> Owner might be different. That's why I'm suggesting it should be optional. It
>> means flexibility. See pg_dump output format that always apply the OWNER as a
>> separate ALTER command.
>>
>>
>> +1
>>
>>
>> * options. Since I mentioned options for some properties (owner, strategy,
>> template), these properties can be accommodated as a VARIADIC argument. The
>> function signature can be something like
>>
>> pg_get_database_ddl(oid, VARIADIC options text[])
>>
>> I would include the pretty print into options too.
>>
>>
>> Same comment as the one I gave for the Owner, if you are referring to
>> these as options to the function.
>>
>>
>> Let me elaborate a bit. As I suggested you can control the output with options.
>> Why? Flexibility.
>>
>> Why am I suggesting such a general purpose implementation? See some of the use
>> cases.
>>
>> 1. object DDL. Check DDL to recreate the object. It is not the exact DDL that
>> the user informed but it produces the same result.
>> 2. clone tool. Clone the objects to recreate the environment for another
>> customer. These objects can be created in the same cluster or in another one.
>> (Of course, global objects don't apply for the same cluster.)
>> 3. dump tool. Dump the commands to recreate the existing objects.
>> 4. diff tool. There are tools like pgquarrel [1] that queries the catalog and
>> compare the results to create commands to turn the target database into the
>> source database. The general purpose functions can be used if the object
>> doesn't exist in the target database. (Of course, it doesn't apply for global
>> objects but again it is a good UI to have all of these pg_get_OBJECT_ddl
>> functions using the same approach.)
>> 5. logical replication. These pg_get_OBJECT_ddl functions can be good
>> candidates to be used in the initial schema replication and even in the DDL
>> replication (if the object doesn't exist in the target database).
>>
>> The "options" parameter is to get the DDL command to serve any of these use
>> cases. There are some properties in a certain object that you *don't* want for
>> whatever reason. See some --no-OBJECT options in pg_dump. Let's say you don't
>> want the TABLESPACE or the table access method while getting the CREATE TABLE
>> DDL because it is different in the other database.
>>
>>
>> +1
>>
>>
>> I received a review comment suggesting the use of tabs. I also looked
>> up PostgreSQL best practices on google, which recommend using tabs for
>> indentation and spaces for alignment. I’m open to updating my code
>> accordingly.
>>
>>
>> I didn't check all of the possible output but the majority uses space instead
>> of tabs. Check psql. If you check the git history (git log --grep=tabs), you
>> will notice that tabs are removed from source code.
>>
>>
>>
>> We should follow the pretty printing style in ruleutils.c, which uses
>> spaces.
>>
>>
>> * permission. I don't think you need to check for permissions inside the
>> function. I wouldn't want a different behavior than pg_dump(all). You can
>> always adjust it in system_functions.sql.
>>
>>
>> We’ve already had extensive discussions on this topic in the same
>> email thread, and ultimately we decided to add the permission check.
>>
>>
>> That's fair. Again, I expect that all of these pg_get_OBJECT_ddl functions use
>> the same approach. We can always relax this restriction in the future.
>>
>>
>>
>> +1
>>
>>
>> cheers
>>
>>
>> andrew
>>
>> --
>> Andrew Dunstan
>> EDB: https://www.enterprisedb.com
>>
>>
Attachments:
[application/octet-stream] v7-0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch (26.4K, 3-v7-0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From ece82b36f2cd7cd9b0364a08a07d60f6a9bd6d49 Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Wed, 24 Sep 2025 17:47:59 +0530
Subject: [PATCH v7] 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 database oid.
Supported ddl_options are 'pretty', '--no-owner', '--no-tablespace' and '--with-defaults'.
Usage:
SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
SELECT pg_get_database_ddl('postgres', 'pretty'); // pretty-formatted DDL
SELECT pg_get_database_ddl('postgres', '--no-owner', '--no-tablespace'); // Omits the Owner and Tablespace clause from the DDL.
SELECT pg_get_database_ddl('postgres', 'pretty', '--with-defaults'); // Includes clauses for parameters that are currently at their default values.
Reference: PG-150
Author: Akshay Joshi <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Reviewed-by: Quan Zongliang <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 77 ++++++
src/backend/catalog/system_functions.sql | 6 +
src/backend/utils/adt/ruleutils.c | 291 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +
src/include/utils/ddl_defaults.h | 37 +++
src/test/regress/expected/database.out | 134 +++++++++++
src/test/regress/sql/database.sql | 91 +++++++
7 files changed, 642 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 175f18315cd..2250031bb83 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3830,4 +3830,81 @@ 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 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>ddl_options</parameter> <type>text[]</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE DATABASE</command> statement from the
+ system catalogs for a specified database. The first argument is the OID or
+ name of the database. The optional variadic argument is an array of text
+ flags to control the output. Supported options include
+ <literal>pretty</literal>, <literal>--no-owner</literal>,
+ <literal>--no-tablespace</literal>, and <literal>--with-defaults</literal>.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <parameter>ddl_options</parameter> for <function>pg_get_database_ddl</function>
+ provide fine-grained control over the generated SQL:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>pretty</literal>: Adds newlines and indentation for better readability.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>--no-owner</literal>: Omits the <literal>OWNER</literal> clause from
+ the reconstructed statement.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>--no-tablespace</literal>: Omits the <literal>TABLESPACE</literal> clause.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>--with-defaults</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.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ </sect2>
+
</sect1>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index eb9e31ae1bf..16c0d52479a 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -657,6 +657,12 @@ LANGUAGE INTERNAL
STRICT VOLATILE PARALLEL UNSAFE
AS 'pg_replication_origin_session_setup';
+CREATE OR REPLACE FUNCTION
+ pg_get_database_ddl(database_id regdatabase, VARIADIC ddl_options text[] DEFAULT '{}')
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl';
+
--
-- The default permissions for functions mean that anyone can execute them.
-- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 033b625f3fc..c6b59790655 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,22 @@
#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
+
+
/* 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
@@ -546,6 +560,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, ArrayType *ddl_options);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -13743,3 +13762,275 @@ 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 */
+ for (int i = 0; i < nSpaces; i++)
+ {
+ appendStringInfoChar(buf, ' ');
+ }
+ }
+ else
+ appendStringInfoChar(buf, ' ');
+
+ va_start(args, fmt);
+ appendStringInfoVA(buf, fmt, args);
+ va_end(args);
+}
+
+/**
+ * parse_ddl_options - Generic helper to parse variadic text options
+ * ddl_options: The ArrayType from PG_GETARG_ARRAYTYPE_P
+ * flags: Bitmask to set options while parsing DDL options.
+ */
+static uint64
+parse_ddl_options(ArrayType *ddl_options)
+{
+ uint64 flags = 0;
+ Datum *options;
+ bool *nulls;
+ int n_options;
+ int i;
+
+ /* If no options provided, return the empty bitmask */
+ if (ddl_options == NULL)
+ return flags;
+
+ deconstruct_array(ddl_options,
+ TEXTOID, -1, false, 'i',
+ &options, &nulls, &n_options);
+
+ for (i = 0; i < n_options; i++)
+ {
+ char *opt;
+
+ if (nulls[i])
+ continue;
+
+ opt = TextDatumGetCString(options[i]);
+
+ /* Map strings to bitmask flags */
+ if (strcmp(opt, "pretty") == 0)
+ flags |= PG_DDL_PRETTY_INDENT;
+ else if (strcmp(opt, "--no-owner") == 0)
+ flags |= PG_DDL_NO_OWNER;
+ else if (strcmp(opt, "--no-tablespace") == 0)
+ flags |= PG_DDL_NO_TABLESPACE;
+ else if (strcmp(opt, "--with-defaults") == 0)
+ flags |= PG_DDL_WITH_DEFAULTS;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unrecognized option: %s", opt)));
+
+ pfree(opt);
+ }
+
+ pfree(options);
+ pfree(nulls);
+
+ return flags;
+}
+
+/*
+ * pg_get_database_ddl
+ *
+ * Generate a CREATE DATABASE statement for the specified database name or oid.
+ *
+ * db_oid - OID/Name of the database for which to generate the DDL.
+ * ddl_options - Array of text options to modify the output.
+ */
+Datum
+pg_get_database_ddl(PG_FUNCTION_ARGS)
+{
+ Oid db_oid = PG_GETARG_OID(0);
+ ArrayType *ddl_options = PG_GETARG_ARRAYTYPE_P(1);
+ char *res;
+
+ res = pg_get_database_ddl_worker(db_oid, ddl_options);
+
+ if (res == NULL)
+ PG_RETURN_NULL();
+
+ PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+static char *
+pg_get_database_ddl_worker(Oid db_oid, ArrayType *ddl_options)
+{
+ char *dbowner = NULL;
+ bool attr_isnull;
+ Datum dbvalue;
+ HeapTuple tuple_database;
+ Form_pg_database dbform;
+ StringInfoData buf;
+ AclResult aclresult;
+
+ /* Variables for ddl_options parsing */
+ int pretty_flags = 0;
+ uint64 ddl_flags = 0;
+ bool is_with_defaults = false;
+
+ /* Call DDL options parser */
+ ddl_flags = parse_ddl_options(ddl_options);
+
+ /* 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) ? true : false;
+
+ /*
+ * 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);
+
+ /* Look up the owner in the system catalog */
+ if (OidIsValid(dbform->datdba))
+ dbowner = GetUserNameFromId(dbform->datdba, false);
+
+ /* 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 --no-owner is not specified */
+ if (OidIsValid(dbform->datdba) && !(ddl_flags & PG_DDL_NO_OWNER))
+ {
+ get_formatted_string(&buf, pretty_flags, 8, "OWNER = %s",
+ quote_identifier(dbowner));
+ }
+
+ /* Set the ENCODING in the DDL */
+ if (dbform->encoding != 0)
+ {
+ /* If it's with defaults, we skip default encoding check */
+ if (is_with_defaults ||
+ (pg_strcasecmp(pg_encoding_to_char(dbform->encoding),
+ DDL_DEFAULTS.DATABASE.ENCODING) != 0))
+ {
+ get_formatted_string(&buf, pretty_flags, 8, "ENCODING = %s",
+ quote_literal_cstr(
+ pg_encoding_to_char(dbform->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 --no-tablespace is not specified */
+ 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 (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 894b6a1b6d6..53d623de58f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4030,6 +4030,12 @@
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', proisstrict => 'f', prorettype => 'text',
+ proargtypes => 'regdatabase _text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{regdatabase,_text}',
+ 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..84ef61b4f3d
--- /dev/null
+++ b/src/include/utils/ddl_defaults.h
@@ -0,0 +1,37 @@
+/*-------------------------------------------------------------------------
+ *
+ * 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 *ENCODING;
+ const char *TABLESPACE;
+ int CONN_LIMIT;
+ bool ALLOW_CONN;
+ } DATABASE;
+
+ /* Add more object types as needed */
+} DDL_DEFAULTS = {
+
+ .DATABASE = {
+ .ENCODING = "UTF8",
+ .TABLESPACE = "pg_default",
+ .CONN_LIMIT = -1,
+ .ALLOW_CONN = true,
+ }
+};
+
+#endif /* DDL_DEFAULTS_H */
\ No newline at end of file
diff --git a/src/test/regress/expected/database.out b/src/test/regress/expected/database.out
index 6b879b0f62a..eb3a13bcf9e 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -1,3 +1,57 @@
+--
+-- 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'
+ );
+
+ 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 +70,86 @@ 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);
+ERROR: database with oid 0 does not exist
+-- Without pretty
+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', '--no-owner'));
+ ddl_filter
+--------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-tablespace'));
+ ddl_filter
+-------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--with-defaults'));
+ ddl_filter
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ENCODING = 'UTF8' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner', '--no-tablespace', '--with-defaults'));
+ ddl_filter
+---------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH ENCODING = 'UTF8' ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty'));
+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', '--no-owner', '--no-tablespace'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--with-defaults'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ OWNER = regress_datdba_after
+ ENCODING = 'UTF8'
+ TABLESPACE = pg_default
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace', '--with-defaults'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ ENCODING = 'UTF8'
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123;
+(1 row)
DROP DATABASE regression_utf8;
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..918b28c47da 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -1,3 +1,59 @@
+--
+-- 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'
+ );
+
+ 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 +75,41 @@ 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 pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+
+-- With No Owner
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner'));
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-tablespace'));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--with-defaults'));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner', '--no-tablespace', '--with-defaults'));
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty'));
+
+-- With No Owner and No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace'));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--with-defaults'));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace', '--with-defaults'));
+
DROP DATABASE regression_utf8;
DROP ROLE regress_datdba_before;
DROP ROLE regress_datdba_after;
--
2.51.0
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
2026-01-07 23:27 Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Andrew Dunstan <[email protected]>
2026-01-14 15:48 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
2026-01-16 07:23 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
@ 2026-01-20 11:36 ` Akshay Joshi <[email protected]>
2026-01-21 09:31 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Akshay Joshi @ 2026-01-20 11:36 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: Euler Taveira <[email protected]>; Álvaro Herrera <[email protected]>; Chao Li <[email protected]>; japin <[email protected]>; Quan Zongliang <[email protected]>; pgsql-hackers
All,
Following Alvaro's suggestion to use DefElem syntax, I have modified the
code to support key-value pairs.
I have attached two different patches :
v7-0001: Uses the double-dash syntax.
v7-0002: Uses the DefElem syntax.
*Usage Example for double-dash approach: *
SELECT pg_get_database_ddl('postgres', '--no-owner', '--no-tablespace'); --
Omit Owner and Tablespace clauses.
SELECT pg_get_database_ddl('postgres', 'pretty', '--with-defaults'); --
Includes clauses for parameters at their default values.
*Usage Example for DefElem approach*: The DefElem implementation supports
various boolean values (no, false, 0) and is case-insensitive.
SELECT pg_get_database_ddl('postgres', 'owner=no', 'tablespace=false'); --
Omits Owner and Tablespace clauses.
SELECT pg_get_database_ddl('postgres', 'pretty', 'defaults=yes'); --
Includes clauses for parameters at their default values.
SELECT pg_get_database_ddl('postgres', 'pretty', 'defaults'); -- Includes
clauses for parameters at their default values.
*Please suggest which approach is preferred. The patches are ready for
review.*
On Fri, Jan 16, 2026 at 12:53 PM Akshay Joshi <[email protected]>
wrote:
>
> -
>
> 1) Implement *uint64* for the flags to provide capacity for up to 64
> distinct options.
> 2) Refactor parse_ddl_options to return the flag set directly rather
> than using an *out parameter*.
>
> Please find the attached *v7* patch, which is now ready for review.
>
>
> On Wed, Jan 14, 2026 at 9:18 PM Akshay Joshi <
> [email protected]> wrote:
>
>> I have incorporated Euler’s changes, which modify the declaration and
>> definition of the *pg_get_database_ddl* function. Please find the
>> attached v6 patch, which is now ready for review. The following updates
>> have been made:
>>
>> 1.
>>
>> Function signature updated to: pg_get_database_ddl(database_id
>> regdatabase, VARIADIC ddl_options text[])
>> 2.
>>
>> Added options *--no-owner* and *--no-tablespace* to omit the OWNER
>> and TABLESPACE clauses from the reconstructed DDL.
>> 3.
>>
>> Moved the "*pretty*" parameter into ddl_options for formatted output.
>> 4.
>>
>> Properties such as ENCODING, LOCALE_PROVIDER, TABLESPACE, CONNECTION
>> LIMIT, and ALLOW_CONNECTIONS are omitted if they are set to default values.
>> 5.
>>
>> Introduced the *--with-defaults* option to include clauses for
>> parameters even when they are at their default values.
>> 6.
>>
>> Standardized formatting to use spaces instead of tabs.
>>
>> *Usage examples:*
>>
>> 1. SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
>> 2. SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
>> 3. SELECT pg_get_database_ddl('postgres', 'pretty'); //
>> pretty-formatted DDL
>> 4. SELECT pg_get_database_ddl('postgres', '--no-owner',
>> '--no-tablespace'); // Omits the Owner and Tablespace clause from the DDL.
>> 5. SELECT pg_get_database_ddl('postgres', 'pretty',
>> '--with-defaults'); // Includes clauses for parameters that are currently
>> at their default values.
>>
>>
>> On Thu, Jan 8, 2026 at 4:57 AM Andrew Dunstan <[email protected]>
>> wrote:
>>
>>>
>>> On 2025-12-12 Fr 10:19 AM, Euler Taveira wrote:
>>>
>>> On Fri, Dec 12, 2025, at 7:52 AM, Akshay Joshi wrote:
>>>
>>> On Thu, Dec 11, 2025 at 7:29 PM Euler Taveira <[email protected]> <[email protected]> wrote:
>>>
>>> Is there any way to obtain the default values directly from the source
>>> code itself, or do I need to refer to the documentation? If we rely on
>>> the documentation and compare against that, then in the future, if the
>>> default values change, we would also need to update our logic
>>> accordingly.
>>>
>>>
>>> No, you need to check the documentation. If you are changing the default value,
>>> you are breaking compatibility; that rarely happens. If we are really concern
>>> about this fact, you can add a test case that creates the object without
>>> properties (all default values) and another with all default properties and
>>> then compare the output.
>>>
>>>
>>> Maybe the function should have a VERBOSE option that emits all the
>>> defaults.
>>>
>>>
>>> Constantly having to check the documentation for default values may
>>> feel annoying to some users. Some users run queries with parameters
>>> such as encoding, connection limit, and locale using their default
>>> values. When they call the pg_get_database_ddl function, it
>>> reconstructs the short command based on those defaults.
>>>
>>>
>>> Encoding and locale, ok but I doubt about connection limit.
>>>
>>> postgres=# SELECT current_user;
>>> current_user
>>> --------------
>>> euler
>>> (1 row)
>>>
>>> postgres=# CREATE DATABASE foo;
>>> CREATE DATABASE
>>> postgres=# CREATE DATABASE bar OWNER euler;
>>> CREATE DATABASE
>>>
>>> When you are learning a new command, you generally don't set the default value
>>> for a property just to be correct. I'm not saying this function shouldn't
>>> include OWNER. I'm just suggesting it to be optional. See some arguments
>>> below.
>>>
>>>
>>> * OWNER. There is no guarantee that the owner exists in the cluster you will
>>> use this output. That's something that pg_dumpall treats separately (see
>>> above). Does it mean we should include the owner? No. We can make it an
>>> option.
>>>
>>>
>>> If I understand correctly, the owner should be an option provided by
>>> the caller of the function, and we reconstruct the Database DDL using
>>> that specified owner. Is that right?
>>> If so, then in my humble opinion, this is not truly a reconstruction
>>> of the existing database object.
>>>
>>>
>>> No. My idea is to have something like the pg_dump --no-owner option. This is
>>> important if you are transporting the objects from one cluster to another one.
>>> Owner might be different. That's why I'm suggesting it should be optional. It
>>> means flexibility. See pg_dump output format that always apply the OWNER as a
>>> separate ALTER command.
>>>
>>>
>>> +1
>>>
>>>
>>> * options. Since I mentioned options for some properties (owner, strategy,
>>> template), these properties can be accommodated as a VARIADIC argument. The
>>> function signature can be something like
>>>
>>> pg_get_database_ddl(oid, VARIADIC options text[])
>>>
>>> I would include the pretty print into options too.
>>>
>>>
>>> Same comment as the one I gave for the Owner, if you are referring to
>>> these as options to the function.
>>>
>>>
>>> Let me elaborate a bit. As I suggested you can control the output with options.
>>> Why? Flexibility.
>>>
>>> Why am I suggesting such a general purpose implementation? See some of the use
>>> cases.
>>>
>>> 1. object DDL. Check DDL to recreate the object. It is not the exact DDL that
>>> the user informed but it produces the same result.
>>> 2. clone tool. Clone the objects to recreate the environment for another
>>> customer. These objects can be created in the same cluster or in another one.
>>> (Of course, global objects don't apply for the same cluster.)
>>> 3. dump tool. Dump the commands to recreate the existing objects.
>>> 4. diff tool. There are tools like pgquarrel [1] that queries the catalog and
>>> compare the results to create commands to turn the target database into the
>>> source database. The general purpose functions can be used if the object
>>> doesn't exist in the target database. (Of course, it doesn't apply for global
>>> objects but again it is a good UI to have all of these pg_get_OBJECT_ddl
>>> functions using the same approach.)
>>> 5. logical replication. These pg_get_OBJECT_ddl functions can be good
>>> candidates to be used in the initial schema replication and even in the DDL
>>> replication (if the object doesn't exist in the target database).
>>>
>>> The "options" parameter is to get the DDL command to serve any of these use
>>> cases. There are some properties in a certain object that you *don't* want for
>>> whatever reason. See some --no-OBJECT options in pg_dump. Let's say you don't
>>> want the TABLESPACE or the table access method while getting the CREATE TABLE
>>> DDL because it is different in the other database.
>>>
>>>
>>> +1
>>>
>>>
>>> I received a review comment suggesting the use of tabs. I also looked
>>> up PostgreSQL best practices on google, which recommend using tabs for
>>> indentation and spaces for alignment. I’m open to updating my code
>>> accordingly.
>>>
>>>
>>> I didn't check all of the possible output but the majority uses space instead
>>> of tabs. Check psql. If you check the git history (git log --grep=tabs), you
>>> will notice that tabs are removed from source code.
>>>
>>>
>>>
>>> We should follow the pretty printing style in ruleutils.c, which uses
>>> spaces.
>>>
>>>
>>> * permission. I don't think you need to check for permissions inside the
>>> function. I wouldn't want a different behavior than pg_dump(all). You can
>>> always adjust it in system_functions.sql.
>>>
>>>
>>> We’ve already had extensive discussions on this topic in the same
>>> email thread, and ultimately we decided to add the permission check.
>>>
>>>
>>> That's fair. Again, I expect that all of these pg_get_OBJECT_ddl functions use
>>> the same approach. We can always relax this restriction in the future.
>>>
>>>
>>>
>>> +1
>>>
>>>
>>> cheers
>>>
>>>
>>> andrew
>>>
>>> --
>>> Andrew Dunstan
>>> EDB: https://www.enterprisedb.com
>>>
>>>
Attachments:
[application/octet-stream] v7-0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch (26.4K, 3-v7-0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From ece82b36f2cd7cd9b0364a08a07d60f6a9bd6d49 Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Wed, 24 Sep 2025 17:47:59 +0530
Subject: [PATCH v7] 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 database oid.
Supported ddl_options are 'pretty', '--no-owner', '--no-tablespace' and '--with-defaults'.
Usage:
SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
SELECT pg_get_database_ddl('postgres', 'pretty'); // pretty-formatted DDL
SELECT pg_get_database_ddl('postgres', '--no-owner', '--no-tablespace'); // Omits the Owner and Tablespace clause from the DDL.
SELECT pg_get_database_ddl('postgres', 'pretty', '--with-defaults'); // Includes clauses for parameters that are currently at their default values.
Reference: PG-150
Author: Akshay Joshi <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Reviewed-by: Quan Zongliang <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 77 ++++++
src/backend/catalog/system_functions.sql | 6 +
src/backend/utils/adt/ruleutils.c | 291 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +
src/include/utils/ddl_defaults.h | 37 +++
src/test/regress/expected/database.out | 134 +++++++++++
src/test/regress/sql/database.sql | 91 +++++++
7 files changed, 642 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 175f18315cd..2250031bb83 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3830,4 +3830,81 @@ 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 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>ddl_options</parameter> <type>text[]</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE DATABASE</command> statement from the
+ system catalogs for a specified database. The first argument is the OID or
+ name of the database. The optional variadic argument is an array of text
+ flags to control the output. Supported options include
+ <literal>pretty</literal>, <literal>--no-owner</literal>,
+ <literal>--no-tablespace</literal>, and <literal>--with-defaults</literal>.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <parameter>ddl_options</parameter> for <function>pg_get_database_ddl</function>
+ provide fine-grained control over the generated SQL:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>pretty</literal>: Adds newlines and indentation for better readability.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>--no-owner</literal>: Omits the <literal>OWNER</literal> clause from
+ the reconstructed statement.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>--no-tablespace</literal>: Omits the <literal>TABLESPACE</literal> clause.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>--with-defaults</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.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ </sect2>
+
</sect1>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index eb9e31ae1bf..16c0d52479a 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -657,6 +657,12 @@ LANGUAGE INTERNAL
STRICT VOLATILE PARALLEL UNSAFE
AS 'pg_replication_origin_session_setup';
+CREATE OR REPLACE FUNCTION
+ pg_get_database_ddl(database_id regdatabase, VARIADIC ddl_options text[] DEFAULT '{}')
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl';
+
--
-- The default permissions for functions mean that anyone can execute them.
-- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 033b625f3fc..c6b59790655 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,22 @@
#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
+
+
/* 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
@@ -546,6 +560,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, ArrayType *ddl_options);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -13743,3 +13762,275 @@ 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 */
+ for (int i = 0; i < nSpaces; i++)
+ {
+ appendStringInfoChar(buf, ' ');
+ }
+ }
+ else
+ appendStringInfoChar(buf, ' ');
+
+ va_start(args, fmt);
+ appendStringInfoVA(buf, fmt, args);
+ va_end(args);
+}
+
+/**
+ * parse_ddl_options - Generic helper to parse variadic text options
+ * ddl_options: The ArrayType from PG_GETARG_ARRAYTYPE_P
+ * flags: Bitmask to set options while parsing DDL options.
+ */
+static uint64
+parse_ddl_options(ArrayType *ddl_options)
+{
+ uint64 flags = 0;
+ Datum *options;
+ bool *nulls;
+ int n_options;
+ int i;
+
+ /* If no options provided, return the empty bitmask */
+ if (ddl_options == NULL)
+ return flags;
+
+ deconstruct_array(ddl_options,
+ TEXTOID, -1, false, 'i',
+ &options, &nulls, &n_options);
+
+ for (i = 0; i < n_options; i++)
+ {
+ char *opt;
+
+ if (nulls[i])
+ continue;
+
+ opt = TextDatumGetCString(options[i]);
+
+ /* Map strings to bitmask flags */
+ if (strcmp(opt, "pretty") == 0)
+ flags |= PG_DDL_PRETTY_INDENT;
+ else if (strcmp(opt, "--no-owner") == 0)
+ flags |= PG_DDL_NO_OWNER;
+ else if (strcmp(opt, "--no-tablespace") == 0)
+ flags |= PG_DDL_NO_TABLESPACE;
+ else if (strcmp(opt, "--with-defaults") == 0)
+ flags |= PG_DDL_WITH_DEFAULTS;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unrecognized option: %s", opt)));
+
+ pfree(opt);
+ }
+
+ pfree(options);
+ pfree(nulls);
+
+ return flags;
+}
+
+/*
+ * pg_get_database_ddl
+ *
+ * Generate a CREATE DATABASE statement for the specified database name or oid.
+ *
+ * db_oid - OID/Name of the database for which to generate the DDL.
+ * ddl_options - Array of text options to modify the output.
+ */
+Datum
+pg_get_database_ddl(PG_FUNCTION_ARGS)
+{
+ Oid db_oid = PG_GETARG_OID(0);
+ ArrayType *ddl_options = PG_GETARG_ARRAYTYPE_P(1);
+ char *res;
+
+ res = pg_get_database_ddl_worker(db_oid, ddl_options);
+
+ if (res == NULL)
+ PG_RETURN_NULL();
+
+ PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+static char *
+pg_get_database_ddl_worker(Oid db_oid, ArrayType *ddl_options)
+{
+ char *dbowner = NULL;
+ bool attr_isnull;
+ Datum dbvalue;
+ HeapTuple tuple_database;
+ Form_pg_database dbform;
+ StringInfoData buf;
+ AclResult aclresult;
+
+ /* Variables for ddl_options parsing */
+ int pretty_flags = 0;
+ uint64 ddl_flags = 0;
+ bool is_with_defaults = false;
+
+ /* Call DDL options parser */
+ ddl_flags = parse_ddl_options(ddl_options);
+
+ /* 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) ? true : false;
+
+ /*
+ * 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);
+
+ /* Look up the owner in the system catalog */
+ if (OidIsValid(dbform->datdba))
+ dbowner = GetUserNameFromId(dbform->datdba, false);
+
+ /* 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 --no-owner is not specified */
+ if (OidIsValid(dbform->datdba) && !(ddl_flags & PG_DDL_NO_OWNER))
+ {
+ get_formatted_string(&buf, pretty_flags, 8, "OWNER = %s",
+ quote_identifier(dbowner));
+ }
+
+ /* Set the ENCODING in the DDL */
+ if (dbform->encoding != 0)
+ {
+ /* If it's with defaults, we skip default encoding check */
+ if (is_with_defaults ||
+ (pg_strcasecmp(pg_encoding_to_char(dbform->encoding),
+ DDL_DEFAULTS.DATABASE.ENCODING) != 0))
+ {
+ get_formatted_string(&buf, pretty_flags, 8, "ENCODING = %s",
+ quote_literal_cstr(
+ pg_encoding_to_char(dbform->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 --no-tablespace is not specified */
+ 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 (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 894b6a1b6d6..53d623de58f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4030,6 +4030,12 @@
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', proisstrict => 'f', prorettype => 'text',
+ proargtypes => 'regdatabase _text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{regdatabase,_text}',
+ 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..84ef61b4f3d
--- /dev/null
+++ b/src/include/utils/ddl_defaults.h
@@ -0,0 +1,37 @@
+/*-------------------------------------------------------------------------
+ *
+ * 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 *ENCODING;
+ const char *TABLESPACE;
+ int CONN_LIMIT;
+ bool ALLOW_CONN;
+ } DATABASE;
+
+ /* Add more object types as needed */
+} DDL_DEFAULTS = {
+
+ .DATABASE = {
+ .ENCODING = "UTF8",
+ .TABLESPACE = "pg_default",
+ .CONN_LIMIT = -1,
+ .ALLOW_CONN = true,
+ }
+};
+
+#endif /* DDL_DEFAULTS_H */
\ No newline at end of file
diff --git a/src/test/regress/expected/database.out b/src/test/regress/expected/database.out
index 6b879b0f62a..eb3a13bcf9e 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -1,3 +1,57 @@
+--
+-- 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'
+ );
+
+ 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 +70,86 @@ 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);
+ERROR: database with oid 0 does not exist
+-- Without pretty
+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', '--no-owner'));
+ ddl_filter
+--------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-tablespace'));
+ ddl_filter
+-------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--with-defaults'));
+ ddl_filter
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ENCODING = 'UTF8' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner', '--no-tablespace', '--with-defaults'));
+ ddl_filter
+---------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH ENCODING = 'UTF8' ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty'));
+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', '--no-owner', '--no-tablespace'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--with-defaults'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ OWNER = regress_datdba_after
+ ENCODING = 'UTF8'
+ TABLESPACE = pg_default
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace', '--with-defaults'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ ENCODING = 'UTF8'
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123;
+(1 row)
DROP DATABASE regression_utf8;
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..918b28c47da 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -1,3 +1,59 @@
+--
+-- 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'
+ );
+
+ 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 +75,41 @@ 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 pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+
+-- With No Owner
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner'));
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-tablespace'));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--with-defaults'));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner', '--no-tablespace', '--with-defaults'));
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty'));
+
+-- With No Owner and No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace'));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--with-defaults'));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace', '--with-defaults'));
+
DROP DATABASE regression_utf8;
DROP ROLE regress_datdba_before;
DROP ROLE regress_datdba_after;
--
2.51.0
[application/octet-stream] v7-0002-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch (27.8K, 4-v7-0002-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From bcd2bde823cd161a14187dee2a013f66c0616b0a Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Tue, 20 Jan 2026 16:40:30 +0530
Subject: [PATCH v8] 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 database oid.
Supported ddl_options are 'pretty', 'owner=no/false/0', 'tablespace=no/false/0' and 'defaults=yes'.
Usage:
SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
SELECT pg_get_database_ddl('postgres', 'pretty'); // pretty-formatted DDL
SELECT pg_get_database_ddl('postgres', 'owner=no', 'tablespace=no'); // Omits the Owner and Tablespace clause from the DDL.
SELECT pg_get_database_ddl('postgres', 'pretty', 'defaults'); // Includes clauses for parameters that are currently at their default values.
Reference: PG-150
Author: Akshay Joshi <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Reviewed-by: Quan Zongliang <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 78 +++++
src/backend/catalog/system_functions.sql | 6 +
src/backend/utils/adt/ruleutils.c | 365 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +
src/include/utils/ddl_defaults.h | 39 +++
src/test/regress/expected/database.out | 134 +++++++++
src/test/regress/sql/database.sql | 91 ++++++
7 files changed, 719 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 175f18315cd..d337d1b3fb6 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3830,4 +3830,82 @@ 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 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>ddl_options</parameter> <type>text[]</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE DATABASE</command> statement from the
+ system catalogs for a specified database. The first argument is the OID or
+ name of the database. The optional variadic argument is an array of text
+ flags to control the output. Supported options include
+ <literal>pretty</literal>, <literal>owner=no</literal>,
+ <literal>tablespace=no</literal>, and <literal>defaults=yes</literal>.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <parameter>ddl_options</parameter> for <function>pg_get_database_ddl</function>
+ provide fine-grained control over the generated SQL:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>pretty</literal>: Adds newlines and indentation for better readability.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>owner=no</literal>: Omits the <literal>OWNER</literal> clause from
+ the reconstructed statement.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>tablespace=no</literal>: Omits the <literal>TABLESPACE</literal>
+ clause from the reconstructed statement.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>defaults=yes</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.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ </sect2>
+
</sect1>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index eb9e31ae1bf..16c0d52479a 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -657,6 +657,12 @@ LANGUAGE INTERNAL
STRICT VOLATILE PARALLEL UNSAFE
AS 'pg_replication_origin_session_setup';
+CREATE OR REPLACE FUNCTION
+ pg_get_database_ddl(database_id regdatabase, VARIADIC ddl_options text[] DEFAULT '{}')
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl';
+
--
-- The default permissions for functions mean that anyone can execute them.
-- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 033b625f3fc..b2773d1f3b2 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,22 @@
#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
+
+
/* 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
@@ -546,6 +560,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, ArrayType *ddl_options);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -13743,3 +13762,349 @@ 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 */
+ for (int i = 0; i < nSpaces; i++)
+ {
+ appendStringInfoChar(buf, ' ');
+ }
+ }
+ else
+ appendStringInfoChar(buf, ' ');
+
+ va_start(args, fmt);
+ appendStringInfoVA(buf, fmt, args);
+ va_end(args);
+}
+
+/**
+ * parse_ddl_options - Generic helper to parse variadic text options
+ * ddl_options: The ArrayType from PG_GETARG_ARRAYTYPE_P
+ * flags: Bitmask to set options while parsing DDL options.
+ */
+static uint64
+parse_ddl_options(ArrayType *ddl_options)
+{
+ uint64 flags = 0;
+ Datum *options;
+ bool *nulls;
+ int n_options;
+ int i;
+
+ if (ddl_options == NULL)
+ return flags;
+
+ deconstruct_array(ddl_options, TEXTOID, -1, false, 'i',
+ &options, &nulls, &n_options);
+
+ for (i = 0; i < n_options; i++)
+ {
+ char *opt;
+ char *name;
+ char *value;
+
+ if (nulls[i])
+ continue;
+
+ opt = TextDatumGetCString(options[i]);
+ name = opt;
+ value = strchr(opt, '=');
+
+ if (value != NULL)
+ {
+ *value = '\0';
+ value++;
+ }
+
+ /*
+ * * Logic for "owner": handle 'owner=no', 'owner=0', 'owner=false',
+ * etc. Using pg_strcasecmp for the key and parse_bool for the value.
+ */
+ if (pg_strcasecmp(name, "owner") == 0)
+ {
+ bool bval;
+
+ if (value == NULL)
+ continue;
+
+ if (parse_bool(value, &bval))
+ {
+ if (!bval)
+ flags |= PG_DDL_NO_OWNER;
+ }
+ else
+ goto invalid_value;
+ }
+ /* Logic for "tablespace" */
+ else if (pg_strcasecmp(name, "tablespace") == 0)
+ {
+ bool bval;
+
+ if (value == NULL)
+ continue;
+
+ if (parse_bool(value, &bval))
+ {
+ if (!bval)
+ flags |= PG_DDL_NO_TABLESPACE;
+ }
+ else
+ goto invalid_value;
+ }
+ /* Logic for "defaults" */
+ else if (pg_strcasecmp(name, "defaults") == 0)
+ {
+ bool bval;
+
+ /* If just 'defaults' is passed without '=val', we assume true */
+ if (value == NULL)
+ flags |= PG_DDL_WITH_DEFAULTS;
+ else if (parse_bool(value, &bval))
+ {
+ if (bval)
+ flags |= PG_DDL_WITH_DEFAULTS;
+ }
+ else
+ goto invalid_value;
+ }
+ /* Logic for "pretty" */
+ else if (pg_strcasecmp(name, "pretty") == 0)
+ {
+ /* Usually a standalone flag, but we check boolean if provided */
+ bool bval;
+
+ if (value == NULL)
+ flags |= PG_DDL_PRETTY_INDENT;
+ else if (parse_bool(value, &bval))
+ {
+ if (bval)
+ flags |= PG_DDL_PRETTY_INDENT;
+ }
+ else
+ goto invalid_value;
+ }
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unrecognized option: %s", name)));
+ }
+
+ pfree(opt);
+ continue;
+
+invalid_value:
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid value for option \"%s\": %s", name, value)));
+ }
+
+ pfree(options);
+ pfree(nulls);
+
+ return flags;
+}
+
+/*
+ * pg_get_database_ddl
+ *
+ * Generate a CREATE DATABASE statement for the specified database name or oid.
+ *
+ * db_oid - OID/Name of the database for which to generate the DDL.
+ * ddl_options - Array of text options to modify the output.
+ */
+Datum
+pg_get_database_ddl(PG_FUNCTION_ARGS)
+{
+ Oid db_oid = PG_GETARG_OID(0);
+ ArrayType *ddl_options = PG_GETARG_ARRAYTYPE_P(1);
+ char *res;
+
+ res = pg_get_database_ddl_worker(db_oid, ddl_options);
+
+ if (res == NULL)
+ PG_RETURN_NULL();
+
+ PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+static char *
+pg_get_database_ddl_worker(Oid db_oid, ArrayType *ddl_options)
+{
+ char *dbowner = NULL;
+ bool attr_isnull;
+ Datum dbvalue;
+ HeapTuple tuple_database;
+ Form_pg_database dbform;
+ StringInfoData buf;
+ AclResult aclresult;
+
+ /* Variables for ddl_options parsing */
+ int pretty_flags = 0;
+ uint64 ddl_flags = 0;
+ bool is_with_defaults = false;
+
+ /* Call DDL options parser */
+ ddl_flags = parse_ddl_options(ddl_options);
+
+ /* 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) ? true : false;
+
+ /*
+ * 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);
+
+ /* Look up the owner in the system catalog */
+ if (OidIsValid(dbform->datdba))
+ dbowner = GetUserNameFromId(dbform->datdba, false);
+
+ /* 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 --no-owner is not specified */
+ if (OidIsValid(dbform->datdba) && !(ddl_flags & PG_DDL_NO_OWNER))
+ {
+ get_formatted_string(&buf, pretty_flags, 8, "OWNER = %s",
+ quote_identifier(dbowner));
+ }
+
+ /* Set the ENCODING in the DDL */
+ if (dbform->encoding != 0)
+ {
+ /* If it's with defaults, we skip default encoding check */
+ if (is_with_defaults ||
+ (pg_strcasecmp(pg_encoding_to_char(dbform->encoding),
+ DDL_DEFAULTS.DATABASE.ENCODING) != 0))
+ {
+ get_formatted_string(&buf, pretty_flags, 8, "ENCODING = %s",
+ quote_literal_cstr(
+ pg_encoding_to_char(dbform->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 --no-tablespace is not specified */
+ 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 (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 894b6a1b6d6..53d623de58f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4030,6 +4030,12 @@
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', proisstrict => 'f', prorettype => 'text',
+ proargtypes => 'regdatabase _text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{regdatabase,_text}',
+ 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..d17e843fe09
--- /dev/null
+++ b/src/include/utils/ddl_defaults.h
@@ -0,0 +1,39 @@
+/*-------------------------------------------------------------------------
+ *
+ * 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
+
+#include <stdbool.h>
+
+static const struct
+{
+ struct
+ {
+ const char *ENCODING;
+ const char *TABLESPACE;
+ int CONN_LIMIT;
+ bool ALLOW_CONN;
+ } DATABASE;
+
+ /* Add more object types as needed */
+} DDL_DEFAULTS = {
+
+ .DATABASE = {
+ .ENCODING = "UTF8",
+ .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..a816b0b525f 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -1,3 +1,57 @@
+--
+-- 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'
+ );
+
+ 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 +70,86 @@ 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);
+ERROR: database with oid 0 does not exist
+-- Without pretty
+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=no'));
+ 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', 'tablespace=no'));
+ ddl_filter
+--------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ENCODING = 'UTF8' ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults=yes'));
+ ddl_filter
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ENCODING = 'UTF8' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner=0', 'tablespace=0', 'defaults=1'));
+ ddl_filter
+---------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH ENCODING = 'UTF8' ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty'));
+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', 'owner=no', 'tablespace=no'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', 'defaults'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ OWNER = regress_datdba_after
+ ENCODING = 'UTF8'
+ TABLESPACE = pg_default
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', 'owner=false', 'tablespace=false', 'defaults=true'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ ENCODING = 'UTF8'
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123;
+(1 row)
DROP DATABASE regression_utf8;
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..4cb17a8d0d6 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -1,3 +1,59 @@
+--
+-- 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'
+ );
+
+ 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 +75,41 @@ 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 pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+
+-- With No Owner
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner=no'));
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults', 'tablespace=no'));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults=yes'));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner=0', 'tablespace=0', 'defaults=1'));
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty'));
+
+-- With No Owner and No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', 'owner=no', 'tablespace=no'));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', 'defaults'));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', 'owner=false', 'tablespace=false', 'defaults=true'));
+
DROP DATABASE regression_utf8;
DROP ROLE regress_datdba_before;
DROP ROLE regress_datdba_after;
--
2.51.0
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
2026-01-07 23:27 Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Andrew Dunstan <[email protected]>
2026-01-14 15:48 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
2026-01-16 07:23 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
2026-01-20 11:36 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
@ 2026-01-21 09:31 ` Akshay Joshi <[email protected]>
2026-01-31 00:27 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Mark Wong <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Akshay Joshi @ 2026-01-21 09:31 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: Euler Taveira <[email protected]>; Álvaro Herrera <[email protected]>; Chao Li <[email protected]>; japin <[email protected]>; Quan Zongliang <[email protected]>; pgsql-hackers
In my previous email, I included two different patches (for two separate
approaches) from different branches. As a result, CommitFest is indicating
that a rebase is required.
Apologies for the inconvenience, I’m still getting familiar with the
process.
Attached are the patches, layered one on top of the other, representing two
approaches:
- *Double Dash*:
v8-0001-Add-pg_get_database_ddl-function-to-reconstruct-double-dash.patch
- *DefElem (Key-Value)*:
v8-0002-Add-pg_get_database_ddl-function-to-reconstruct-DefElem.patch
I am now submitting the *v8 patches*, which are ready for review. Please
let me know which approach you find more suitable and preferable.
On Tue, Jan 20, 2026 at 5:06 PM Akshay Joshi <[email protected]>
wrote:
> All,
>
> Following Alvaro's suggestion to use DefElem syntax, I have modified the
> code to support key-value pairs.
>
> I have attached two different patches :
> v7-0001: Uses the double-dash syntax.
> v7-0002: Uses the DefElem syntax.
>
> *Usage Example for double-dash approach: *
> SELECT pg_get_database_ddl('postgres', '--no-owner', '--no-tablespace');
> -- Omit Owner and Tablespace clauses.
> SELECT pg_get_database_ddl('postgres', 'pretty', '--with-defaults'); --
> Includes clauses for parameters at their default values.
>
> *Usage Example for DefElem approach*: The DefElem implementation supports
> various boolean values (no, false, 0) and is case-insensitive.
> SELECT pg_get_database_ddl('postgres', 'owner=no', 'tablespace=false'); --
> Omits Owner and Tablespace clauses.
> SELECT pg_get_database_ddl('postgres', 'pretty', 'defaults=yes'); --
> Includes clauses for parameters at their default values.
> SELECT pg_get_database_ddl('postgres', 'pretty', 'defaults'); -- Includes
> clauses for parameters at their default values.
>
>
> *Please suggest which approach is preferred. The patches are ready for
> review.*
>
>
> On Fri, Jan 16, 2026 at 12:53 PM Akshay Joshi <
> [email protected]> wrote:
>
>>
>> -
>>
>> 1) Implement *uint64* for the flags to provide capacity for up to 64
>> distinct options.
>> 2) Refactor parse_ddl_options to return the flag set directly rather
>> than using an *out parameter*.
>>
>> Please find the attached *v7* patch, which is now ready for review.
>>
>>
>> On Wed, Jan 14, 2026 at 9:18 PM Akshay Joshi <
>> [email protected]> wrote:
>>
>>> I have incorporated Euler’s changes, which modify the declaration and
>>> definition of the *pg_get_database_ddl* function. Please find the
>>> attached v6 patch, which is now ready for review. The following updates
>>> have been made:
>>>
>>> 1.
>>>
>>> Function signature updated to: pg_get_database_ddl(database_id
>>> regdatabase, VARIADIC ddl_options text[])
>>> 2.
>>>
>>> Added options *--no-owner* and *--no-tablespace* to omit the OWNER
>>> and TABLESPACE clauses from the reconstructed DDL.
>>> 3.
>>>
>>> Moved the "*pretty*" parameter into ddl_options for formatted output.
>>> 4.
>>>
>>> Properties such as ENCODING, LOCALE_PROVIDER, TABLESPACE, CONNECTION
>>> LIMIT, and ALLOW_CONNECTIONS are omitted if they are set to default values.
>>> 5.
>>>
>>> Introduced the *--with-defaults* option to include clauses for
>>> parameters even when they are at their default values.
>>> 6.
>>>
>>> Standardized formatting to use spaces instead of tabs.
>>>
>>> *Usage examples:*
>>>
>>> 1. SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted
>>> DDL
>>> 2. SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
>>> 3. SELECT pg_get_database_ddl('postgres', 'pretty'); //
>>> pretty-formatted DDL
>>> 4. SELECT pg_get_database_ddl('postgres', '--no-owner',
>>> '--no-tablespace'); // Omits the Owner and Tablespace clause from the DDL.
>>> 5. SELECT pg_get_database_ddl('postgres', 'pretty',
>>> '--with-defaults'); // Includes clauses for parameters that are currently
>>> at their default values.
>>>
>>>
>>> On Thu, Jan 8, 2026 at 4:57 AM Andrew Dunstan <[email protected]>
>>> wrote:
>>>
>>>>
>>>> On 2025-12-12 Fr 10:19 AM, Euler Taveira wrote:
>>>>
>>>> On Fri, Dec 12, 2025, at 7:52 AM, Akshay Joshi wrote:
>>>>
>>>> On Thu, Dec 11, 2025 at 7:29 PM Euler Taveira <[email protected]> <[email protected]> wrote:
>>>>
>>>> Is there any way to obtain the default values directly from the source
>>>> code itself, or do I need to refer to the documentation? If we rely on
>>>> the documentation and compare against that, then in the future, if the
>>>> default values change, we would also need to update our logic
>>>> accordingly.
>>>>
>>>>
>>>> No, you need to check the documentation. If you are changing the default value,
>>>> you are breaking compatibility; that rarely happens. If we are really concern
>>>> about this fact, you can add a test case that creates the object without
>>>> properties (all default values) and another with all default properties and
>>>> then compare the output.
>>>>
>>>>
>>>> Maybe the function should have a VERBOSE option that emits all the
>>>> defaults.
>>>>
>>>>
>>>> Constantly having to check the documentation for default values may
>>>> feel annoying to some users. Some users run queries with parameters
>>>> such as encoding, connection limit, and locale using their default
>>>> values. When they call the pg_get_database_ddl function, it
>>>> reconstructs the short command based on those defaults.
>>>>
>>>>
>>>> Encoding and locale, ok but I doubt about connection limit.
>>>>
>>>> postgres=# SELECT current_user;
>>>> current_user
>>>> --------------
>>>> euler
>>>> (1 row)
>>>>
>>>> postgres=# CREATE DATABASE foo;
>>>> CREATE DATABASE
>>>> postgres=# CREATE DATABASE bar OWNER euler;
>>>> CREATE DATABASE
>>>>
>>>> When you are learning a new command, you generally don't set the default value
>>>> for a property just to be correct. I'm not saying this function shouldn't
>>>> include OWNER. I'm just suggesting it to be optional. See some arguments
>>>> below.
>>>>
>>>>
>>>> * OWNER. There is no guarantee that the owner exists in the cluster you will
>>>> use this output. That's something that pg_dumpall treats separately (see
>>>> above). Does it mean we should include the owner? No. We can make it an
>>>> option.
>>>>
>>>>
>>>> If I understand correctly, the owner should be an option provided by
>>>> the caller of the function, and we reconstruct the Database DDL using
>>>> that specified owner. Is that right?
>>>> If so, then in my humble opinion, this is not truly a reconstruction
>>>> of the existing database object.
>>>>
>>>>
>>>> No. My idea is to have something like the pg_dump --no-owner option. This is
>>>> important if you are transporting the objects from one cluster to another one.
>>>> Owner might be different. That's why I'm suggesting it should be optional. It
>>>> means flexibility. See pg_dump output format that always apply the OWNER as a
>>>> separate ALTER command.
>>>>
>>>>
>>>> +1
>>>>
>>>>
>>>> * options. Since I mentioned options for some properties (owner, strategy,
>>>> template), these properties can be accommodated as a VARIADIC argument. The
>>>> function signature can be something like
>>>>
>>>> pg_get_database_ddl(oid, VARIADIC options text[])
>>>>
>>>> I would include the pretty print into options too.
>>>>
>>>>
>>>> Same comment as the one I gave for the Owner, if you are referring to
>>>> these as options to the function.
>>>>
>>>>
>>>> Let me elaborate a bit. As I suggested you can control the output with options.
>>>> Why? Flexibility.
>>>>
>>>> Why am I suggesting such a general purpose implementation? See some of the use
>>>> cases.
>>>>
>>>> 1. object DDL. Check DDL to recreate the object. It is not the exact DDL that
>>>> the user informed but it produces the same result.
>>>> 2. clone tool. Clone the objects to recreate the environment for another
>>>> customer. These objects can be created in the same cluster or in another one.
>>>> (Of course, global objects don't apply for the same cluster.)
>>>> 3. dump tool. Dump the commands to recreate the existing objects.
>>>> 4. diff tool. There are tools like pgquarrel [1] that queries the catalog and
>>>> compare the results to create commands to turn the target database into the
>>>> source database. The general purpose functions can be used if the object
>>>> doesn't exist in the target database. (Of course, it doesn't apply for global
>>>> objects but again it is a good UI to have all of these pg_get_OBJECT_ddl
>>>> functions using the same approach.)
>>>> 5. logical replication. These pg_get_OBJECT_ddl functions can be good
>>>> candidates to be used in the initial schema replication and even in the DDL
>>>> replication (if the object doesn't exist in the target database).
>>>>
>>>> The "options" parameter is to get the DDL command to serve any of these use
>>>> cases. There are some properties in a certain object that you *don't* want for
>>>> whatever reason. See some --no-OBJECT options in pg_dump. Let's say you don't
>>>> want the TABLESPACE or the table access method while getting the CREATE TABLE
>>>> DDL because it is different in the other database.
>>>>
>>>>
>>>> +1
>>>>
>>>>
>>>> I received a review comment suggesting the use of tabs. I also looked
>>>> up PostgreSQL best practices on google, which recommend using tabs for
>>>> indentation and spaces for alignment. I’m open to updating my code
>>>> accordingly.
>>>>
>>>>
>>>> I didn't check all of the possible output but the majority uses space instead
>>>> of tabs. Check psql. If you check the git history (git log --grep=tabs), you
>>>> will notice that tabs are removed from source code.
>>>>
>>>>
>>>>
>>>> We should follow the pretty printing style in ruleutils.c, which uses
>>>> spaces.
>>>>
>>>>
>>>> * permission. I don't think you need to check for permissions inside the
>>>> function. I wouldn't want a different behavior than pg_dump(all). You can
>>>> always adjust it in system_functions.sql.
>>>>
>>>>
>>>> We’ve already had extensive discussions on this topic in the same
>>>> email thread, and ultimately we decided to add the permission check.
>>>>
>>>>
>>>> That's fair. Again, I expect that all of these pg_get_OBJECT_ddl functions use
>>>> the same approach. We can always relax this restriction in the future.
>>>>
>>>>
>>>>
>>>> +1
>>>>
>>>>
>>>> cheers
>>>>
>>>>
>>>> andrew
>>>>
>>>> --
>>>> Andrew Dunstan
>>>> EDB: https://www.enterprisedb.com
>>>>
>>>>
Attachments:
[application/octet-stream] v8-0002-Add-pg_get_database_ddl-function-to-reconstruct-DefElem.patch (14.5K, 3-v8-0002-Add-pg_get_database_ddl-function-to-reconstruct-DefElem.patch)
download | inline diff:
From 07bf3ed7d7c299cd6aeb955398373ed79ec0df4a Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Wed, 21 Jan 2026 14:21:28 +0530
Subject: [PATCH v8 2/2] 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 database oid.
Supported ddl_options are 'pretty', 'owner=no/false/0', 'tablespace=no/false/0' and 'defaults'.
Usage:
SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
SELECT pg_get_database_ddl('postgres', 'pretty'); // pretty-formatted DDL
SELECT pg_get_database_ddl('postgres', 'owner=no', 'tablespace=false'); -- Omits Owner and Tablespace clauses.
SELECT pg_get_database_ddl('postgres', 'pretty', 'defaults=yes'); -- Includes clauses for parameters at their default values.
SELECT pg_get_database_ddl('postgres', 'pretty', 'defaults'); -- Includes clauses for parameters at their default values.
Reference: PG-150
Author: Akshay Joshi <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Reviewed-by: Quan Zongliang <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 17 ++--
src/backend/utils/adt/ruleutils.c | 103 +++++++++++++++++++++----
src/include/utils/ddl_defaults.h | 4 +-
src/test/regress/expected/database.out | 20 ++---
src/test/regress/sql/database.sql | 14 ++--
5 files changed, 120 insertions(+), 38 deletions(-)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 2250031bb83..71a60dbc11c 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3859,7 +3859,10 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
<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>ddl_options</parameter> <type>text[]</type> </optional> )
+ <function>pg_get_database_ddl</function>
+ ( <parameter>database_id</parameter> <type>regdatabase</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>ddl_options</parameter>
+ <type>text[]</type> </optional> )
<returnvalue>text</returnvalue>
</para>
<para>
@@ -3867,8 +3870,9 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
system catalogs for a specified database. The first argument is the OID or
name of the database. The optional variadic argument is an array of text
flags to control the output. Supported options include
- <literal>pretty</literal>, <literal>--no-owner</literal>,
- <literal>--no-tablespace</literal>, and <literal>--with-defaults</literal>.
+ <literal>pretty</literal>, <literal>owner=no/false/0</literal>,
+ <literal>tablespace=no/false/0</literal>, and <literal>defaults</literal>
+ Or <literal>defaults=yes</literal>.
</para></entry>
</row>
</tbody>
@@ -3886,18 +3890,19 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</listitem>
<listitem>
<para>
- <literal>--no-owner</literal>: Omits the <literal>OWNER</literal> clause from
+ <literal>owner=no/false/0</literal>: Omits the <literal>OWNER</literal> clause from
the reconstructed statement.
</para>
</listitem>
<listitem>
<para>
- <literal>--no-tablespace</literal>: Omits the <literal>TABLESPACE</literal> clause.
+ <literal>tablespace=no/false/0</literal>: Omits the <literal>TABLESPACE</literal>
+ clause from the reconstructed statement.
</para>
</listitem>
<listitem>
<para>
- <literal>--with-defaults</literal>: Includes clauses for parameters that are
+ <literal>defaults</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.
</para>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index c6b59790655..8181010224a 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -13813,34 +13813,109 @@ parse_ddl_options(ArrayType *ddl_options)
if (ddl_options == NULL)
return flags;
- deconstruct_array(ddl_options,
- TEXTOID, -1, false, 'i',
+ deconstruct_array(ddl_options, TEXTOID, -1, false, 'i',
&options, &nulls, &n_options);
for (i = 0; i < n_options; i++)
{
char *opt;
+ char *name;
+ char *value;
if (nulls[i])
continue;
opt = TextDatumGetCString(options[i]);
+ name = opt;
+ value = strchr(opt, '=');
+
+ if (value != NULL)
+ {
+ *value = '\0';
+ value++;
+ }
+
+ /*
+ * * Logic for "owner": handle 'owner=no', 'owner=0', 'owner=false',
+ * etc. Using pg_strcasecmp for the key and parse_bool for the value.
+ */
+ if (pg_strcasecmp(name, "owner") == 0)
+ {
+ bool bval;
+
+ if (value == NULL)
+ continue;
+
+ if (parse_bool(value, &bval))
+ {
+ if (!bval)
+ flags |= PG_DDL_NO_OWNER;
+ }
+ else
+ goto invalid_value;
+ }
+ /* Logic for "tablespace" */
+ else if (pg_strcasecmp(name, "tablespace") == 0)
+ {
+ bool bval;
+
+ if (value == NULL)
+ continue;
+
+ if (parse_bool(value, &bval))
+ {
+ if (!bval)
+ flags |= PG_DDL_NO_TABLESPACE;
+ }
+ else
+ goto invalid_value;
+ }
+ /* Logic for "defaults" */
+ else if (pg_strcasecmp(name, "defaults") == 0)
+ {
+ bool bval;
+
+ /* If just 'defaults' is passed without '=val', we assume true */
+ if (value == NULL)
+ flags |= PG_DDL_WITH_DEFAULTS;
+ else if (parse_bool(value, &bval))
+ {
+ if (bval)
+ flags |= PG_DDL_WITH_DEFAULTS;
+ }
+ else
+ goto invalid_value;
+ }
+ /* Logic for "pretty" */
+ else if (pg_strcasecmp(name, "pretty") == 0)
+ {
+ /* Usually a standalone flag, but we check boolean if provided */
+ bool bval;
- /* Map strings to bitmask flags */
- if (strcmp(opt, "pretty") == 0)
- flags |= PG_DDL_PRETTY_INDENT;
- else if (strcmp(opt, "--no-owner") == 0)
- flags |= PG_DDL_NO_OWNER;
- else if (strcmp(opt, "--no-tablespace") == 0)
- flags |= PG_DDL_NO_TABLESPACE;
- else if (strcmp(opt, "--with-defaults") == 0)
- flags |= PG_DDL_WITH_DEFAULTS;
+ if (value == NULL)
+ flags |= PG_DDL_PRETTY_INDENT;
+ else if (parse_bool(value, &bval))
+ {
+ if (bval)
+ flags |= PG_DDL_PRETTY_INDENT;
+ }
+ else
+ goto invalid_value;
+ }
else
+ {
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("unrecognized option: %s", opt)));
+ errmsg("unrecognized option: %s", name)));
+ }
pfree(opt);
+ continue;
+
+invalid_value:
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid value for option \"%s\": %s", name, value)));
}
pfree(options);
@@ -13929,7 +14004,7 @@ pg_get_database_ddl_worker(Oid db_oid, ArrayType *ddl_options)
quote_identifier(dbform->datname.data));
get_formatted_string(&buf, pretty_flags, 4, "WITH");
- /* Set the OWNER in the DDL if --no-owner is not specified */
+ /* Set the OWNER in the DDL if owner is not omitted */
if (OidIsValid(dbform->datdba) && !(ddl_flags & PG_DDL_NO_OWNER))
{
get_formatted_string(&buf, pretty_flags, 8, "OWNER = %s",
@@ -13997,7 +14072,7 @@ pg_get_database_ddl_worker(Oid db_oid, ArrayType *ddl_options)
else if (is_with_defaults)
get_formatted_string(&buf, pretty_flags, 8, "LOCALE_PROVIDER = libc");
- /* Set the TABLESPACE in the DDL if --no-tablespace is not specified */
+ /* 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 */
diff --git a/src/include/utils/ddl_defaults.h b/src/include/utils/ddl_defaults.h
index 84ef61b4f3d..d17e843fe09 100644
--- a/src/include/utils/ddl_defaults.h
+++ b/src/include/utils/ddl_defaults.h
@@ -13,6 +13,8 @@
#ifndef DDL_DEFAULTS_H
#define DDL_DEFAULTS_H
+#include <stdbool.h>
+
static const struct
{
struct
@@ -34,4 +36,4 @@ static const struct
}
};
-#endif /* DDL_DEFAULTS_H */
\ No newline at end of file
+#endif /* DDL_DEFAULTS_H */
diff --git a/src/test/regress/expected/database.out b/src/test/regress/expected/database.out
index eb3a13bcf9e..a816b0b525f 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -87,28 +87,28 @@ SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
(1 row)
-- With No Owner
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner=no'));
ddl_filter
--------------------------------------------------------------
CREATE DATABASE regression_utf8 WITH CONNECTION LIMIT = 123;
(1 row)
-- With No Tablespace
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-tablespace'));
- ddl_filter
--------------------------------------------------------------------------------------------
- CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after CONNECTION LIMIT = 123;
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults', 'tablespace=no'));
+ ddl_filter
+--------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ENCODING = 'UTF8' ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
(1 row)
-- With Defaults
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--with-defaults'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults=yes'));
ddl_filter
--------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ENCODING = 'UTF8' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
(1 row)
-- With No Owner, No Tablespace and With Defaults
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner', '--no-tablespace', '--with-defaults'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner=0', 'tablespace=0', 'defaults=1'));
ddl_filter
---------------------------------------------------------------------------------------------------------
CREATE DATABASE regression_utf8 WITH ENCODING = 'UTF8' ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
@@ -124,14 +124,14 @@ CREATE DATABASE regression_utf8
CONNECTION LIMIT = 123;
(1 row)
-- With No Owner and No Tablespace
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', 'owner=no', 'tablespace=no'));
ddl_filter
CREATE DATABASE regression_utf8
WITH
CONNECTION LIMIT = 123;
(1 row)
-- With Defaults
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--with-defaults'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', 'defaults'));
ddl_filter
CREATE DATABASE regression_utf8
WITH
@@ -142,7 +142,7 @@ CREATE DATABASE regression_utf8
CONNECTION LIMIT = 123;
(1 row)
-- With No Owner, No Tablespace and With Defaults
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace', '--with-defaults'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', 'owner=false', 'tablespace=false', 'defaults=true'));
ddl_filter
CREATE DATABASE regression_utf8
WITH
diff --git a/src/test/regress/sql/database.sql b/src/test/regress/sql/database.sql
index 918b28c47da..4cb17a8d0d6 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -86,29 +86,29 @@ SELECT pg_get_database_ddl(NULL);
SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
-- With No Owner
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner=no'));
-- With No Tablespace
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-tablespace'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults', 'tablespace=no'));
-- With Defaults
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--with-defaults'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults=yes'));
-- With No Owner, No Tablespace and With Defaults
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner', '--no-tablespace', '--with-defaults'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner=0', 'tablespace=0', 'defaults=1'));
-- With Pretty formatted
\pset format unaligned
SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty'));
-- With No Owner and No Tablespace
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', 'owner=no', 'tablespace=no'));
-- With Defaults
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--with-defaults'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', 'defaults'));
-- With No Owner, No Tablespace and With Defaults
-SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace', '--with-defaults'));
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', 'owner=false', 'tablespace=false', 'defaults=true'));
DROP DATABASE regression_utf8;
DROP ROLE regress_datdba_before;
--
2.51.0
[application/octet-stream] v8-0001-Add-pg_get_database_ddl-function-to-reconstruct-double-dash.patch (26.4K, 4-v8-0001-Add-pg_get_database_ddl-function-to-reconstruct-double-dash.patch)
download | inline diff:
From 77b9c4d3165b52ebe696a007c6f2cc813cabadfc Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Wed, 24 Sep 2025 17:47:59 +0530
Subject: [PATCH v8 1/2] 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 database oid.
Supported ddl_options are 'pretty', '--no-owner', '--no-tablespace' and '--with-defaults'.
Usage:
SELECT pg_get_database_ddl('postgres'); // Non pretty-formatted DDL
SELECT pg_get_database_ddl(16835); // Non pretty-formatted DDL
SELECT pg_get_database_ddl('postgres', 'pretty'); // pretty-formatted DDL
SELECT pg_get_database_ddl('postgres', '--no-owner', '--no-tablespace'); // Omits the Owner and Tablespace clause from the DDL.
SELECT pg_get_database_ddl('postgres', 'pretty', '--with-defaults'); // Includes clauses for parameters that are currently at their default values.
Reference: PG-150
Author: Akshay Joshi <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Reviewed-by: Quan Zongliang <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 77 ++++++
src/backend/catalog/system_functions.sql | 6 +
src/backend/utils/adt/ruleutils.c | 291 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +
src/include/utils/ddl_defaults.h | 37 +++
src/test/regress/expected/database.out | 134 +++++++++++
src/test/regress/sql/database.sql | 91 +++++++
7 files changed, 642 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 175f18315cd..2250031bb83 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3830,4 +3830,81 @@ 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 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>ddl_options</parameter> <type>text[]</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE DATABASE</command> statement from the
+ system catalogs for a specified database. The first argument is the OID or
+ name of the database. The optional variadic argument is an array of text
+ flags to control the output. Supported options include
+ <literal>pretty</literal>, <literal>--no-owner</literal>,
+ <literal>--no-tablespace</literal>, and <literal>--with-defaults</literal>.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <parameter>ddl_options</parameter> for <function>pg_get_database_ddl</function>
+ provide fine-grained control over the generated SQL:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>pretty</literal>: Adds newlines and indentation for better readability.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>--no-owner</literal>: Omits the <literal>OWNER</literal> clause from
+ the reconstructed statement.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>--no-tablespace</literal>: Omits the <literal>TABLESPACE</literal> clause.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>--with-defaults</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.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ </sect2>
+
</sect1>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index eb9e31ae1bf..16c0d52479a 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -657,6 +657,12 @@ LANGUAGE INTERNAL
STRICT VOLATILE PARALLEL UNSAFE
AS 'pg_replication_origin_session_setup';
+CREATE OR REPLACE FUNCTION
+ pg_get_database_ddl(database_id regdatabase, VARIADIC ddl_options text[] DEFAULT '{}')
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl';
+
--
-- The default permissions for functions mean that anyone can execute them.
-- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 033b625f3fc..c6b59790655 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,22 @@
#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
+
+
/* 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
@@ -546,6 +560,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, ArrayType *ddl_options);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -13743,3 +13762,275 @@ 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 */
+ for (int i = 0; i < nSpaces; i++)
+ {
+ appendStringInfoChar(buf, ' ');
+ }
+ }
+ else
+ appendStringInfoChar(buf, ' ');
+
+ va_start(args, fmt);
+ appendStringInfoVA(buf, fmt, args);
+ va_end(args);
+}
+
+/**
+ * parse_ddl_options - Generic helper to parse variadic text options
+ * ddl_options: The ArrayType from PG_GETARG_ARRAYTYPE_P
+ * flags: Bitmask to set options while parsing DDL options.
+ */
+static uint64
+parse_ddl_options(ArrayType *ddl_options)
+{
+ uint64 flags = 0;
+ Datum *options;
+ bool *nulls;
+ int n_options;
+ int i;
+
+ /* If no options provided, return the empty bitmask */
+ if (ddl_options == NULL)
+ return flags;
+
+ deconstruct_array(ddl_options,
+ TEXTOID, -1, false, 'i',
+ &options, &nulls, &n_options);
+
+ for (i = 0; i < n_options; i++)
+ {
+ char *opt;
+
+ if (nulls[i])
+ continue;
+
+ opt = TextDatumGetCString(options[i]);
+
+ /* Map strings to bitmask flags */
+ if (strcmp(opt, "pretty") == 0)
+ flags |= PG_DDL_PRETTY_INDENT;
+ else if (strcmp(opt, "--no-owner") == 0)
+ flags |= PG_DDL_NO_OWNER;
+ else if (strcmp(opt, "--no-tablespace") == 0)
+ flags |= PG_DDL_NO_TABLESPACE;
+ else if (strcmp(opt, "--with-defaults") == 0)
+ flags |= PG_DDL_WITH_DEFAULTS;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unrecognized option: %s", opt)));
+
+ pfree(opt);
+ }
+
+ pfree(options);
+ pfree(nulls);
+
+ return flags;
+}
+
+/*
+ * pg_get_database_ddl
+ *
+ * Generate a CREATE DATABASE statement for the specified database name or oid.
+ *
+ * db_oid - OID/Name of the database for which to generate the DDL.
+ * ddl_options - Array of text options to modify the output.
+ */
+Datum
+pg_get_database_ddl(PG_FUNCTION_ARGS)
+{
+ Oid db_oid = PG_GETARG_OID(0);
+ ArrayType *ddl_options = PG_GETARG_ARRAYTYPE_P(1);
+ char *res;
+
+ res = pg_get_database_ddl_worker(db_oid, ddl_options);
+
+ if (res == NULL)
+ PG_RETURN_NULL();
+
+ PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+static char *
+pg_get_database_ddl_worker(Oid db_oid, ArrayType *ddl_options)
+{
+ char *dbowner = NULL;
+ bool attr_isnull;
+ Datum dbvalue;
+ HeapTuple tuple_database;
+ Form_pg_database dbform;
+ StringInfoData buf;
+ AclResult aclresult;
+
+ /* Variables for ddl_options parsing */
+ int pretty_flags = 0;
+ uint64 ddl_flags = 0;
+ bool is_with_defaults = false;
+
+ /* Call DDL options parser */
+ ddl_flags = parse_ddl_options(ddl_options);
+
+ /* 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) ? true : false;
+
+ /*
+ * 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);
+
+ /* Look up the owner in the system catalog */
+ if (OidIsValid(dbform->datdba))
+ dbowner = GetUserNameFromId(dbform->datdba, false);
+
+ /* 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 --no-owner is not specified */
+ if (OidIsValid(dbform->datdba) && !(ddl_flags & PG_DDL_NO_OWNER))
+ {
+ get_formatted_string(&buf, pretty_flags, 8, "OWNER = %s",
+ quote_identifier(dbowner));
+ }
+
+ /* Set the ENCODING in the DDL */
+ if (dbform->encoding != 0)
+ {
+ /* If it's with defaults, we skip default encoding check */
+ if (is_with_defaults ||
+ (pg_strcasecmp(pg_encoding_to_char(dbform->encoding),
+ DDL_DEFAULTS.DATABASE.ENCODING) != 0))
+ {
+ get_formatted_string(&buf, pretty_flags, 8, "ENCODING = %s",
+ quote_literal_cstr(
+ pg_encoding_to_char(dbform->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 --no-tablespace is not specified */
+ 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 (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 894b6a1b6d6..53d623de58f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4030,6 +4030,12 @@
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', proisstrict => 'f', prorettype => 'text',
+ proargtypes => 'regdatabase _text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{regdatabase,_text}',
+ 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..84ef61b4f3d
--- /dev/null
+++ b/src/include/utils/ddl_defaults.h
@@ -0,0 +1,37 @@
+/*-------------------------------------------------------------------------
+ *
+ * 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 *ENCODING;
+ const char *TABLESPACE;
+ int CONN_LIMIT;
+ bool ALLOW_CONN;
+ } DATABASE;
+
+ /* Add more object types as needed */
+} DDL_DEFAULTS = {
+
+ .DATABASE = {
+ .ENCODING = "UTF8",
+ .TABLESPACE = "pg_default",
+ .CONN_LIMIT = -1,
+ .ALLOW_CONN = true,
+ }
+};
+
+#endif /* DDL_DEFAULTS_H */
\ No newline at end of file
diff --git a/src/test/regress/expected/database.out b/src/test/regress/expected/database.out
index 6b879b0f62a..eb3a13bcf9e 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -1,3 +1,57 @@
+--
+-- 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'
+ );
+
+ 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 +70,86 @@ 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);
+ERROR: database with oid 0 does not exist
+-- Without pretty
+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', '--no-owner'));
+ ddl_filter
+--------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-tablespace'));
+ ddl_filter
+-------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--with-defaults'));
+ ddl_filter
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ENCODING = 'UTF8' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner', '--no-tablespace', '--with-defaults'));
+ ddl_filter
+---------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH ENCODING = 'UTF8' ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123;
+(1 row)
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty'));
+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', '--no-owner', '--no-tablespace'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--with-defaults'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ OWNER = regress_datdba_after
+ ENCODING = 'UTF8'
+ TABLESPACE = pg_default
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace', '--with-defaults'));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ ENCODING = 'UTF8'
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123;
+(1 row)
DROP DATABASE regression_utf8;
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..918b28c47da 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -1,3 +1,59 @@
+--
+-- 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'
+ );
+
+ 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 +75,41 @@ 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 pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+
+-- With No Owner
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner'));
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-tablespace'));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--with-defaults'));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', '--no-owner', '--no-tablespace', '--with-defaults'));
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty'));
+
+-- With No Owner and No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace'));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--with-defaults'));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', '--no-owner', '--no-tablespace', '--with-defaults'));
+
DROP DATABASE regression_utf8;
DROP ROLE regress_datdba_before;
DROP ROLE regress_datdba_after;
--
2.51.0
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
2026-01-07 23:27 Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Andrew Dunstan <[email protected]>
2026-01-14 15:48 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
2026-01-16 07:23 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
2026-01-20 11:36 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
2026-01-21 09:31 ` Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Akshay Joshi <[email protected]>
@ 2026-01-31 00:27 ` Mark Wong <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Mark Wong @ 2026-01-31 00:27 UTC (permalink / raw)
To: Akshay Joshi <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; Euler Taveira <[email protected]>; Álvaro Herrera <[email protected]>; Chao Li <[email protected]>; japin <[email protected]>; Quan Zongliang <[email protected]>; pgsql-hackers
On Wed, Jan 21, 2026 at 03:01:44PM +0530, Akshay Joshi wrote:
> In my previous email, I included two different patches (for two separate
> approaches) from different branches. As a result, CommitFest is indicating
> that a rebase is required.
>
> Apologies for the inconvenience, I’m still getting familiar with the
> process.
>
> Attached are the patches, layered one on top of the other, representing two
> approaches:
>
> - *Double Dash*:
> v8-0001-Add-pg_get_database_ddl-function-to-reconstruct-double-dash.patch
> - *DefElem (Key-Value)*:
> v8-0002-Add-pg_get_database_ddl-function-to-reconstruct-DefElem.patch
>
> I am now submitting the *v8 patches*, which are ready for review. Please
> let me know which approach you find more suitable and preferable.
I have some documentation style suggestions that I've also attached as a patch:
* Update the pg_get_database_ddl() definitions in the func-info table to list
the multiple database way to reference a database, similar to
pg_database_size()
* Shorten the argument description for OID and name, again similar to
pg_database_size()'s style
* Shorten ddl_options argument description to simply options, to match the
style of how other functions in the docs describe optional parameters
* Remove the redundant explanation of the optional parameters in the individual
function's description, and instead refer the reader to the explanation
following the table
* Some word choice and phrasing changes in the pg_get_database_ddl function
description to be grammatically closer to something like
pg_restore_relation_stats()
I hope that is helpful...
Regards,
Mark
--
Mark Wong <[email protected]>
EDB https://enterprisedb.com
Attachments:
[text/x-diff] v8-0003-doc-suggestions.patch (3.0K, 2-v8-0003-doc-suggestions.patch)
download | inline diff:
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 218578c318f..4fc74ca29a1 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3874,20 +3874,21 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
<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>ddl_options</parameter>
- <type>text[]</type> </optional> )
+ <function>pg_get_database_ddl</function> ( <type>name</type> <optional>, <literal>VARIADIC</literal> <parameter>options</parameter> <type>text[]</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para role="func_signature">
+ <indexterm>
+ <primary>pg_get_database_ddl</primary>
+ </indexterm>
+ <function>pg_get_database_ddl</function> ( <type>oid</type> <optional>, <literal>VARIADIC</literal> <parameter>options</parameter> <type>text[]</type> </optional> )
<returnvalue>text</returnvalue>
</para>
<para>
Reconstructs the <command>CREATE DATABASE</command> statement from the
- system catalogs for a specified database. The first argument is the OID or
- name of the database. The optional variadic argument is an array of text
- flags to control the output. Supported options include
- <literal>pretty</literal>, <literal>owner=no/false/0</literal>,
- <literal>tablespace=no/false/0</literal>, and <literal>defaults</literal>
- Or <literal>defaults=yes</literal>.
+ system catalogs for a specified database by name or OID. The optional
+ variadic argument is an array of arguments to control the output
+ formatting and content. Supported arguments explained below.
</para></entry>
</row>
</tbody>
@@ -3895,8 +3896,8 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</table>
<para>
- The <parameter>ddl_options</parameter> for <function>pg_get_database_ddl</function>
- provide fine-grained control over the generated SQL:
+ The <parameter>options</parameter> for these object DDL functions provide
+ fine-grained control over the generated SQL:
<itemizedlist>
<listitem>
<para>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 16c0d52479a..69a098561e7 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -658,7 +658,7 @@ STRICT VOLATILE PARALLEL UNSAFE
AS 'pg_replication_origin_session_setup';
CREATE OR REPLACE FUNCTION
- pg_get_database_ddl(database_id regdatabase, VARIADIC ddl_options text[] DEFAULT '{}')
+ pg_get_database_ddl(database_id regdatabase, VARIADIC options text[] DEFAULT '{}')
RETURNS text
LANGUAGE internal
AS 'pg_get_database_ddl';
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-01-31 00:27 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-07 23:27 Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement Andrew Dunstan <[email protected]>
2026-01-14 15:48 ` Akshay Joshi <[email protected]>
2026-01-16 07:23 ` Akshay Joshi <[email protected]>
2026-01-20 11:36 ` Akshay Joshi <[email protected]>
2026-01-21 09:31 ` Akshay Joshi <[email protected]>
2026-01-31 00:27 ` Mark Wong <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox