public inbox for [email protected]  

help / color / mirror / Atom feed

From: Quan Zongliang <[email protected]>
To: Akshay Joshi <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
Date: Thu, 13 Nov 2025 12:17:35 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com>
References: <CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com>



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)
> 
> 
> 

view thread (38+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox