public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: Chao Li <[email protected]>
Cc: Álvaro Herrera <[email protected]>
Cc: Japin Li <[email protected]>
Cc: Quan Zongliang <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
Date: Tue, 18 Nov 2025 13:33:10 +0530
Message-ID: <CANxoLDdPqyTLyYhBnekFYkaSn_8DBdtf32cH=MLNJc=+BQP=MQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com>
<[email protected]>
<[email protected]>
<CANxoLDfWexTBBVgSOabeh-z=-0BTQCEst_E037tej+DLJxtFqw@mail.gmail.com>
<SY0P300MB153948953051E054E8F69487B6CAA@SY0P300MB1539.AUSP300.PROD.OUTLOOK.COM>
<CANxoLDdTfsmmx6vMcZfdvW+5GSUjE9Fs5O4Jwiy3aGHiV+Vz8g@mail.gmail.com>
<[email protected]>
Hi Chao
Thanks for reviewing my patch.
On Tue, Nov 18, 2025 at 5:59 AM Chao Li <[email protected]> wrote:
> Hi Akshay,
>
> I just reviewed v3 and got some comments:
>
> > On Nov 17, 2025, at 22:34, Akshay Joshi <[email protected]>
> wrote:
> >
> > All the review comments have been addressed in v3 patch.
>
>
> 1 - ruleutils.c
> ```
> + if (dbForm->datconnlimit != 0)
> + get_formatted_string(&buf, prettyFlags, 1, "CONNECTION
> LIMIT = %d",
> +
> dbForm->datconnlimit);
> ```
>
> I think this is wrong. Default value of CONNECTION_LIMIT is -1 rather than
> 0. 0 means no connection is allowed, users should intentionally set the
> value, thus 0 should be printed.
>
> 2 - ruleutils.c
> ```
> + if (!attrIsNull)
> + get_formatted_string(&buf, prettyFlags, 1, "ICU_RULES =
> %s",
> +
> quote_identifier(TextDatumGetCString(dbValue)));
> ```
>
> ICU_RULES should be omitted if provider is not icu.
>
> 3 - ruleutils.c
> ```
> + if (!HeapTupleIsValid(tupleDatabase))
> + ereport(ERROR,
> + errcode(ERRCODE_UNDEFINED_OBJECT),
> + errmsg("database with oid %d does not
> exist", dbOid));
> ```
>
> I believe all existing code use %u to format oid. I ever raised the same
> comment to the other get_xxx_ddl patch.
>
Fixed all above in the attached v4 patch.
>
> 4 - ruleutils.c
> ```
> + /*
> + * User must have connect privilege for target database.
> + */
> + aclresult = object_aclcheck(DatabaseRelationId, dbOid, GetUserId(),
> +
> ACL_CONNECT);
> + if (aclresult != ACLCHECK_OK)
> + {
> + aclcheck_error(aclresult, OBJECT_DATABASE,
> + get_database_name(dbOid));
> + }
> ```
>
> I don’t think CONNECT privilege is good enough. By default, a new user
> gets CONNECT privilege via the PUBLIC role. I just did a quick test to
> confirm that.
>
> ```
> # Create a new cluster
> % initdb .
> % pg_ctl -D . start
> % createdb evantest
> % createdb evan
>
> # connect to the db
> % psql -d evantest -U evan
> psql (19devel)
> Type "help" for help. # Got into the database successfully
>
> # Without any privilege grant, the user can get ddl of the system
> database, which seems not good
> evantest=> select pg_get_database_ddl('postgres', true);
> pg_get_database_ddl
> ------------------------------------
> CREATE DATABASE postgres +
> WITH +
> OWNER = chaol +
> ENCODING = "UTF8" +
> LC_COLLATE = "en_US.UTF-8"+
> LC_CTYPE = "en_US.UTF-8" +
> LOCALE_PROVIDER = 'libc' +
> TABLESPACE = pg_default +
> ALLOW_CONNECTIONS = true +
> CONNECTION LIMIT = -1;
> (1 row)
> ```
>
> IMO, only super user and database owner should be to get ddl of the
> database.
>
I wasn't entirely sure, but after reviewing the *pg_database_size*()
function, I've concluded that its usage extends beyond the Superuser and
Database Owner. Specifically, other roles can view the database size if
they have the *CONNECT* privilege or are Members of the *pg_read_all_stats*
role.
>
> 5 - as you can see from the above test output, “+” in the end of every
> line is weird.
>
The plus sign (+) is merely an artifact of *psql's* output formatting when
a result cell contains a newline character (\n). It serves as a visual cue
to the user that the data continues on the next line. This is confirmed by
the absence of the + sign when viewing the same data in a different client,
such as *pgAdmin*." To suppress this visual cue in psql, you can use the
command: \pset format unaligned
>
> 6 - “WITH” has the same indent as the parameters, which doesn’t good look.
> If we look at the doc
> https://www.postgresql.org/docs/18/sql-createdatabase.html, “WITH” takes
> the first level of indent, and parameters take the second level of indent.
>
Fixed in the v4 patch and followed the docs.
>
> 7 - For those parameters that have default values should be omitted. For
> example:
> ```
> evantest=> select pg_get_database_ddl('evantest', true);
> pg_get_database_ddl
> ------------------------------------
> CREATE DATABASE evantest +
> WITH +
> OWNER = chaol +
> ENCODING = "UTF8" +
> LC_COLLATE = "en_US.UTF-8"+
> LC_CTYPE = "en_US.UTF-8" +
> LOCALE_PROVIDER = 'libc' +
> TABLESPACE = pg_default +
> ALLOW_CONNECTIONS = true +
> CONNECTION LIMIT = -1;
> (1 row)
> ```
>
> I created the database “evantest” without providing any parameter. I think
> at least OWNER, TABLESPACE and ALLOW_CNONNECTIONS should be omitted. For
> CONNECTION LIMIT, you already have a logic to omit it but the logic has
> some problem as comment 1.
>
IMHO, parameters with default values *should not* be omitted from the
output of the pg_get_xxx_ddl functions. The primary purpose of these
functions is to accurately reconstruct the DDL. Including all parameters
ensures clarity, as not everyone is familiar with the default value of
every single parameter.
>
> 8 - ruleutils.c
> ```
> +/*
> + * get_formatted_string
> + *
> + * Return a formatted version of the string.
> + *
> + * prettyFlags - Based on prettyFlags the output includes tabs (\t) and
> + * newlines (\n).
> + * nTabChars - indent with specified number of tab characters.
> + * fmt - printf-style format string used by appendStringInfoVA.
> + */
> +static void
> +get_formatted_string(StringInfo buf, int prettyFlags, int nTabChars,
> const char *fmt,...)
> ```
>
> I don’t feel good with this function, but not because of the
> implementation of the function.
>
> I have reviewed a bunch of get_xxx_ddl patches submitted by different
> persons. All of them are under a big project, however, looks like to me
> that all authors work independently without properly coordinated. A
> function like this one should be common for all those patches. Maybe Alvaro
> can help here, pushing a common function that is used to format DDL and
> requesting all patches to use the function.
>
Yes, all pg_get_xxx_ddl functions are part of a larger project, and
different authors have implemented output formatting in different ways;
some implementations may lack formatting altogether. Yes I agree one common
function should be developed and committed so that all other authors can
reuse it, ensuring consistency across the entire suite of DDL functions."
>
> Best regards,
> --
> Chao Li (Evan)
> HighGo Software Co., Ltd.
> https://www.highgo.com/
>
>
>
>
>
Attachments:
[application/octet-stream] v4-0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch (18.5K, 3-v4-0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From 664f14d030232607da1c644fbb808731f8648a2d Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Wed, 24 Sep 2025 17:47:59 +0530
Subject: [PATCH v4] 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, pretty),
which reconstructs the CREATE DATABASE statement for a given database name or database oid.
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', true); // pretty-formatted DDL
SELECT pg_get_database_ddl(16835, true); // pretty-formatted DDL
Reference: PG-150
Author: Akshay Joshi <[email protected]>
Reviewed-by: Álvaro Herrera <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 63 +++++++
src/backend/catalog/system_functions.sql | 12 ++
src/backend/utils/adt/ruleutils.c | 217 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +
src/test/regress/expected/database.out | 75 ++++++++
src/test/regress/sql/database.sql | 62 +++++++
6 files changed, 435 insertions(+)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..a918a1694c4 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,67 @@ 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_name</parameter> <type>name</type>, <optional> <parameter>pretty</parameter> <type>boolean</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para role="func_signature">
+ <indexterm>
+ <primary>pg_get_database_ddl</primary>
+ </indexterm>
+ <function>pg_get_database_ddl</function>
+ ( <parameter>database_oid</parameter> <type>oid</type>, <optional> <parameter>pretty</parameter> <type>boolean</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE DATABASE</command> statement from the
+ system catalogs for a specified database name or database oid. The
+ result is a comprehensive <command>CREATE DATABASE</command> statement.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ Most of the functions that reconstruct (decompile) database objects have an
+ optional <parameter>pretty</parameter> flag, which if
+ <literal>true</literal> causes the result to be
+ <quote>pretty-printed</quote>. Pretty-printing adds tab character and new
+ line character for legibility. Passing <literal>false</literal> for the
+ <parameter>pretty</parameter> parameter yields the same result as omitting
+ the parameter.
+ </para>
+
+ </sect2>
+
</sect1>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..2db9d3bbcfc 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -657,6 +657,18 @@ LANGUAGE INTERNAL
STRICT VOLATILE PARALLEL UNSAFE
AS 'pg_replication_origin_session_setup';
+CREATE OR REPLACE FUNCTION
+ pg_get_database_ddl(database_name name, pretty bool DEFAULT false)
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl_name';
+
+CREATE OR REPLACE FUNCTION
+ pg_get_database_ddl(database_oid oid, pretty bool DEFAULT false)
+RETURNS text
+LANGUAGE internal
+AS 'pg_get_database_ddl_oid';
+
--
-- 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 556ab057e5a..b39723b0855 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,6 +58,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"
@@ -94,6 +96,10 @@
((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 +552,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 nTabChars,
+ const char *fmt,...) pg_attribute_printf(4, 5);
+static char *pg_get_database_ddl_worker(Oid dbOid, int prettyFlags);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -13743,3 +13754,209 @@ 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 tabs (\t) and
+ * newlines (\n).
+ * nTabChars - indent with specified number of tab characters.
+ * fmt - printf-style format string used by appendStringInfoVA.
+ */
+static void
+get_formatted_string(StringInfo buf, int prettyFlags, int nTabChars, const char *fmt,...)
+{
+ va_list args;
+
+ if (prettyFlags & PRETTYFLAG_INDENT)
+ {
+ appendStringInfoChar(buf, '\n');
+ /* Indent with tabs */
+ for (int i = 0; i < nTabChars; i++)
+ {
+ appendStringInfoChar(buf, '\t');
+ }
+ }
+ else
+ appendStringInfoChar(buf, ' ');
+
+ va_start(args, fmt);
+ appendStringInfoVA(buf, fmt, args);
+ va_end(args);
+}
+
+/*
+ * pg_get_database_ddl_name
+ *
+ * Generate a CREATE DATABASE statement for the specified database name.
+ *
+ * dbName - Name of the database for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */
+Datum
+pg_get_database_ddl_name(PG_FUNCTION_ARGS)
+{
+ Name dbName = PG_GETARG_NAME(0);
+ bool pretty = PG_GETARG_BOOL(1);
+ int prettyFlags;
+ char *res;
+
+ /* Get the database oid respective to the given database name */
+ Oid dbOid = get_database_oid(NameStr(*dbName), false);
+
+ prettyFlags = GET_DDL_PRETTY_FLAGS(pretty);
+ res = pg_get_database_ddl_worker(dbOid, prettyFlags);
+
+ if (res == NULL)
+ PG_RETURN_NULL();
+
+ PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+/*
+ * pg_get_database_ddl_oid
+ *
+ * Generate a CREATE DATABASE statement for the specified database oid.
+ *
+ * dbOid - OID of the database for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */
+Datum
+pg_get_database_ddl_oid(PG_FUNCTION_ARGS)
+{
+ Oid dbOid = PG_GETARG_OID(0);
+ bool pretty = PG_GETARG_BOOL(1);
+ int prettyFlags;
+ char *res;
+
+ prettyFlags = GET_DDL_PRETTY_FLAGS(pretty);
+ res = pg_get_database_ddl_worker(dbOid, prettyFlags);
+
+ if (res == NULL)
+ PG_RETURN_NULL();
+
+ PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+static char *
+pg_get_database_ddl_worker(Oid dbOid, int prettyFlags)
+{
+ char *dbOwner = NULL;
+ char *dbTablespace = NULL;
+ bool attrIsNull;
+ Datum dbValue;
+ HeapTuple tupleDatabase;
+ Form_pg_database dbForm;
+ StringInfoData buf;
+ AclResult aclresult;
+
+ /*
+ * User must have connect privilege for target database.
+ */
+ aclresult = object_aclcheck(DatabaseRelationId, dbOid, 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(dbOid));
+ }
+
+ /* Look up the database in pg_database */
+ tupleDatabase = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbOid));
+ if (!HeapTupleIsValid(tupleDatabase))
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("database with oid %u does not exist", dbOid));
+
+ dbForm = (Form_pg_database) GETSTRUCT(tupleDatabase);
+
+ 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, prettyFlags, 1, "WITH OWNER = %s",
+ quote_identifier(dbOwner));
+
+ if (dbForm->encoding != 0)
+ get_formatted_string(&buf, prettyFlags, 2, "ENCODING = %s",
+ quote_identifier(pg_encoding_to_char(dbForm->encoding)));
+
+ /* Fetch the value of LC_COLLATE */
+ dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+ Anum_pg_database_datcollate, &attrIsNull);
+ if (!attrIsNull)
+ get_formatted_string(&buf, prettyFlags, 2, "LC_COLLATE = %s",
+ quote_identifier(TextDatumGetCString(dbValue)));
+
+ /* Fetch the value of LC_CTYPE */
+ dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+ Anum_pg_database_datctype, &attrIsNull);
+ if (!attrIsNull)
+ get_formatted_string(&buf, prettyFlags, 2, "LC_CTYPE = %s",
+ quote_identifier(TextDatumGetCString(dbValue)));
+
+ /* Fetch the value of LOCALE */
+ dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+ Anum_pg_database_datlocale, &attrIsNull);
+ if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_BUILTIN)
+ get_formatted_string(&buf, prettyFlags, 2, "BUILTIN_LOCALE = %s",
+ quote_identifier(TextDatumGetCString(dbValue)));
+ else if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_ICU)
+ get_formatted_string(&buf, prettyFlags, 2, "ICU_LOCALE = %s",
+ quote_identifier(TextDatumGetCString(dbValue)));
+
+ /* Fetch the value of ICU_RULES */
+ dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+ Anum_pg_database_daticurules, &attrIsNull);
+ if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_ICU)
+ get_formatted_string(&buf, prettyFlags, 2, "ICU_RULES = %s",
+ quote_identifier(TextDatumGetCString(dbValue)));
+
+ /* Fetch the value of COLLATION_VERSION */
+ dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+ Anum_pg_database_datcollversion, &attrIsNull);
+ if (!attrIsNull)
+ get_formatted_string(&buf, prettyFlags, 2, "COLLATION_VERSION = %s",
+ quote_identifier(TextDatumGetCString(dbValue)));
+
+ /* Set the appropriate LOCALE_PROVIDER */
+ if (dbForm->datlocprovider == COLLPROVIDER_BUILTIN)
+ get_formatted_string(&buf, prettyFlags, 2, "LOCALE_PROVIDER = 'builtin'");
+ else if (dbForm->datlocprovider == COLLPROVIDER_ICU)
+ get_formatted_string(&buf, prettyFlags, 2, "LOCALE_PROVIDER = 'icu'");
+ else
+ get_formatted_string(&buf, prettyFlags, 2, "LOCALE_PROVIDER = 'libc'");
+
+ /* Get the tablespace name respective to the given tablespace oid */
+ if (OidIsValid(dbForm->dattablespace))
+ {
+ dbTablespace = get_tablespace_name(dbForm->dattablespace);
+ if (dbTablespace)
+ get_formatted_string(&buf, prettyFlags, 2, "TABLESPACE = %s",
+ quote_identifier(dbTablespace));
+ }
+
+ get_formatted_string(&buf, prettyFlags, 2, "ALLOW_CONNECTIONS = %s",
+ dbForm->datallowconn ? "true" : "false");
+
+ get_formatted_string(&buf, prettyFlags, 2, "CONNECTION LIMIT = %d",
+ dbForm->datconnlimit);
+
+ if (dbForm->datistemplate)
+ get_formatted_string(&buf, prettyFlags, 2, "IS_TEMPLATE = %s",
+ dbForm->datistemplate ? "true" : "false");
+
+ appendStringInfoChar(&buf, ';');
+
+ ReleaseSysCache(tupleDatabase);
+
+ return buf.data;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5cf9e12fcb9..27fbb71297f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4021,6 +4021,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',
+ proname => 'pg_get_database_ddl', prorettype => 'text',
+ proargtypes => 'name bool', prosrc => 'pg_get_database_ddl_name' },
+{ oid => '9493', descr => 'get CREATE statement for database oid',
+ proname => 'pg_get_database_ddl', prorettype => 'text',
+ proargtypes => 'oid bool', prosrc => 'pg_get_database_ddl_oid' },
{ 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..fa0d1b01e67 100644
--- a/src/test/regress/expected/database.out
+++ b/src/test/regress/expected/database.out
@@ -1,3 +1,49 @@
+--
+-- Reconsturct 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 LC_COLLATE assignments
+ cleaned_ddl := regexp_replace(
+ ddl_input,
+ '\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 +62,35 @@ 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', false);
+ERROR: database "regression_database" does not exist
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+ pg_get_database_ddl
+---------------------
+
+(1 row)
+
+-- Without pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+ 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 Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', true));
+ddl_filter
+CREATE DATABASE regression_utf8
+ WITH OWNER = regress_datdba_after
+ ENCODING = "UTF8"
+ TABLESPACE = pg_default
+ 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..392a4d96bb5 100644
--- a/src/test/regress/sql/database.sql
+++ b/src/test/regress/sql/database.sql
@@ -1,3 +1,51 @@
+--
+-- Reconsturct 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 LC_COLLATE assignments
+ cleaned_ddl := regexp_replace(
+ ddl_input,
+ '\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 +67,20 @@ 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', false);
+
+-- Test NULL value
+SELECT pg_get_database_ddl(NULL);
+
+-- Without pretty
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8'));
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT ddl_filter(pg_get_database_ddl('regression_utf8', true));
+
DROP DATABASE regression_utf8;
DROP ROLE regress_datdba_before;
DROP ROLE regress_datdba_after;
--
2.51.0
view thread (38+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
In-Reply-To: <CANxoLDdPqyTLyYhBnekFYkaSn_8DBdtf32cH=MLNJc=+BQP=MQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox