From: Japin Li <japinli@hotmail.com>
To: Akshay Joshi <akshay.joshi@enterprisedb.com>
Cc: Euler Taveira <euler@eulerto.com>,  =?utf-8?Q?=C3=81lvaro?= Herrera
 <alvherre@kurilemu.de>,  Amul Sul <sulamul@gmail.com>,  Andrew Dunstan
 <andrew@dunslane.net>,  Chao Li <li.evan.chao@gmail.com>,  Quan Zongliang
 <quanzongliang@yeah.net>,  pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct
 CREATE DATABASE statement
In-Reply-To: 
 <CANxoLDfR-8nOYetoSKiJ9yVvMkXodVRqR+e=7CSEwiJTrU7yrA@mail.gmail.com>
	(Akshay Joshi's message of "Fri, 27 Feb 2026 17:52:36 +0530")
References: <202602251144.gu4luvkj6e2c@alvherre.pgsql>
	<0fbd1982-4bc3-4c10-aa66-9446af7dc48a@app.fastmail.com>
	<CANxoLDfR-8nOYetoSKiJ9yVvMkXodVRqR+e=7CSEwiJTrU7yrA@mail.gmail.com>
User-Agent: mu4e 1.12.12; emacs 29.3
Date: Wed, 04 Mar 2026 16:39:06 +0800
Message-ID: 
 <SY7PR01MB10921EA37563D7A65CFF0D771B67CA@SY7PR01MB10921.ausprd01.prod.outlook.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: SY7PR01MB10921:EE_|SY7PR01MB10570:EE_
X-MS-Office365-Filtering-Correlation-Id: 127c0ab2-7f14-4ce0-4d89-08de79c9821c
X-Microsoft-Antispam: 
	BCL:0;ARA:14566002|8060799015|19110799012|23021999003|12121999013|15080799012|461199028|11091999009|41001999006|6090799003|5072599009|40105399003|53005399003|440099028|3412199025;
X-Microsoft-Antispam-Message-Info: 
	=?utf-8?B?ZDRZMmdwbXRMTksvS2FwMWhCUTRjUlliVXpXUDhQTmVwNnZ2akYrUnVZSEp2?=
 =?utf-8?B?ZEJBTnphOHZGaGRSUko4ZFFDQnhTZWJqbUZhRmlUWnBKdVV1bGU3Nm4zelp6?=
 =?utf-8?B?YUwzRGJxbHhaNkgrS0pteVVEc09jZUFWQmE3TDVnUnh5M3QrREhtczNZTThW?=
 =?utf-8?B?aEFEZHg3TzVBdHB2ZkZWdHhtakQxRCtsNFBlTk1vQndsbFpSbFlFZVVhS2o5?=
 =?utf-8?B?ZWh0L04zODg2am1HUXp2bXRjVG1HUXZkcnhJeER3OWZhZmRnVnNPQ3ZIbU1x?=
 =?utf-8?B?eVQ5MDdlQVZtZkNMM2owVWVsY202UVNaZitOYXRSQmhyVjFNRHNvUUkrYVVy?=
 =?utf-8?B?dDF5OGp5cFU5dU9hTCtBNGhPQ0Qzak50ak94MWw1YXZUcnFjcG1tSUI5ak9P?=
 =?utf-8?B?UmhudXpVaWxYeFZHWHNsdHYrelFaa2FIUkVDSUlyM2hwWlQ5LzUwZ2FyL051?=
 =?utf-8?B?K0pEdStSb1FHUUJGeFI1K1ordEpkaE5GSzNGb0pDeXB4bFpnUjdOSk1DblZp?=
 =?utf-8?B?ejBQZ2ZLWkpEYnpzSXBSTjlvNmI4WUd5aTZEcTAvRVhkSW9Ca1lsNWdBZGIw?=
 =?utf-8?B?Rzg2c09VVHlPODh4T1JJL0tjcU1sZnJuSmJPQVBBKzBVSHQ4NVdUQmRsZXdH?=
 =?utf-8?B?RjZCSzU4ZWFjcXEwazNhbDFDek1hVk9TNTl2SjFnOEdNUW85eUltRVV5SG9y?=
 =?utf-8?B?dXRvNzhjdDNEZncvaXMrREV0UWFoR0RQRjJJUWwwdDMrZTl4bDZmZTcyYUph?=
 =?utf-8?B?dVlOUkt5d3ltaVNmVkJ1NzZxbVQ3YW85cGZrdi9mVXNuK2hlVnhiNjFDVjhS?=
 =?utf-8?B?c0s0SmxaUnJsamx1bHc3bzVYTU9RK2psSE41R3pmejNsQS9Jbm52NXp2Nkkr?=
 =?utf-8?B?cldGdGhhemZCTkhDVEZlZFpPdHgrSzZKdS9RRSt3R0RMeCswcEhWbUw0UmZG?=
 =?utf-8?B?YlV5S1Z1Q1VKSHo5QTRWNmFpWmFSMHdGZ2pCMjdNcW44d3dvUXcrS2l0djhS?=
 =?utf-8?B?N3lhMkFYMFhlUzdrR0tnNE9JM2ZmcmZlUG12cWE3M213UVRKZHIwNGtNUDhh?=
 =?utf-8?B?Z29YcTFoRUFjZHZoeVprcHJ0VlBuU0k3aFU4b1dCNEtnZlB1LzNaQllLdm1X?=
 =?utf-8?B?WVk0MGwrZEhsNHdLQU5ERkUvemtHNkY3UVFOY3R3VWg3SVN3UlBUSkwySmpQ?=
 =?utf-8?B?Yy9vL2NEOXJUbkdDMzhJTjY5cDJTVnl5eFRtVzFUZkZlajBzNzlZbnprUkNh?=
 =?utf-8?B?bFhQQzdWNC90eXk4UWhuWTJhK2x3MXVzVFRwNmp5cForbU96MlV4R0tvRVlu?=
 =?utf-8?B?TGIyeUVGcEhHdHRGa1ZFQUM3Y1pFcmNYNVA2cUFhNWkwR2ZTZFBnMXd6SDll?=
 =?utf-8?B?OXVLL2xmNS9jZ0E9PQ==?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?utf-8?B?NkZEMVcxdmVQYjNKWGhIRmVydUtGY2pVbFd6QXJXdUZVYmtFSlV4aXVyR3NO?=
 =?utf-8?B?MFRoVHNUU0Y0bXJVeEhlWlJMMjlkYTBBOHcyMEtoanpWZlRYZkdFZU5vUzM2?=
 =?utf-8?B?NXkwVUZoMVYvNmVEbVhOZzd3QVUrTHVSUzA4QS9PaTJQdS85RjNZbEljdFBX?=
 =?utf-8?B?cXMxQjRTVXRtZFhJa1M0b3ZySk9QYWFrd3JCTzNwV29YbFp0QkM3MUNiS2pX?=
 =?utf-8?B?U0c3aE4vL1N0em1tS2g1UStDSXZweHpoWTluR2pNRytaMGFSdGJIQ1hBdnBp?=
 =?utf-8?B?enhtK3lncFhreEJ0Rk5zQ0dxSnoreUNaWEtqV0VNN1JzVTNPbzFFYUlHVVBk?=
 =?utf-8?B?SThVWDBtWFpkL2o2eS9QSGk0ODd3U25GcjVNNVVlUnVBUHNQanpzV05pTjc1?=
 =?utf-8?B?VVAxTkRaQ0pMYUtsSDNoYzAwcjIybHBkNmZuZXhzQlNoRml5czdtSE1JVnV0?=
 =?utf-8?B?aTN3NThOTVVORzE3UEV4d3ZQeGN0a2FFekJ6cTFlUUlRSDhpYmFCYnRvMkFs?=
 =?utf-8?B?UXpwNTVESFRlb25CYnBxNzkzV0JDc29LTzFVYUR6TTZ3Nm5PbEY1N21vQUhR?=
 =?utf-8?B?V3g2Y294amQyQ1JaVGxFNEhrRjlEL2ZBMU83WGdETWU1c0NqSE9JNE83ZXVP?=
 =?utf-8?B?Q2Y5Wng5WWt4VXBkV3JtSG9lWVVPcG43Q3ZyRHZSNVVHbVZMUXVLK1pHRGVE?=
 =?utf-8?B?R01pRlcvcXoxc2dhdTZ4My9GcFRUWmd1R2VWaUhqWkZJREx3L215VFRpRXMy?=
 =?utf-8?B?OG9nQjJncUtVT05WRWVlSk5GNUdwY1ZtSUpyQVRWeDVVWjFOYlMxMkZjUmc3?=
 =?utf-8?B?cGZDVW83Lys1ZVRqL0tubXdDVXNZczVsMTloMExDWnBSZzdNUWxqNkt6eUxY?=
 =?utf-8?B?dW1GY3JYeDJFdjF3TEdXZjJ1OEI3VFhsbkNjdmhIazd0ZStYUTBsdWRnMjNL?=
 =?utf-8?B?OSt5Nzl3MnZRWXFwc0g1Nk9zRXRWMXNYdHArcFR0S3dJUEZaOXcvNE5rb09x?=
 =?utf-8?B?QTNjdFZLQitrckczWFVoYTYvT09EWUFUSE0wVnVER3d4eXZUaS91cEJUY1Ja?=
 =?utf-8?B?eVBsY1ZnbWFWNWZxQzYvTlU5Y2lZMTFMQ2VIVHZoMlFTcmZpdTdKMlRpWGVK?=
 =?utf-8?B?dEZ3dllHd3FiNjhObW0zVkF0djN4aTRINExtd1VDZTBtRnJjcmtSZXF6YUx1?=
 =?utf-8?B?YzVETXpiQzJIYU9DRlhzTG5xZTJnaWlmQnQ5aG1jcWN4WFVHT2lNcUNGUmhk?=
 =?utf-8?B?Q05yU0tuUkIxSGIyZjdqdDhaUm1DSW84RFpFL2k5MGlqYmFmVEpoM0xqNFY3?=
 =?utf-8?B?MGx4ZThBcXU1RXc3TFlOTHRUMXh6bDRzZVR5T2lENitUVHB2WHRRQlR0QWhB?=
 =?utf-8?B?Q0ZET29mYzcwYzJVUTlxVnNNZ29mNjc4MXZRdXFZS3dyTDZ0ejVMREVJWkRk?=
 =?utf-8?B?eTRncjhRVHpGRk0xRGJIdmdyNUZZVEZkMXl0NzNFNkxCa2sybUZ0UGlPVE41?=
 =?utf-8?B?OFpGb3c4YUxMcWxBdGtMakVBMkoybzZCSDU1RGc5eTJ3NmRsZjc1cExjdm0w?=
 =?utf-8?B?WlI3QkxGWUdoUGhXRmFxaDJtclE4cko5SGhWckpIT3BNVDZ0T2paUmNORHZj?=
 =?utf-8?B?QW84MjBlZnE4Z2toRkZ4WlIvakZ5UXJFWFdzUHNiUk9tKzJCRk81L0Y0TXAr?=
 =?utf-8?B?UlpoTllYa1llcE1wZ0NyOGxxZ1ZlbWhjVHNIV0J6WFVvZmxBc1lxaWR3R0Uv?=
 =?utf-8?B?VzJxemRMNUNkSHZhTGRyV3pOeStDd1VBZUpiWGRCRWIxenZRN012QWE1NkpL?=
 =?utf-8?B?L2xvWWxEbys5YkRIYlFEQVJqbFZ2ZmZaUUFPa01CMERYb2ZzZ05HakkvOHpD?=
 =?utf-8?Q?VGmS46c75EEmM?=
X-OriginatorOrg: sct-15-20-9412-4-msonline-outlook-feddd.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 127c0ab2-7f14-4ce0-4d89-08de79c9821c
X-MS-Exchange-CrossTenant-AuthSource: SY7PR01MB10921.ausprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 04 Mar 2026 08:39:11.4043
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
	00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: SY7PR01MB10570
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/SY7PR01MB10921EA37563D7A65CFF0D771B67CA%40SY7PR01MB10921.ausprd01.prod.outlook.com>
Precedence: bulk

On Fri, 27 Feb 2026 at 17:52, Akshay Joshi <akshay.joshi@enterprisedb.com> =
wrote:
> Following suggestions from Alvaro and Mark, I have re-implemented this fe=
ature using variadic
> function argument pairs.
> This patch incorporates Mark Wong=E2=80=99s documentation updates, Amul=
=E2=80=99s one review comment, and the
> majority of Euler=E2=80=99s comments.
>
> New changes:
> - Supports flexible DDL options as key-value pairs:
>     - pretty - Format output for readability
>     - owner - Include OWNER clause
>     - tablespace - Include TABLESPACE clause
>     - defaults - Include parameters even if it is set to default value.
> - Option values accept: 'yes'/'on'/true/'1' (or their negatives)
>
> Usage Examples:
>   -- Basic usage with options
>   SELECT pg_get_database_ddl('mydb', 'owner', 'yes', 'defaults', 'yes');
>   -- Pretty-printed output without owner and tablespace
>   SELECT pg_get_database_ddl('mydb', 'owner', 'no', 'tablespace', 'no', '=
pretty', 'on');
>   -- Using boolean values
>   SELECT pg_get_database_ddl('mydb', 'owner', false, 'defaults', true);
>   -- Using OID
>   SELECT pg_get_database_ddl(16384, 'pretty', 'yes');
>
> Note: To keep things clean, I=E2=80=99ve moved the logic into two generic=
 functions (get_formatted_string
> and parse_ddl_options) and a common DDLOptionDef struct. This should simp=
lify the work for the
> rest of the pg_get_<object>_ddl patches.
>
> Attached is the v9 patch which is ready for review.
>
> On Thu, Feb 26, 2026 at 2:49=E2=80=AFAM Euler Taveira <euler@eulerto.com>=
 wrote:
>
>  On Wed, Feb 25, 2026, at 8:53 AM, =C3=81lvaro Herrera wrote:
>  >
>  > I'm surprised to not have seen an update on this topic following the
>  > discovery by Mark Wong that commit d32d1463995c (in branch 18) already
>  > established a convention for passing arguments to functions: use argum=
ent
>  > pairs to variadic functions, the way pg_restore_relation_stats() and
>  > pg_restore_attribute_stats() work.  While I like my previous suggestio=
n
>  > of using DefElems better, I think it's more sensible to follow this
>  > established precedent and not innovate on this.
>  >
>
>  This convention is much older than the referred commit. It predates from=
 the
>  logical decoding (commit b89e151054a0). See pg_logical_slot_get_changes_=
guts()
>  that is an internal function for pg_logical_slot_FOO_changes().  It seem=
s a
>  good idea to have a central function to validate the variadic parameter =
for all
>  of these functions.
>

Thanks for updating the patch, here are some comments on v9.

1.
+	uint64		flag;			/* Flag to set */

Do we actually need 64 bits for this flag field?

2.
+		/* Indent with spaces */
+		for (int i =3D 0; i < nSpaces; i++)
+		{
+			appendStringInfoChar(buf, ' ');
+		}

How about using appendStringInfoSpaces(buf, nSpaces) instead?

3.
+	/* If no options provided (VARIADIC NULL), return the empty bitmask */
+	if (nargs < 0)
+		return flags;
+
+	...
+
+	/* No arguments provided */
+	if (nargs =3D=3D 0)
+		return flags;

These two conditions are identical =E2=80=94 how about just `if (nargs <=3D=
 0)`?

4.
+	/* Arguments must come in name/value pairs */
+	if (nargs % 2 !=3D 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("argument list must have even number of elements"),
+				 errhint("The arguments of %s must consist of alternating keys and val=
ues.",
+						 "pg_get_database_ddl()")));

Should we align this with stats_fill_fcinfo_from_arg_pairs()?

Suggested wording:
    errmsg("variadic arguments must be name/value pairs")
   =20
5.
+		/* Key must not be null */
+		if (nulls[i])
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("argument %d: key must not be null", i + 1)));
+

Suggested wording:

    errmsg("name at variadic position %d is null")

6.
+		/* Key must be text type */
+		if (types[i] !=3D TEXTOID)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("argument %d: key must be text type", i + 1)));

Suggested wording:

    errmsg("name at variadic position %d has type %s, expected type %s")

7.
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("argument %d: value for key \"%s\" must be boolean or text ty=
pe",
+							i + 2, name)));

Suggested wording:

    errmsg("argument \"%s\" has type %s, execpted type boolean or text")

See stats_check_arg_type().

8.
+	for (i =3D 0; i < nargs; i +=3D 2)
+	{

We can narrow the scope of `i` by declaring it in the for initializer.

9.
+        {
+            bool        found =3D false;
+            int         j;
+
+            for (j =3D 0; j < lengthof(ddl_option_defs); j++)
+            {

Minor style improvements:

- We can (and should) declare `j` inside its for-loop initializer, just lik=
e `i`.
- Move the declaration of `found` up to the top of the outer for-loop scope=
. =20
  This allows us to remove the unnecessary braces around the loop body.

--=20
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.