Re: pg_get__*_ddl consolidation

Andrew Dunstan <andrew@dunslane.net>

From: Andrew Dunstan <andrew@dunslane.net>

To: "David G. Johnston" <david.g.johnston@gmail.com>, Japin Li <japinli@hotmail.com>

Cc: Zsolt Parragi <zsolt.parragi@percona.com>, Euler Taveira <euler@eulerto.com>, Álvaro Herrera <alvherre@kurilemu.de>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

Date: 2026-04-02T16:27:21Z

Lists: pgsql-hackers

Attachments

v4-0001-Add-infrastructure-for-pg_get_-_ddl-functions.patch (text/x-patch) patch v4-0001
v4-0002-Add-pg_get_role_ddl-function.patch (text/x-patch) patch v4-0002
v4-0003-Add-pg_get_tablespace_ddl-function.patch (text/x-patch) patch v4-0003
v4-0004-Add-pg_get_database_ddl-function.patch (text/x-patch) patch v4-0004

On 2026-04-02 Th 9:35 AM, David G. Johnston wrote:
> On Thursday, April 2, 2026, Japin Li <japinli@hotmail.com> wrote:
>
>
>     v3-0004
>     ========
>
>     1.
>     +       append_ddl_option(&buf, pretty, 4, "WITH TEMPLATE =
>     template0");
>
>     I'm curious why WITH TEMPLATE = template0 is hardcoded. For example:
>
>       [local]:1374846 postgres=# create database db01 IS_TEMPLATE true;
>       CREATE DATABASE
>       [local]:1374846 postgres=# create database db02 template db01;
>       CREATE DATABASE
>       [local]:1374846 postgres=# select pg_get_database_ddl('db02');
>      pg_get_database_ddl
>      
>     -----------------------------------------------------------------------------------------------------------------
>        CREATE DATABASE db02 WITH TEMPLATE = template0 ENCODING =
>     'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';
>        ALTER DATABASE db02 OWNER TO japin;
>       (2 rows)
>
>     Is this working as expected?
>
>     It seems there's no way to reconstruct the WITH TEMPLATE clause,
>     right?
>     A comment here would help.
>
>
> There is no way or use in constructing the original template clause, 
> though I agree it’s worth a comment.  At the end of the day the 
> catalog data that was found in the db01 database already exists in the 
> db02 database when executing these DLL reconstruction functions 
> against the existing db02 database. Taking nothing from the template 
> is the correct behavior - hence template0.
>
>


OK, here's a v4.


cheers


andrew


--
Andrew Dunstan
EDB:https://www.enterprisedb.com

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Use named boolean parameters for pg_get_*_ddl option arguments
- d6ed87d19890 19 (unreleased) landed
Convert ddlutils regression tests to TAP tests.
- c529ee38b9eb 19 (unreleased) landed
Fix pfree crash in pg_get_role_ddl() and pg_get_database_ddl().
- 1f108fc02ece 19 (unreleased) landed
Fixups for a4f774cf1c7
- 6c7bce28c83f 19 (unreleased) landed
Add infrastructure for pg_get_*_ddl functions
- 4881981f9202 19 (unreleased) landed
Add pg_get_database_ddl() function
- a4f774cf1c7e 19 (unreleased) landed
Add pg_get_role_ddl() function
- 76e514ebb4b5 19 (unreleased) landed
Add pg_get_tablespace_ddl() function
- b99fd9fd7f36 19 (unreleased) landed