On Wed, Mar 11, 2026 at 3:35 AM Zsolt Parragi <[email protected]>
wrote:
> Hello
>
> Is ruleutils.c the best place for this function?
>
> It's already huge, and it has a different scope: "Functions to convert
> stored expressions/querytrees back to source text"
>
Created the ddlutils.c file.
>
> + /* 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)));
>
> pg_dumpall only shows this for binary upgrade, otherwise skips it. Is
> it okay for this command to print it by default, shouldn't it depend
> on is_with_defaults or something similar?
>
Shows only when `is_with_defaults` is true.
>
> +#ifndef DDL_DEFAULTS_H
> +#define DDL_DEFAULTS_H
> +
> +static const struct
> +{
> + struct
> + {
> ....
>
> This file seems strange. A static const struct in a header with
> uppercase names doesn't seem to follow postgres conventions?
> DATCONNLIMIT_UNLIMITED alredy exists as a definition, and probably
> should be used instead or referenced, or the existing uses should
> refer to the new way of defining it.
>
Removed the header file and implemented an alternative logic. Note that
a similar file may be necessary in the future to handle default values for
other pg_get_<object>_ddl.
>
> + /* 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);
>
> Can these be ever nulls?
>
> Also, pg_dump only emits LOCALE if ctype==collate, shouldn't this
> follow the same pattern?
>
> + else if (is_with_defaults)
> + get_formatted_string(&buf, pretty_flags, 8, "LOCALE_PROVIDER = libc");
>
> Doesn't pg_dump always emit this? Shouldn't this function follow the
> same convention? Emitting it seems to be a safer default, in case
> postgres ever changes this.
>
> + /* Build the CREATE DATABASE statement */
> + appendStringInfo(&buf, "CREATE DATABASE %s",
> + quote_identifier(dbform->datname.data));
> + get_formatted_string(&buf, pretty_flags, 4, "WITH");
>
> Shouldn't we only emit "WITH" if it is actually followed by something,
> not unconditionally?
>
> +/*
> + * get_formatted_string
> + *
> + * Return a formatted version of the string.
>
> But it's a void function.
>
>
> + else if (!attr_isnull)
> + get_formatted_string(&buf, pretty_flags, 8, "LOCALE = %s",
> + quote_literal_cstr(TextDatumGetCString(dbvalue)));
> +
>
> Can this ever happen, shouldn't it be an assertion instead?
>
Fixed all the preceding review comments.
Attached is the *v15 patch*, now ready for further review.
From 757098bb278cc6ee1de9b3d1022829f4b74ffbc3 Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Fri, 6 Mar 2026 16:46:02 +0530
Subject: [PATCH v15] Add pg_get_database_ddl() function to reconstruct CREATE
DATABASE statements.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This adds a new system function, pg_get_database_ddl(database_name/database_oid, ddl_options),
which reconstructs the CREATE DATABASE statement for a given database name or OID.
Supported ddl_options are 'pretty', 'owner', 'tablespace' and 'defaults' and respective
values could be 'yes'/'on'/true/'1' or 'no'/'off'/false/'0'.
Usage:
SELECT pg_get_database_ddl('regression_utf8', 'owner', 'no', 'defaults', 'yes');
SELECT pg_get_database_ddl('regression_utf8', 'owner', '0', 'tablespace', '0', 'defaults', '1');
SELECT pg_get_database_ddl('regression_utf8', 'owner', 'off', 'pretty', 'on');
SELECT pg_get_database_ddl('regression_utf8', 'owner', false, 'defaults', 'true', 'tablespace', 'no');
Reference: PG-150
Author: Akshay Joshi <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
Reviewed-by: Euler Taveira <[email protected]>
Reviewed-by: Quan Zongliang <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Reviewed-by: Rafia Sabih <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 91 +++++
src/backend/catalog/system_functions.sql | 6 +
src/backend/utils/adt/Makefile | 1 +
src/backend/utils/adt/ddlutils.c | 486 +++++++++++++++++++++++
src/backend/utils/adt/meson.build | 1 +
src/backend/utils/adt/ruleutils.c | 2 +-
src/include/catalog/pg_proc.dat | 7 +
src/test/regress/expected/database.out | 186 +++++++++
src/test/regress/sql/database.sql | 120 ++++++
src/tools/pgindent/typedefs.list | 1 +
10 files changed, 900 insertions(+), 1 deletion(-)
create mode 100644 src/backend/utils/adt/ddlutils.c
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 294f45e82a3..6915408ae30 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3845,4 +3845,95 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</sect2>
+ <sect2 id="functions-get-object-ddl">
+ <title>Get Object DDL Functions</title>
+
+ <para>
+ The functions described in <xref linkend="functions-get-object-ddl-table"/>
+ return the Data Definition Language (DDL) statement for any given database object.
+ This feature is implemented as a set of distinct functions, one for each object type.
+ </para>
+
+ <table id="functions-get-object-ddl-table">
+ <title>Get Object DDL Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_database_ddl</primary>
+ </indexterm>
+ <function>pg_get_database_ddl</function>
+ ( <parameter>database_id</parameter> <type>regdatabase</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>"any"</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE DATABASE</command> statement for the
+ specified database (identified by name or OID) from the system
+ catalogs. The optional variadic arguments are name/value pairs that
+ control the output
+ formatting and content (e.g., <literal>'pretty', true, 'owner', false</literal>).
+ Supported options are explained below.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <parameter>options</parameter> for <function>pg_get_database_ddl</function>
+ provide fine-grained control over the generated SQL. Options are passed as
+ alternating key/value pairs where the key is a text string and the
+ value is either a boolean or a text string representing a boolean
+ (<literal>true</literal>, <literal>false</literal>, <literal>yes</literal>,
+ <literal>no</literal>, <literal>1</literal>, <literal>0</literal>,
+ <literal>on</literal>, <literal>off</literal>):
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>'pretty', true</literal> (or <literal>'pretty', 'yes'</literal>):
+ Formats the output with newlines and indentation for better readability.
+ This option defaults to false.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>'owner', false</literal> (or <literal>'owner', 'no'</literal>):
+ Omits the <literal>OWNER</literal> clause from the reconstructed statement.
+ This option defaults to true.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>'tablespace', false</literal> (or <literal>'tablespace', '0'</literal>):
+ Omits the <literal>TABLESPACE</literal> clause from the reconstructed statement.
+ This option defaults to true.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>'defaults', true</literal> (or <literal>'defaults', '1'</literal>):
+ Includes clauses for parameters that are currently at their default values
+ (e.g., <literal>CONNECTION LIMIT -1</literal>), which are normally omitted for brevity.
+ This option defaults to false.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ </sect2>
+
</sect1>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 1c5b6d6df05..fa48e2f0775 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -372,3 +372,9 @@ CREATE OR REPLACE FUNCTION ts_debug(document text,
BEGIN ATOMIC
SELECT * FROM ts_debug(get_current_ts_config(), $1);
END;
+
+CREATE OR REPLACE FUNCTION
+ pg_get_database_ddl(database_id regdatabase, VARIADIC options "any" DEFAULT NULL)
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl';
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index a8fd680589f..4fdd541f7bf 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -102,6 +102,7 @@ OBJS = \
regproc.o \
ri_triggers.o \
rowtypes.o \
+ ddlutils.o \
ruleutils.o \
selfuncs.o \
skipsupport.o \
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
new file mode 100644
index 00000000000..6e88b8a1a92
--- /dev/null
+++ b/src/backend/utils/adt/ddlutils.c
@@ -0,0 +1,486 @@
+/*-------------------------------------------------------------------------
+ *
+ * ddlutils.c
+ * Functions to reconstruct DDL statements from catalog data.
+ *
+ * Unlike ruleutils.c (which deparses expressions and query trees),
+ * these functions generate DDL by reading catalog attributes directly.
+ *
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/ddlutils.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <stdarg.h>
+
+#include "access/htup_details.h"
+#include "catalog/pg_authid.h"
+#include "catalog/pg_collation.h"
+#include "catalog/pg_database.h"
+#include "catalog/pg_tablespace.h"
+#include "commands/tablespace.h"
+#include "funcapi.h"
+#include "mb/pg_wchar.h"
+#include "miscadmin.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
+#include "utils/lsyscache.h"
+#include "utils/syscache.h"
+
+/* Pretty flags (subset needed for DDL formatting) */
+#define PRETTYFLAG_INDENT 0x0002
+
+/* DDL Options flags */
+#define PG_DDL_PRETTY_INDENT 0x00000001
+#define PG_DDL_WITH_DEFAULTS 0x00000002
+#define PG_DDL_NO_OWNER 0x00000004
+#define PG_DDL_NO_TABLESPACE 0x00000008
+
+/*
+ * Structure to define DDL options for parse_ddl_options().
+ * This allows easy addition of new options in the future.
+ */
+typedef struct DDLOptionDef
+{
+ const char *name; /* Option name (case-insensitive) */
+ uint32 flag; /* Flag to set */
+ bool set_on_true; /* If true, set flag when value is true; if
+ * false, set flag when value is false */
+} DDLOptionDef;
+
+/*
+ * Array of supported DDL options.
+ * To add a new option, simply add an entry to this array.
+ */
+static const DDLOptionDef ddl_option_defs[] = {
+ {"pretty", PG_DDL_PRETTY_INDENT, true},
+ {"defaults", PG_DDL_WITH_DEFAULTS, true},
+ {"owner", PG_DDL_NO_OWNER, false},
+ {"tablespace", PG_DDL_NO_TABLESPACE, false},
+};
+
+#define GET_DDL_PRETTY_FLAGS(pretty) \
+ ((pretty) ? (PRETTYFLAG_INDENT) \
+ : 0)
+
+/* Local function declarations */
+static char *pg_get_database_ddl_worker(Oid db_oid, uint32 ddl_flags);
+static void get_formatted_string(StringInfo buf, int prettyFlags, int nSpaces, const char *fmt,...) pg_attribute_printf(4, 5);
+static uint32 parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start);
+
+/*
+ * get_formatted_string
+ *
+ * Helper function to append formatted strings to a StringInfo buffer, with
+ * optional pretty-printing based on flags.
+ *
+ * prettyFlags - Based on prettyFlags the output includes spaces and
+ * newlines (\n).
+ * nSpaces - indent with specified number of space characters.
+ * fmt - printf-style format string used by appendStringInfoVA.
+ */
+static void
+get_formatted_string(StringInfo buf, int prettyFlags, int nSpaces, const char *fmt,...)
+{
+ va_list args;
+
+ if (prettyFlags & PRETTYFLAG_INDENT)
+ {
+ appendStringInfoChar(buf, '\n');
+ /* Indent with spaces */
+ appendStringInfoSpaces(buf, nSpaces);
+ }
+ else
+ appendStringInfoChar(buf, ' ');
+
+ for (;;)
+ {
+ int needed;
+
+ va_start(args, fmt);
+ needed = appendStringInfoVA(buf, fmt, args);
+ va_end(args);
+ if (needed == 0)
+ break;
+ enlargeStringInfo(buf, needed);
+ }
+}
+
+/*
+ * parse_ddl_options - Generic helper to parse variadic name/value options
+ * fcinfo: The FunctionCallInfo from the calling function
+ * variadic_start: The argument position where variadic arguments start
+ *
+ * Returns: Bitmask of flags based on the parsed options.
+ *
+ * Options are passed as name/value pairs.
+ * For example: pg_get_database_ddl('mydb', 'owner', false, 'pretty', true)
+ */
+static uint32
+parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start)
+{
+ uint32 flags = 0;
+ uint32 seen_flags = 0;
+ Datum *args;
+ bool *nulls;
+ Oid *types;
+ int nargs;
+
+ /* Extract variadic arguments */
+ nargs = extract_variadic_args(fcinfo, variadic_start, true,
+ &args, &types, &nulls);
+
+ /* If no options provided (VARIADIC NULL), return the empty bitmask */
+ if (nargs <= 0)
+ return flags;
+
+ /*
+ * Handle the case where DEFAULT NULL was used and no explicit variadic
+ * arguments were provided. In this case, we get a single NULL argument.
+ */
+ if (nargs == 1 && nulls[0])
+ return flags;
+
+ /* Arguments must come in name/value pairs */
+ if (nargs % 2 != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("variadic arguments must be name/value pairs"),
+ errhint("Provide an even number of variadic arguments that can be divided into pairs.")));
+
+ for (int i = 0; i < nargs; i += 2)
+ {
+ char *name;
+ bool bval;
+ bool found = false;
+
+ /* Key must not be null */
+ if (nulls[i])
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("name at variadic position %d is null", i + 1)));
+
+ /* Key must be text type */
+ if (types[i] != TEXTOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("name at variadic position %d has type %s, expected type %s",
+ i + 1, format_type_be(types[i]),
+ format_type_be(TEXTOID))));
+
+ name = TextDatumGetCString(args[i]);
+
+ /* Value must not be null */
+ if (nulls[i + 1])
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("value for option \"%s\" must not be null",
+ name)));
+
+ /* Value must be boolean or text type */
+ if (types[i + 1] == BOOLOID)
+ {
+ bval = DatumGetBool(args[i + 1]);
+ }
+ else if (types[i + 1] == TEXTOID)
+ {
+ char *valstr = TextDatumGetCString(args[i + 1]);
+
+ if (!parse_bool(valstr, &bval))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("value for option \"%s\" at position %d has invalid value \"%s\"",
+ name, i + 2, valstr),
+ errhint("Valid values are: true, false, yes, no, 1, 0, on, off.")));
+ pfree(valstr);
+ }
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("value for option \"%s\" at position %d has type %s, expected type boolean or text",
+ name, i + 2, format_type_be(types[i + 1]))));
+ }
+
+ /*
+ * Look up the option in the ddl_option_defs array and set the
+ * appropriate flag based on the value.
+ */
+ for (int j = 0; j < lengthof(ddl_option_defs); j++)
+ {
+ const DDLOptionDef *opt = &ddl_option_defs[j];
+
+ if (pg_strcasecmp(name, opt->name) == 0)
+ {
+ /* Error if this option was already specified */
+ if (seen_flags & opt->flag)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("option \"%s\" is specified more than once", name)));
+
+ seen_flags |= opt->flag;
+
+ /*
+ * Set the flag if the value matches the set_on_true
+ * condition: if set_on_true is true, set flag when bval is
+ * true; if set_on_true is false, set flag when bval is false.
+ */
+ if (bval == opt->set_on_true)
+ flags |= opt->flag;
+
+ found = true;
+ break;
+ }
+ }
+
+ if (!found)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unrecognized option: \"%s\"", name)));
+
+ pfree(name);
+ }
+
+ return flags;
+}
+
+/*
+ * pg_get_database_ddl
+ *
+ * Generate a CREATE DATABASE statement for the specified database oid.
+ *
+ * db_oid - OID of the database for which to generate the DDL.
+ * options - Variadic name/value pairs to modify the output.
+ */
+Datum
+pg_get_database_ddl(PG_FUNCTION_ARGS)
+{
+ Oid db_oid;
+ uint32 ddl_flags;
+ char *res;
+
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ db_oid = PG_GETARG_OID(0);
+
+ /* Parse variadic options starting from argument 1 */
+ ddl_flags = parse_ddl_options(fcinfo, 1);
+
+ res = pg_get_database_ddl_worker(db_oid, ddl_flags);
+
+ PG_RETURN_TEXT_P(cstring_to_text(res));
+}
+
+static char *
+pg_get_database_ddl_worker(Oid db_oid, uint32 ddl_flags)
+{
+ const char *encoding;
+ bool attr_isnull;
+ Datum dbvalue;
+ HeapTuple tuple_database;
+ Form_pg_database dbform;
+ StringInfoData buf;
+ StringInfoData optbuf;
+ AclResult aclresult;
+ HeapTuple tmpl_tuple;
+ int tmpl_encoding = -1;
+ char *collate;
+ char *ctype;
+
+ /* Variables for ddl_options parsing */
+ int pretty_flags = 0;
+ bool is_with_defaults = false;
+
+ /* Set the appropriate flags */
+ if (ddl_flags & PG_DDL_PRETTY_INDENT)
+ pretty_flags = GET_DDL_PRETTY_FLAGS(1);
+
+ is_with_defaults = (ddl_flags & PG_DDL_WITH_DEFAULTS) != 0;
+
+ /*
+ * User must have connect privilege for target database.
+ */
+ aclresult = object_aclcheck(DatabaseRelationId, db_oid, GetUserId(),
+ ACL_CONNECT);
+ if (aclresult != ACLCHECK_OK &&
+ !has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_STATS))
+ {
+ aclcheck_error(aclresult, OBJECT_DATABASE,
+ get_database_name(db_oid));
+ }
+
+ /* Look up the database in pg_database */
+ tuple_database = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(db_oid));
+ if (!HeapTupleIsValid(tuple_database))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("database with oid %u does not exist", db_oid));
+
+ dbform = (Form_pg_database) GETSTRUCT(tuple_database);
+
+ initStringInfo(&buf);
+ initStringInfo(&optbuf);
+
+ /*
+ * Build the options into a separate buffer first, so we can emit WITH
+ * only when there are options to show.
+ */
+
+ /* Set the OWNER in the DDL if owner is not omitted */
+ if (OidIsValid(dbform->datdba) && !(ddl_flags & PG_DDL_NO_OWNER))
+ {
+ char *dbowner = GetUserNameFromId(dbform->datdba, false);
+
+ get_formatted_string(&optbuf, pretty_flags, 8, "OWNER = %s",
+ quote_identifier(dbowner));
+ }
+
+ /*
+ * Emit ENCODING if it differs from template1's encoding, or if defaults
+ * are requested. The default encoding for CREATE DATABASE comes from the
+ * template database (template1), not a fixed value.
+ */
+ encoding = pg_encoding_to_char(dbform->encoding);
+
+ tmpl_tuple = SearchSysCache1(DATABASEOID,
+ ObjectIdGetDatum(Template1DbOid));
+ if (HeapTupleIsValid(tmpl_tuple))
+ {
+ Form_pg_database tmplform = (Form_pg_database) GETSTRUCT(tmpl_tuple);
+
+ tmpl_encoding = tmplform->encoding;
+ ReleaseSysCache(tmpl_tuple);
+ }
+
+ if (is_with_defaults || dbform->encoding != tmpl_encoding)
+ get_formatted_string(&optbuf, pretty_flags, 8, "ENCODING = %s",
+ quote_literal_cstr(encoding));
+
+ /*
+ * LC_COLLATE and LC_CTYPE are BKI_FORCE_NOT_NULL, always present. Emit
+ * LOCALE when they match (like pg_dump), otherwise emit separately.
+ */
+ dbvalue = SysCacheGetAttr(DATABASEOID, tuple_database,
+ Anum_pg_database_datcollate, &attr_isnull);
+ Assert(!attr_isnull);
+ collate = TextDatumGetCString(dbvalue);
+
+ dbvalue = SysCacheGetAttr(DATABASEOID, tuple_database,
+ Anum_pg_database_datctype, &attr_isnull);
+ Assert(!attr_isnull);
+ ctype = TextDatumGetCString(dbvalue);
+
+ if (strcmp(collate, ctype) == 0)
+ {
+ get_formatted_string(&optbuf, pretty_flags, 8, "LOCALE = %s",
+ quote_literal_cstr(collate));
+ }
+ else
+ {
+ get_formatted_string(&optbuf, pretty_flags, 8, "LC_COLLATE = %s",
+ quote_literal_cstr(collate));
+ get_formatted_string(&optbuf, pretty_flags, 8, "LC_CTYPE = %s",
+ quote_literal_cstr(ctype));
+ }
+
+
+ /*
+ * Fetch datlocale: emit as BUILTIN_LOCALE or ICU_LOCALE depending on the
+ * provider. For libc, datlocale should always be NULL.
+ */
+ dbvalue = SysCacheGetAttr(DATABASEOID, tuple_database,
+ Anum_pg_database_datlocale, &attr_isnull);
+ if (!attr_isnull && dbform->datlocprovider == COLLPROVIDER_BUILTIN)
+ get_formatted_string(&optbuf, pretty_flags, 8, "BUILTIN_LOCALE = %s",
+ quote_literal_cstr(TextDatumGetCString(dbvalue)));
+ else if (!attr_isnull && dbform->datlocprovider == COLLPROVIDER_ICU)
+ get_formatted_string(&optbuf, pretty_flags, 8, "ICU_LOCALE = %s",
+ quote_literal_cstr(TextDatumGetCString(dbvalue)));
+ else
+ Assert(attr_isnull);
+
+ /* 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(&optbuf, pretty_flags, 8, "ICU_RULES = %s",
+ quote_literal_cstr(TextDatumGetCString(dbvalue)));
+
+ /*
+ * Emit COLLATION_VERSION only when defaults are requested. Normally this
+ * is an internal implementation detail that should be determined freshly
+ * by the target cluster (similar to how pg_dump only emits it during
+ * binary upgrades).
+ */
+ if (is_with_defaults)
+ {
+ dbvalue = SysCacheGetAttr(DATABASEOID, tuple_database,
+ Anum_pg_database_datcollversion, &attr_isnull);
+ if (!attr_isnull)
+ get_formatted_string(&optbuf, pretty_flags, 8, "COLLATION_VERSION = %s",
+ quote_literal_cstr(TextDatumGetCString(dbvalue)));
+ }
+
+ /* Set the appropriate LOCALE_PROVIDER */
+ if (dbform->datlocprovider == COLLPROVIDER_BUILTIN)
+ get_formatted_string(&optbuf, pretty_flags, 8, "LOCALE_PROVIDER = builtin");
+ else if (dbform->datlocprovider == COLLPROVIDER_ICU)
+ get_formatted_string(&optbuf, pretty_flags, 8, "LOCALE_PROVIDER = icu");
+ else
+ get_formatted_string(&optbuf, pretty_flags, 8, "LOCALE_PROVIDER = libc");
+
+ /* Set the TABLESPACE in the DDL if tablespace is not omitted */
+ if (OidIsValid(dbform->dattablespace) && !(ddl_flags & PG_DDL_NO_TABLESPACE))
+ {
+ if (is_with_defaults ||
+ dbform->dattablespace != DEFAULTTABLESPACE_OID)
+ {
+ char *dbTablespace = get_tablespace_name(dbform->dattablespace);
+
+ get_formatted_string(&optbuf, pretty_flags, 8, "TABLESPACE = %s",
+ quote_identifier(dbTablespace));
+ }
+ }
+
+ if (is_with_defaults || !dbform->datallowconn)
+ {
+ get_formatted_string(&optbuf, pretty_flags, 8, "ALLOW_CONNECTIONS = %s",
+ dbform->datallowconn ? "true" : "false");
+ }
+
+ if (is_with_defaults ||
+ dbform->datconnlimit != DATCONNLIMIT_UNLIMITED)
+ {
+ get_formatted_string(&optbuf, pretty_flags, 8, "CONNECTION LIMIT = %d",
+ dbform->datconnlimit);
+ }
+
+ if (is_with_defaults || dbform->datistemplate)
+ get_formatted_string(&optbuf, pretty_flags, 8, "IS_TEMPLATE = %s",
+ dbform->datistemplate ? "true" : "false");
+
+ /* Build the CREATE DATABASE statement */
+ appendStringInfo(&buf, "CREATE DATABASE %s",
+ quote_identifier(dbform->datname.data));
+
+ /* Only emit WITH if there are options */
+ if (optbuf.len > 0)
+ {
+ get_formatted_string(&buf, pretty_flags, 4, "WITH");
+ appendStringInfoString(&buf, optbuf.data);
+ }
+
+ pfree(optbuf.data);
+ appendStringInfoChar(&buf, ';');
+
+ ReleaseSysCache(tuple_database);
+
+ return buf.data;
+}
diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build
index fb8294d7e4a..f9893b5dfb0 100644
--- a/src/backend/utils/adt/meson.build
+++ b/src/backend/utils/adt/meson.build
@@ -98,6 +98,7 @@ backend_sources += files(
'regproc.c',
'ri_triggers.c',
'rowtypes.c',
+ 'ddlutils.c',
'ruleutils.c',
'selfuncs.c',
'skipsupport.c',
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index f16f1535785..997c671aef0 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -57,6 +57,7 @@
#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/fmgroids.h"
@@ -547,7 +548,6 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
deparse_context *context,
bool showimplicit,
bool needcomma);
-
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 361e2cfffeb..1e2f7d3ac35 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4034,6 +4034,13 @@
proname => 'pg_get_function_sqlbody', provolatile => 's',
prorettype => 'text', proargtypes => 'oid',
prosrc => 'pg_get_function_sqlbody' },
+{ oid => '9492', descr => 'get CREATE statement for database name and oid',
+ proname => 'pg_get_database_ddl', provariadic => 'any', proisstrict => 'f',
+ provolatile => 's', prorettype => 'text',
+ proargtypes => 'regdatabase any',
+ proargmodes => '{i,v}',
+ proallargtypes => '{regdatabase,any}',
+ prosrc => 'pg_get_database_ddl' },
{ oid => '1686', descr => 'list of SQL keywords',
proname => 'pg_get_keywords', procost => '10', prorows => '500',
diff --git a/src/test/regress/expected/database.out b/src/test/regress/expected/database.out
index 6b879b0f62a..76eca22cf3a 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -1,3 +1,65 @@
+--
+-- Reconstruct DDL
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- functions removes collation and locale related details.
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+ cleaned_ddl TEXT;
+BEGIN
+ -- Remove %LOCALE_PROVIDER% placeholders
+ cleaned_ddl := regexp_replace(
+ ddl_input,
+ '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)',
+ '',
+ 'gi'
+ );
+
+ -- Remove LC_COLLATE assignments
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove LC_CTYPE assignments
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove %LOCALE% placeholders
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove %COLLATION% placeholders
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove ENCODING assignments
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*ENCODING\s*=\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
CREATE DATABASE regression_tbd
ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
ALTER DATABASE regression_tbd RENAME TO regression_utf8;
@@ -16,6 +78,130 @@ CREATE ROLE regress_datdba_before;
CREATE ROLE regress_datdba_after;
ALTER DATABASE regression_utf8 OWNER TO regress_datdba_before;
REASSIGN OWNED BY regress_datdba_before TO regress_datdba_after;
+-- Test pg_get_database_ddl
+-- Database doesn't exists
+SELECT pg_get_database_ddl('regression_database');
+ERROR: database "regression_database" does not exist
+LINE 1: SELECT pg_get_database_ddl('regression_database');
+ ^
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+ pg_get_database_ddl
+---------------------
+
+(1 row)
+
+-- Without options
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+ ddl_filter
+-------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Owner
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', false));
+ ddl_filter
+--------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH CONNECTION LIMIT = 123;
+(1 row)
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults', true, 'tablespace', false));
+ ddl_filter
+----------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123 IS_TEMPLATE = false;
+(1 row)
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults', true));
+ ddl_filter
+----------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH OWNER = regress_datdba_after TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123 IS_TEMPLATE = false;
+(1 row)
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', false, 'tablespace', false, 'defaults', true));
+ ddl_filter
+-----------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123 IS_TEMPLATE = false;
+(1 row)
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ OWNER = regress_datdba_after
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With No Owner and No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true, 'owner', false, 'tablespace', false));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ CONNECTION LIMIT = 123;
+(1 row)
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true, 'defaults', true));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ OWNER = regress_datdba_after
+ TABLESPACE = pg_default
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123
+ IS_TEMPLATE = false;
+(1 row)
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true, 'owner', false, 'tablespace', false, 'defaults', true));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH
+ ALLOW_CONNECTIONS = true
+ CONNECTION LIMIT = 123
+ IS_TEMPLATE = false;
+(1 row)
+-- Test with text values: 'yes', 'no', '1', '0', 'on', 'off'
+\pset format aligned
+-- Using 'yes' and 'no'
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', 'no', 'defaults', 'yes'));
+ ddl_filter
+-----------------------------------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123 IS_TEMPLATE = false;
+(1 row)
+
+-- Using '1' and '0'
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', '0', 'tablespace', '0', 'defaults', '1'));
+ ddl_filter
+-----------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123 IS_TEMPLATE = false;
+(1 row)
+
+-- Using 'on' and 'off'
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', 'off', 'pretty', 'on'));
+ ddl_filter
+---------------------------------
+ CREATE DATABASE regression_utf8+
+ WITH +
+ CONNECTION LIMIT = 123;
+(1 row)
+
+-- Mixed boolean and text values
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', false, 'defaults', 'true', 'tablespace', 'no'));
+ ddl_filter
+-----------------------------------------------------------------------------------------------------------
+ CREATE DATABASE regression_utf8 WITH ALLOW_CONNECTIONS = true CONNECTION LIMIT = 123 IS_TEMPLATE = false;
+(1 row)
+
+-- Test duplicate option (should error)
+SELECT pg_get_database_ddl('regression_utf8', 'owner', false, 'owner', true);
+ERROR: option "owner" is specified more than once
+-- Test invalid text value (should error)
+SELECT pg_get_database_ddl('regression_utf8', 'owner', 'invalid');
+ERROR: value for option "owner" at position 2 has invalid value "invalid"
+HINT: Valid values are: true, false, yes, no, 1, 0, on, off.
DROP DATABASE regression_utf8;
+DROP FUNCTION ddl_filter(text);
DROP ROLE regress_datdba_before;
DROP ROLE regress_datdba_after;
diff --git a/src/test/regress/sql/database.sql b/src/test/regress/sql/database.sql
index 4ef36127291..9ef926fac9c 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -1,3 +1,67 @@
+--
+-- Reconstruct DDL
+--
+-- To produce stable regression test output, it's usually necessary to
+-- ignore collation and locale related details. This filter
+-- functions removes collation and locale related details.
+
+CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT)
+RETURNS TEXT AS $$
+DECLARE
+ cleaned_ddl TEXT;
+BEGIN
+ -- Remove %LOCALE_PROVIDER% placeholders
+ cleaned_ddl := regexp_replace(
+ ddl_input,
+ '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)',
+ '',
+ 'gi'
+ );
+
+ -- Remove LC_COLLATE assignments
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove LC_CTYPE assignments
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove %LOCALE% placeholders
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove %COLLATION% placeholders
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ -- Remove ENCODING assignments
+ cleaned_ddl := regexp_replace(
+ cleaned_ddl,
+ '\s*ENCODING\s*=\s*([''"])[^''"]*\1',
+ '',
+ 'gi'
+ );
+
+ RETURN cleaned_ddl;
+END;
+$$ LANGUAGE plpgsql;
+
CREATE DATABASE regression_tbd
ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0;
ALTER DATABASE regression_tbd RENAME TO regression_utf8;
@@ -19,6 +83,62 @@ CREATE ROLE regress_datdba_after;
ALTER DATABASE regression_utf8 OWNER TO regress_datdba_before;
REASSIGN OWNED BY regress_datdba_before TO regress_datdba_after;
+-- Test pg_get_database_ddl
+-- Database doesn't exists
+SELECT pg_get_database_ddl('regression_database');
+
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+
+-- Without options
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+
+-- With No Owner
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', false));
+
+-- With No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults', true, 'tablespace', false));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'defaults', true));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', false, 'tablespace', false, 'defaults', true));
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true));
+
+-- With No Owner and No Tablespace
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true, 'owner', false, 'tablespace', false));
+
+-- With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true, 'defaults', true));
+
+-- With No Owner, No Tablespace and With Defaults
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'pretty', true, 'owner', false, 'tablespace', false, 'defaults', true));
+
+-- Test with text values: 'yes', 'no', '1', '0', 'on', 'off'
+\pset format aligned
+-- Using 'yes' and 'no'
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', 'no', 'defaults', 'yes'));
+
+-- Using '1' and '0'
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', '0', 'tablespace', '0', 'defaults', '1'));
+
+-- Using 'on' and 'off'
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', 'off', 'pretty', 'on'));
+
+-- Mixed boolean and text values
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', 'owner', false, 'defaults', 'true', 'tablespace', 'no'));
+
+-- Test duplicate option (should error)
+SELECT pg_get_database_ddl('regression_utf8', 'owner', false, 'owner', true);
+
+-- Test invalid text value (should error)
+SELECT pg_get_database_ddl('regression_utf8', 'owner', 'invalid');
+
DROP DATABASE regression_utf8;
+DROP FUNCTION ddl_filter(text);
DROP ROLE regress_datdba_before;
DROP ROLE regress_datdba_after;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 3da19d41413..622d10541d0 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -602,6 +602,7 @@ CycleCtr
DBState
DbOidName
DCHCacheEntry
+DDLOptionDef
DEADLOCK_INFO
DECountItem
DH
--
2.51.0
