public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: Quan Zongliang <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
Date: Thu, 13 Nov 2025 14:02:30 +0530
Message-ID: <CANxoLDfWexTBBVgSOabeh-z=-0BTQCEst_E037tej+DLJxtFqw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com>
<[email protected]>
<[email protected]>
On Thu, Nov 13, 2025 at 10:18 AM Quan Zongliang <[email protected]>
wrote:
>
>
> On 11/13/25 12:17 PM, Quan Zongliang wrote:
> >
> >
> > On 11/12/25 8:04 PM, Akshay Joshi wrote:
> >> Hi Hackers,
> >>
> >> I’m submitting a patch as part of the broader Retail DDL Functions
> >> project described by Andrew Dunstan https://www.postgresql.org/
> >> message- id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net
> >> <https:// www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-
> >> cb1e56f2e3e9%40dunslane.net>
> >>
> >> This patch 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. When the pretty flag is set to true, the function
> >> returns a neatly formatted, multi-line DDL statement instead of a
> >> single-line statement.
> >>
> >> *Usage examples:*
> >>
> >> 1) SELECT pg_get_database_ddl('test_get_database_ddl_builtin'); --
> >> *non-pretty formatted DDL*
> >> pg_get_database_ddl
> >>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> >> CREATE DATABASE test_get_database_ddl_builtin WITH OWNER =
> >> regress_ddl_database ENCODING = "UTF8" LC_COLLATE = "C" LC_CTYPE = "C"
> >> BUILTIN_LOCALE = "C.UTF-8" COLLATION_VERSION = "1" LOCALE_PROVIDER =
> >> 'builtin' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION
> >> LIMIT = -1;
> >>
> >>
> >> 2) SELECT pg_get_database_ddl('test_get_database_ddl_builtin', true);
> >> -- *pretty formatted DDL*
> >>
> >> CREATE DATABASE test_get_database_ddl_builtin
> >> WITH
> >> OWNER = regress_ddl_database
> >> ENCODING = "UTF8"
> >> LC_COLLATE = "C"
> >> LC_CTYPE = "C"
> >> BUILTIN_LOCALE = "C.UTF-8"
> >> COLLATION_VERSION = "1"
> >> LOCALE_PROVIDER = 'builtin'
> >> TABLESPACE = pg_default
> >> ALLOW_CONNECTIONS = true
> >> CONNECTION LIMIT = -1;
> >>
> >> 3) SELECT pg_get_database_ddl(16835); -- *non-pretty formatted
> >> DDL for OID*
> >> 4) SELECT pg_get_database_ddl(16835, true); -- *pretty formatted DDL
> >> for OID*
> >>
> >> The patch includes documentation, in-code comments, and regression
> >> tests, all of which pass successfully.
> >> *
> >> **Note:* To run the regression tests, particularly the pg_upgrade
> >> tests successfully, I had to add a helper function, ddl_filter (in
> >> database.sql), which removes locale and collation-related information
> >> from the pg_get_database_ddl output.
> >>
> > I think we should check the connection permissions here. Otherwise:
> >
> > postgres=> SELECT pg_database_size('testdb');
> > ERROR: permission denied for database testdb
> > postgres=> SELECT pg_get_database_ddl('testdb');
> >
> > pg_get_database_ddl
> >
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> > CREATE DATABASE testdb WITH OWNER = quanzl ENCODING = "UTF8"
> > LC_COLLATE = "zh_CN.UTF-8" LC_CTYPE = "zh_CN.UTF-8" LOCALE_PROVIDER =
> > 'libc' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT
> > = -1;
> > (1 row)
> >
> > Users without connection permissions should not generate DDL.
> >
>
> The "dbOwner" is defined as a null pointer.
> char *dbOwner = NULL;
>
> Later, there might be a risk of it not being assigned a value.
> if (OidIsValid(dbForm->datdba))
> dbOwner = GetUserNameFromId(dbForm->datdba, false);
>
> Although there is no problem in normal circumstances here. Many parts of
> the existing code have not been checked either. Since this possibility
> exists, it should be checked before using it. Just like the function
> roles_is_member_of (acl.c).
>
> if (dbOwner)
> get_formatted_string(&buf, prettyFlags, 1, "OWNER = %s",
> quote_identifier(dbOwner));
>
Fixed the given review comment. I've attached the v2 patch ready for
review.
>
> > Regards,
> > Quan Zongliang
> >
> >> -----
> >> Regards,
> >> Akshay Joshi
> >> EDB (EnterpriseDB)
> >>
> >>
> >>
> >
> >
>
>
Attachments:
[application/octet-stream] v2-0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch (18.3K, 3-v2-0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From 25bd4ed143c5dae77c85ade087dfee10356bd562 Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Wed, 24 Sep 2025 17:47:59 +0530
Subject: [PATCH v2] 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 | 70 ++++++++
src/backend/catalog/system_functions.sql | 12 ++
src/backend/utils/adt/ruleutils.c | 206 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +
src/test/regress/expected/database.out | 76 +++++++++
src/test/regress/sql/database.sql | 62 +++++++
6 files changed, 432 insertions(+)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..88fe04e649d 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,74 @@ 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>
+ Reconstructs the <command>CREATE DATABASE</command> statement from the
+ system catalogs for a specified database name. The result is a
+ comprehensive <command>CREATE DATABASE</command> statement.
+ </para></entry>
+ </row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_database_ddl</primary>
+ </indexterm>
+ <function>pg_get_database_ddl</function>
+ ( <parameter>database_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 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..2b622bcc66d 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"
@@ -94,6 +95,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 +551,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 noOfTabChars,
+ 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 +13753,199 @@ 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).
+ * noOfTabChars - indent with specified no of tabs.
+ * fmt - printf-style format string used by appendStringInfoVA.
+ */
+static void
+get_formatted_string(StringInfo buf, int prettyFlags, int noOfTabChars, const char *fmt,...)
+{
+ va_list args;
+
+ if (prettyFlags & PRETTYFLAG_INDENT)
+ {
+ appendStringInfoChar(buf, '\n');
+ /* Indent with tabs */
+ for (int i = 0; i < noOfTabChars; 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.
+ *
+ * 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_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;
+
+ /* 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 %d 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");
+ if (dbOwner)
+ get_formatted_string(&buf, prettyFlags, 1, "OWNER = %s",
+ quote_identifier(dbOwner));
+
+ if (dbForm->encoding != 0)
+ get_formatted_string(&buf, prettyFlags, 1, "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, 1, "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, 1, "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, 1, "BUILTIN_LOCALE = %s",
+ quote_identifier(TextDatumGetCString(dbValue)));
+ else if (!attrIsNull && dbForm->datlocprovider == COLLPROVIDER_ICU)
+ get_formatted_string(&buf, prettyFlags, 1, "ICU_LOCALE = %s",
+ quote_identifier(TextDatumGetCString(dbValue)));
+
+ /* Fetch the value of ICU_RULES */
+ dbValue = SysCacheGetAttr(DATABASEOID, tupleDatabase,
+ Anum_pg_database_daticurules, &attrIsNull);
+ if (!attrIsNull)
+ get_formatted_string(&buf, prettyFlags, 1, "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, 1, "COLLATION_VERSION = %s",
+ quote_identifier(TextDatumGetCString(dbValue)));
+
+ /* Set the appropriate LOCALE_PROVIDER */
+ if (dbForm->datlocprovider == COLLPROVIDER_BUILTIN)
+ get_formatted_string(&buf, prettyFlags, 1, "LOCALE_PROVIDER = 'builtin'");
+ else if (dbForm->datlocprovider == COLLPROVIDER_ICU)
+ get_formatted_string(&buf, prettyFlags, 1, "LOCALE_PROVIDER = 'icu'");
+ else
+ get_formatted_string(&buf, prettyFlags, 1, "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, 1, "TABLESPACE = %s",
+ quote_identifier(dbTablespace));
+ }
+
+ get_formatted_string(&buf, prettyFlags, 1, "ALLOW_CONNECTIONS = %s",
+ dbForm->datallowconn ? "true" : "false");
+
+ if (dbForm->datconnlimit != 0)
+ get_formatted_string(&buf, prettyFlags, 1, "CONNECTION LIMIT = %d",
+ dbForm->datconnlimit);
+
+ if (dbForm->datistemplate)
+ get_formatted_string(&buf, prettyFlags, 1, "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..df90fded42c 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,36 @@ 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]
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
In-Reply-To: <CANxoLDfWexTBBVgSOabeh-z=-0BTQCEst_E037tej+DLJxtFqw@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