Re: [PATCH] Add pg_get_subscription_ddl() function

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 Thu, Nov 6, 2025 at 7:26 PM Vaibhav Dalvi <vaibhav(dot)dalvi(at)enterprisedb(dot)com>
wrote:

> 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(dot)dalvi(at)enterprisedb(dot)com> wrote:
>
>> Hi Hackers,
>>
>> I am submitting a patch as a part of a larger Retail DDL functions
>> project described by Andrew Dunstan here
>> <https://www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net>
>> . 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
>>
>>