[PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement - Mailing list pgsql-hackers

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)