On 11/12/25 8:04 PM, Akshay Joshi wrote:
Hi Hackers,
I’m submitting a patch as part of the broader Retail DDL Functions
project described by Andrew Dunstan https://www.postgresql.org/message-
id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net <https://
www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-
cb1e56f2e3e9%40dunslane.net>
This patch adds a new system function pg_get_database_ddl(database_name/
database_oid, pretty), which reconstructs the CREATE DATABASE statement
for a given database name or database oid. When the pretty flag is set
to true, the function returns a neatly formatted, multi-line DDL
statement instead of a single-line statement.
*Usage examples:*
1) SELECT pg_get_database_ddl('test_get_database_ddl_builtin'); --
*non-pretty formatted DDL*
pg_get_database_ddl
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE DATABASE test_get_database_ddl_builtin WITH OWNER =
regress_ddl_database ENCODING = "UTF8" LC_COLLATE = "C" LC_CTYPE = "C"
BUILTIN_LOCALE = "C.UTF-8" COLLATION_VERSION = "1" LOCALE_PROVIDER =
'builtin' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION
LIMIT = -1;
2) SELECT pg_get_database_ddl('test_get_database_ddl_builtin', true);
-- *pretty formatted DDL*
CREATE DATABASE test_get_database_ddl_builtin
WITH
OWNER = regress_ddl_database
ENCODING = "UTF8"
LC_COLLATE = "C"
LC_CTYPE = "C"
BUILTIN_LOCALE = "C.UTF-8"
COLLATION_VERSION = "1"
LOCALE_PROVIDER = 'builtin'
TABLESPACE = pg_default
ALLOW_CONNECTIONS = true
CONNECTION LIMIT = -1;
3) SELECT pg_get_database_ddl(16835); -- *non-pretty formatted DDL
for OID*
4) SELECT pg_get_database_ddl(16835, true); -- *pretty formatted DDL
for OID*
The patch includes documentation, in-code comments, and regression
tests, all of which pass successfully.
*
**Note:* To run the regression tests, particularly the pg_upgrade tests
successfully, I had to add a helper function, ddl_filter (in
database.sql), which removes locale and collation-related information
from the pg_get_database_ddl output.
I think we should check the connection permissions here. Otherwise:
postgres=> SELECT pg_database_size('testdb');
ERROR: permission denied for database testdb
postgres=> SELECT pg_get_database_ddl('testdb');
pg_get_database_ddl
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE DATABASE testdb WITH OWNER = quanzl ENCODING = "UTF8"
LC_COLLATE = "zh_CN.UTF-8" LC_CTYPE = "zh_CN.UTF-8" LOCALE_PROVIDER =
'libc' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT
= -1;
(1 row)
Users without connection permissions should not generate DDL.
Regards,
Quan Zongliang
-----
Regards,
Akshay Joshi
EDB (EnterpriseDB)
