The semantics of the "depends on extension" subprogram attribute

Installing an extension typically brings several subprograms. For example, installing the tablefunc extension brings the normal_rand() function. If, later, you drop the extension, then all the subprograms that it brought are silently dropped as a consequence. You don't need to say cascade to bring this outcome. Occasionally, users enlarge an extension's functionality with their own subprograms. They consider that these, too, are part of the extension and they want, therefore, to have these be silently dropped if the extension is dropped. This is what "depends on extension" achieves.

You can test this easily, if you have a sandbox cluster, by creating a brand-new database and installing, say, the tablefunc extension into it, specifying, for example, "with schema extensions". (You need to do this as a superuser.) Then create a test function, say f(), and a test procedure, say p() in the same database and schema and use "alter... depends on extension..." to make these "part of" tablefunc. Use this query to observe this outcome:

select
  p.proname::text,
  e.extname
from
  pg_catalog.pg_proc p
  inner join
  pg_catalog.pg_depend d
  on p.oid = d.objid
  inner join
  pg_catalog.pg_extension e
  on d.refobjid = e.oid
where p.pronamespace::regnamespace::text = 'extensions'
order by 1, 2, 3;

You'll see this:

   proname   |  extname
-------------+-----------
 connectby   | tablefunc
 connectby   | tablefunc
 connectby   | tablefunc
 connectby   | tablefunc
 crosstab    | tablefunc
 crosstab    | tablefunc
 crosstab    | tablefunc
 crosstab2   | tablefunc
 crosstab3   | tablefunc
 crosstab4   | tablefunc
 f           | tablefunc
 normal_rand | tablefunc
 p           | tablefunc

Then do this:

drop extension tablefunc restrict;

the restrict keyword is used to emphasize the pedagogy. Usually, it prevents the attempt to drop an object when the object has dependent objects. But dropping an extension is a special case. And "alter ... depends on extension..." recruits your user-defined subprograms into the special-case regime. You can confirm the outcome by attempting to execute f() or p(). You'll get the 42883 (undefined_function) error.

0A000: This statement not supported yet
Please report the issue on https://github.com/YugaByte/yugabyte-db/issues