public inbox for [email protected]help / color / mirror / Atom feed
Re: [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]> 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-14 15:48 Akshay Joshi <[email protected]> parent: Andrew Dunstan <[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-16 07:23 Akshay Joshi <[email protected]> parent: 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-20 11:36 Akshay Joshi <[email protected]> parent: 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-21 09:31 Akshay Joshi <[email protected]> parent: Akshay Joshi <[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-31 00:27 Mark Wong <[email protected]> parent: Akshay Joshi <[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