Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement

On Thu, Nov 20, 2025, at 6:18 AM, Akshay Joshi wrote:
>
>  Implemented in the suggested solution. Attached is the v5 patch for review.
>

I reviewed your patch and have some suggestions for this patch.

* You shouldn't include the property if the value is default. A long command
  adds nothing. Clarity? Tell someone that needs to select, copy and paste a
  long statement. It is a good goal to provide a short command to reconstruct
  the object. If you don't know why it didn't include CONNECTION LIMIT, it is
  time to check the manual again.

$ psql -AtqX -c "SELECT pg_get_database_ddl('postgres')" -d postgres
CREATE DATABASE postgres WITH OWNER = euler ENCODING = "UTF8" LC_COLLATE = "pt_BR.UTF-8" LC_CTYPE = "pt_BR.UTF-8" COLLATION_VERSION = "2.36" LOCALE_PROVIDER = 'libc' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT = -1;

* Use single quotes. The encoding, locale, lc_collate, lc_type,
  collation_version and some other properties should use single quotes. The
  locale_provider doesn't need a single quote because it is an enum. See how
  pg_dumpall constructs the command. Use simple_quote_literal.

$ pg_dumpall --binary-upgrade | grep 'p5'
-- Database "p5" dump
-- Name: p5; Type: DATABASE; Schema: -; Owner: euler
CREATE DATABASE p5 WITH TEMPLATE = template0 OID = 16392 STRATEGY = FILE_COPY ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8' COLLATION_VERSION = '2.36';
ALTER DATABASE p5 OWNER TO euler;
\connect p5

* OWNER. There is no guarantee that the owner exists in the cluster you will
  use this output. That's something that pg_dumpall treats separately (see
  above). Does it mean we should include the owner? No. We can make it an
  option.

* LOCALE. Why didn't you include it? I know there are some combinations that
  does not work together but this function can provide a minimal set of
  properties related to locale.

postgres=# CREATE DATABASE p6 LOCALE_PROVIDER builtin LOCALE 'C' TEMPLATE template0;
CREATE DATABASE

* STRATEGY. Although this is a runtime property, it should be an option.

* TEMPLATE. Ditto.

* options. Since I mentioned options for some properties (owner, strategy,
  template), these properties can be accommodated as a VARIADIC argument. The
  function signature can be something like

pg_get_database_ddl(oid, VARIADIC options text[])

I would include the pretty print into options too.

* Tabs. I don't think we use tabs to format output. Use spaces. A good practice
  is to use EXPLAIN style (2 spaces)and depending on the nesting, 4 spaces are
  fine too.

$ psql -AtqX -c "SELECT pg_get_database_ddl('postgres', true)" -d postgres
CREATE DATABASE postgres
	WITH OWNER = euler
		ENCODING = "UTF8"
		LC_COLLATE = "pt_BR.UTF-8"
		LC_CTYPE = "pt_BR.UTF-8"
		COLLATION_VERSION = "2.36"
		LOCALE_PROVIDER = 'libc'
		TABLESPACE = pg_default
		ALLOW_CONNECTIONS = true
		CONNECTION LIMIT = -1;

* permission. I don't think you need to check for permissions inside the
  function. I wouldn't want a different behavior than pg_dump(all). You can
  always adjust it in system_functions.sql.

* typo.

+--                                                                             
+-- Reconsturct DDL                                                             
+--

s/Reconsturct/Reconstruct/


-- 
Euler Taveira
EDB   https://www.enterprisedb.com/