- 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 >> >>
v7-0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch
Description: Binary data
