Re: [PATCH] Add pg_get_subscription_ddl() function - Mailing list pgsql-hackers

Hi Hackers,

Please find the revised patch for the `pg_get_subscription_ddl()` function attached.

Based on feedback, this version of the function now supports calling the DDL retrieval

using either the subscription name or the OID, as shown in the examples below:

```sql

postgres=# SELECT pg_get_subscription_ddl('testsub1');

pg_get_subscription_ddl

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE SUBSCRIPTION testsub1 CONNECTION 'dbname=db_doesnotexist' PUBLICATION "testpub1" WITH (connect = false, slot_name = 'testsub1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);

(1 row)

postgres=# SELECT pg_get_subscription_ddl(16384);

pg_get_subscription_ddl

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE SUBSCRIPTION testsub1 CONNECTION 'dbname=db_doesnotexist' PUBLICATION "testpub1" WITH (connect = false, slot_name = 'testsub1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);

(1 row)

```

I request your review of the updated patch.

Regards,

Vaibhav Dalvi

EnterpriseDB

On Fri, Oct 31, 2025 at 4:27 PM Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com> wrote:

Hi Hackers,

I am submitting a patch as a part of a larger Retail DDL functions

project described by Andrew Dunstan here. This patch creates a

function pg_get_subscription_ddl, designed to retrieve the full DDL

statement for a subscription. Users can obtain the DDL by providing

the subscription name, like so:

  SELECT pg_get_subscription_ddl('testsub1');

                                                                                                                                                                                      pg_get_subscription_ddl                                                                                                                                                                                      

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 CREATE SUBSCRIPTION testsub1 CONNECTION 'dbname=db_doesnotexist' PUBLICATION "testpub1" WITH (connect = false, slot_name = 'testsub1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);

(1 row)

This patch includes documentation, comments, and regression tests.

Regards,

Vaibhav Dalvi

EnterpriseDB