On 11/13/25 12:17 PM, Quan Zongliang wrote:
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.
The "dbOwner" is defined as a null pointer.
char *dbOwner = NULL;
Later, there might be a risk of it not being assigned a value.
if (OidIsValid(dbForm->datdba))
dbOwner = GetUserNameFromId(dbForm->datdba, false);
Although there is no problem in normal circumstances here. Many parts of
the existing code have not been checked either. Since this possibility
exists, it should be checked before using it. Just like the function
roles_is_member_of (acl.c).
if (dbOwner)
get_formatted_string(&buf, prettyFlags, 1, "OWNER = %s",
quote_identifier(dbOwner));
Regards,
Quan Zongliang
-----
Regards,
Akshay Joshi
EDB (EnterpriseDB)
