How to drop a subscription inside a stored procedure?

[Thomas Kellerer <shammat@xxxxxxx>]

I am trying to write a stored procedure (Postgres 13) to enable
non-superusers to re-create a subscription.

For that, I essentially want to drop and re-create the subscription.
In order to be able to do that, the tables need to be empty.

So the approach is:

   Run a query to get all replicated tables, store this in an array.

   Run "drop subscription ..."

   Truncate all tables

   Run "create subscription ..."

However, the "drop subscription" part results in this error:

    ERROR:  DROP SUBSCRIPTION cannot be executed from a function
    CONTEXT:  SQL statement "drop subscription if exists my_replication"

I first thought that the initial SELECT to fetch all replicated tables,
starts an implicit transaction, so I removed everything else from the procedure,
including the dynamic SQL.

But even this very simple implementation:

    create or replace procedure drop_subscription()
    as
    $$
    begin
      drop subscription if exists test_subscription;
    end;
    $$
    security definer
    language plpgsql;


fails with that error.

Is there any way, I can provide a stored procedure to do this?

Regards
Thomas