Thread

[PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement

Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-11-12T12:04:44Z

Hi Hackers,

I’m submitting a patch as part of the broader Retail DDL Functions project
described by Andrew Dunstan
https://www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net

This patch adds a new system function
pg_get_database_ddl(database_name/database_oid, pretty), which reconstructs
the CREATE DATABASE statement for a given database name or database oid.
When the pretty flag is set to true, the function returns a neatly
formatted, multi-line DDL statement instead of a single-line statement.

*Usage examples:*

1) SELECT pg_get_database_ddl('test_get_database_ddl_builtin');  -- *non-pretty
formatted DDL*

                                                        pg_get_database_ddl


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  CREATE DATABASE test_get_database_ddl_builtin WITH OWNER =
regress_ddl_database ENCODING = "UTF8" LC_COLLATE = "C" LC_CTYPE = "C"
BUILTIN_LOCALE = "C.UTF-8" COLLATION_VERSION = "1" LOCALE_PROVIDER =
'builtin' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT
= -1;


2) SELECT pg_get_database_ddl('test_get_database_ddl_builtin', true);
 -- *pretty
formatted DDL*

CREATE DATABASE test_get_database_ddl_builtin
         WITH
         OWNER = regress_ddl_database
         ENCODING = "UTF8"
         LC_COLLATE = "C"
         LC_CTYPE = "C"
         BUILTIN_LOCALE = "C.UTF-8"
         COLLATION_VERSION = "1"
         LOCALE_PROVIDER = 'builtin'
         TABLESPACE = pg_default
         ALLOW_CONNECTIONS = true
         CONNECTION LIMIT = -1;

3) SELECT pg_get_database_ddl(16835);      -- *non-pretty formatted DDL for
OID*
4) SELECT pg_get_database_ddl(16835, true);  -- *pretty formatted DDL for
OID*

The patch includes documentation, in-code comments, and regression tests,
all of which pass successfully.

*Note:* To run the regression tests, particularly the pg_upgrade tests
successfully, I had to add a helper function, ddl_filter (in database.sql),
which removes locale and collation-related information from the
pg_get_database_ddl output.

-----
Regards,
Akshay Joshi
EDB (EnterpriseDB)