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: 
 <SY7PR01MB10921EA37563D7A65CFF0D771B67CA@SY7PR01MB10921.ausprd01.prod.outlook.com>
	(Japin Li's message of "Wed, 04 Mar 2026 16:39:06 +0800")
References: <202602251144.gu4luvkj6e2c@alvherre.pgsql>
	<0fbd1982-4bc3-4c10-aa66-9446af7dc48a@app.fastmail.com>
	<CANxoLDfR-8nOYetoSKiJ9yVvMkXodVRqR+e=7CSEwiJTrU7yrA@mail.gmail.com>
	<SY7PR01MB10921EA37563D7A65CFF0D771B67CA@SY7PR01MB10921.ausprd01.prod.outlook.com>
User-Agent: mu4e 1.12.12; emacs 29.3
Date: Wed, 04 Mar 2026 17:30:34 +0800
Message-ID: 
 <SY7PR01MB10921BB04879E2BDE787AA5EFB67CA@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_|MEUPR01MB9334:EE_
X-MS-Office365-Filtering-Correlation-Id: af4db3e8-ece2-4ceb-b0e9-08de79d0b2ba
X-Microsoft-Antispam: 
	BCL:0;ARA:14566002|5072599009|461199028|11091999009|15080799012|6090799003|41001999006|12121999013|19110799012|23021999003|8060799015|3412199025|440099028|40105399003|53005399003;
X-Microsoft-Antispam-Message-Info: 
	=?utf-8?B?R3IvS3d2ZWNiaTdCNHdDaGZsSFVwK2Z6a2RZT1VyVmlnTmU3OEUxYS9UVURv?=
 =?utf-8?B?UzljZU94NXRGNDJiVlpWYStnem5qYS9nbE5vQmNxWE92Wm80L3JsVVMycndq?=
 =?utf-8?B?TVo5QzRVaGp4SmdIOWRjcFdaY0djYTNwb1FjdjQ5dUV2MkJGaC9HNG1rT2hT?=
 =?utf-8?B?ZjdXUlBVMndGUE8yT040RkJGdUhBb3BabmwxeGRsaXJaaFBCNHNibUlEd2Ez?=
 =?utf-8?B?QnI2aHVFUm1Db2k2RkR2WWlBZmYzTEd6bC94WWJFSGNQejgxTnBxY1ZTNHpt?=
 =?utf-8?B?Tk9VRVgxSHlLa0RaMzVOTUJiSHBJK004SWFEbEZXUHBiQzdiY0cya3NXYjN6?=
 =?utf-8?B?TFoxMnBTWGl6d1VWQ2hwSm5QVXA3ZlRoSG1VNm9vb3NMY1pQYTMyNW5CcVps?=
 =?utf-8?B?NmlKbFZBYUJXaFhVb3hGUWtFRFFySnRXcmUwL1RuSFJtTGhBWkRIN0NtREw1?=
 =?utf-8?B?WGJacVdYSm5hN05nTktGdk1VZmVUNlpvQ1FBczBmY1JKTXU1c0lydlFSbUEv?=
 =?utf-8?B?UklUM1FFWXBSUlgxZDhpbGNQT0FtU3U4ckVLK3VydFhPblpXOWpNdlRweno0?=
 =?utf-8?B?WWpNS2ZSRm1BbVZaS2FxZmVjWjE4T3MxTXM5Zlc2MTFiaHlvNW11TXNLeXZC?=
 =?utf-8?B?OU1UV2xOVjhjZXlzd0lUSjl5bXVUVlBpVTY5eEdoampoUU9LOUJnNllETXdi?=
 =?utf-8?B?ZDVHbFdlTzlaRGNhb2dJVnoreDBNTTMyTWdzdXhJdmY1ZWFkYVRhS2NkSFk3?=
 =?utf-8?B?T1FBTEM0MEg5Q0lvYWFlcW1QRG9vRXdSdkRWa2lRQzM4emxCV0JDbURiNkdF?=
 =?utf-8?B?OU1YNDdoZHlVSjV4WUovbzhQemZpZWZ3N08xc2VEU2pYeEdiY09kTmk3TWtC?=
 =?utf-8?B?VUtqTU1xd0h2MVdJWGk0b2NQNUF4cjRFbjM5OUMvRm91NWVydkpPSnhlazZ3?=
 =?utf-8?B?S2plQisxcUg2K3pJQmRvck5XSkhwRGdUamNpQWlqTko1WmY5cTEwelhCOWpz?=
 =?utf-8?B?SjdNSDFrS1lieklsNGVlaWtaajN5eTM0VlFyWlpuOEMwV3JodHhyQkhaM09N?=
 =?utf-8?B?ajZGM3pHeG5LTXhJb3lLQU1JMkdQb2VKVmM4dWZ1RjFDN0p6UEZ5Q2toYUZN?=
 =?utf-8?B?QzNuM2R0bzlXVzVDWERIcHRtWE9uU1JFTG9yOTRVSHJWckEyVUJCNUFDdHBD?=
 =?utf-8?B?akNkdTR6Tm5FRldGSWdlZjNQRFg1QXl5V3E5T0ROd1VqYVJFMG0rbmRNUXl5?=
 =?utf-8?B?SE5NbW9lMHF5NU1kL0ZKaU1JaFZjZFVNWDVFd1cvYWd3UnNqMXR1akJPTzhB?=
 =?utf-8?B?SGgwaTF4eU44c2NxUkI3UElPNHU2M0RMR0FPdFp6WWozTG5sblRBRVZncnQ4?=
 =?utf-8?B?R3lscThmeGZTSGc9PQ==?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?utf-8?B?WjQyT0MyUGNvVFU4V21SN3NHMVdRcGpRSitPSjlHQnBRQVdQaUcwNnl4TUxr?=
 =?utf-8?B?YUlLT0xEYkxGQ1RWdCtCb29MVlJaU1pEZmVlck4zTE9jR1plZ3NjTy9mNlQr?=
 =?utf-8?B?djBTTnBGc1ZFaEQ3Z2piZ1k1eENVUlFRcUxib2xXa2JJL1I3MzVDcGwvK1NL?=
 =?utf-8?B?NmhiTVFCWmdTbEtmbmRyajUyQk5qdWxMbTY1T2I3Wi9JQTNMZTZoRnl4cU1C?=
 =?utf-8?B?RWcrSXRTellRM1h1bWlkTWNNUXptNWptcjM3WCtUendKeVVCWEMvL0htNWY5?=
 =?utf-8?B?UUZCV0ZMdThKdy8wQ1hlUnp4b21lM1lsbW9OakFpS2dVQ2Nqd3loMHgzZzEx?=
 =?utf-8?B?ZDRlbDRiM3JSUFhkK09GdUV0WlljdVZ2azBrOUlVdlhSbXcxVzBFV0FLN01P?=
 =?utf-8?B?Rk5rNHZHVlQ3bUZSMUlmTDVlY3FoSHZsSTk2YVBWcmFZR2hkVVZIWngwUCtv?=
 =?utf-8?B?WW5oeDlqSTUwaFFVQjF0cTJodE16WlZoRTE2b1k0cTF3OU9DekYyN0ltR1Jh?=
 =?utf-8?B?QzZ3NzdBWXFnZkpIL2Vkdm1Ua3pVUjRLTkFlTnhKbFFtVUR0Ly9VUi82YmVJ?=
 =?utf-8?B?UENla2tIYnk3Vm81eHhsZ1VhN1BjMW5pdEdjbXFPK2lON2JxMXUrTmRmRk4w?=
 =?utf-8?B?Q1M4RXB0U0tpaEN0QTFLV291NzNHcFhHZXloa01FY3pjaDlmVk5scGx5eHo0?=
 =?utf-8?B?UXc3VWxCV3FRclRXQWUwNVRPVk9La2lzRXptUmtmU0lRa3FONHhheTJ1QW5W?=
 =?utf-8?B?KzdqZlgyall5dUxydExpZjJmVmJlclpZR0VmTGp4TS9DYzhSNDFDQ3pjTjFh?=
 =?utf-8?B?TTdia0ZiRG5Qc2hQR0tFOWhMaTNWYlJHLzFLT2o5OHRVaEFwajVBaUhNWWJT?=
 =?utf-8?B?cEVyRjE2MWZyL2s4akhKcVJnTFY4Sk01eGxTd2laQVV1dCszdVMwTnVWeUpt?=
 =?utf-8?B?RHpOK2c4ejBPZkdXZEszZThJRlpBME9xYlh4Q3BpVzdZZGJnWS9IUXR5N1N2?=
 =?utf-8?B?cmNsZHpzQWNYSnF1NHJIMW00aW1uVWd2cmErYXZXaUJoaTJSOUlJNHpBTlFK?=
 =?utf-8?B?dDE4UnJNSkVJRnhWYVYybVZzd29tZEdUSHFSWDJQY3lpcTdITW5FeWR6N25P?=
 =?utf-8?B?SThodC9Kd2VIeEpZY1N3MkcvQitCYS94cElQZTFkYVJYTWRZdzF6c2RqQ1Yw?=
 =?utf-8?B?dXYwKy9BdFloTCtobHZIcGEvRmJjVmFWK0c0MlBDMGRTTFIzM01hQ3V3Z0RT?=
 =?utf-8?B?dmVEYzE2czB6c1d4QUpja1JhUnhxUTFvb1lBbmt6YnV6TVlVQlEvcExXdWVm?=
 =?utf-8?B?S0pqWTNvMm5MaVlLRFV2cngrdG5ESElKczMzblRNaDBMaXF6MS90L2RtekNY?=
 =?utf-8?B?RGMrM3ZtaVZOSEJVd3VzRkc4Ums0cXZOTjZNQktkZ0x0ZzFBUU1FYnErb1Qv?=
 =?utf-8?B?ZHUzTkRVK1Zyc04vMStlQTNoZkkyYkVKYTRUblFBcVpKNW9hdnM1bGNFS1lQ?=
 =?utf-8?B?ZER3YXhxQWxaMlYvZmhjM1VVQjB0QzV2aVhCeWRPRElSZGl0SlB3Z2UvWWlX?=
 =?utf-8?B?eFpCUm5KbWdUWkRGRHgxUmg2ekhBc0FObTA1SUlNZWtvN2NKSkg2RENidzNR?=
 =?utf-8?B?YU1DbDhBYkhSNXZBcHIxajJHa3IxZVluNnhBYmwvWkd3bDlHZkJFdUJRM2F3?=
 =?utf-8?B?ZWJRN09XZllnK0ZmZ0FTRTJSV1VqQ093VGY0VEJ4bXdQMmtEZnkxV2xDZVhW?=
 =?utf-8?B?TFZta0svTUNVT0Zwdm0zc2U0ZlF3QWE0SkY0a1hMNjJZTHNrNE1UbHZBQUpI?=
 =?utf-8?B?Mk9pdFRKUEllMVFxN2REZGkzRXhhZ1lBQUFlWU4zWDc3d0pzTzh6dmJ1eWpR?=
 =?utf-8?Q?PtBj9hVIOrvxR?=
X-OriginatorOrg: sct-15-20-9412-4-msonline-outlook-feddd.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 af4db3e8-ece2-4ceb-b0e9-08de79d0b2ba
X-MS-Exchange-CrossTenant-AuthSource: SY7PR01MB10921.ausprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 04 Mar 2026 09:30:39.3874
 (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: MEUPR01MB9334
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/SY7PR01MB10921BB04879E2BDE787AA5EFB67CA%40SY7PR01MB10921.ausprd01.prod.outlook.com>
Precedence: bulk

On Wed, 04 Mar 2026 at 16:39, Japin Li <japinli@hotmail.com> wrote:
> 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 f=
eature 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 generi=
c functions (get_formatted_string
>> and parse_ddl_options) and a common DDLOptionDef struct. This should sim=
plify 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) alread=
y
>>  > established a convention for passing arguments to functions: use argu=
ment
>>  > pairs to variadic functions, the way pg_restore_relation_stats() and
>>  > pg_restore_attribute_stats() work.  While I like my previous suggesti=
on
>>  > 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 fro=
m the
>>  logical decoding (commit b89e151054a0). See pg_logical_slot_get_changes=
_guts()
>>  that is an internal function for pg_logical_slot_FOO_changes().  It see=
ms 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 v=
alues.",
> +						 "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 =
type",
> +							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 l=
ike `i`.
> - Move the declaration of `found` up to the top of the outer for-loop sco=
pe. =20
>   This allows us to remove the unnecessary braces around the loop body.
>

After playing with this patch, I=E2=80=99m seeing the following output:

# select pg_get_database_ddl('postgres'::regdatabase, 'defaults', true, 'pr=
etty', true, 'pretty', false);
        pg_get_database_ddl
------------------------------------
 CREATE DATABASE postgres          +
     WITH                          +
         OWNER =3D japin             +
         ENCODING =3D 'UTF8'         +
         LC_COLLATE =3D 'en_US.UTF-8'+
         LC_CTYPE =3D 'en_US.UTF-8'  +
         COLLATION_VERSION =3D '2.39'+
         LOCALE_PROVIDER =3D libc    +
         TABLESPACE =3D pg_default   +
         ALLOW_CONNECTIONS =3D true  +
         CONNECTION LIMIT =3D -1;
(1 row)

Is this the expected behavior?

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