*Briefly*
qu. 1. What is the use-case for "alter function/procedure depends on extension"?
After all, subprograms depend, functionally, on all sorts of things. But
(unlike in Oracle Database) there’s (almost) no semantic check at “create” time
and therefore nothing like the semantics that ORCL’s "DBA_Dependencies"
exposes. Rather, if some semantic dependency parent doesn’t exist, then you
might get a run-time error—depending on whether the execution path tries to use
it. Moreover, on such an error, the persisted source code remains in place so
that if you create the missing dependency parent, then the dependant will then
just work. It would seem that this same run-time error model that was designed
for schema objects would be a good choice for extensions too. And you get this
model, anyway, by default. What is the argument for overriding the default
behavior by using "alter function/procedure"?
qu. 2. Why does "drop extension restrict" quietly succeed—having the effect of
cascade-dropping any subprogram that you've altered to depend on the extension
in question? This is at odds with the documented meaning of "restrict".
qu. 3. I established by experiment that you can repeat "alter
function/procedure depends on extension" time and again for the same subprogram
to make it depend on lots of extensions. And I checked that dropping any one of
the extensions nominated this way is enough to cascade-drop the dependent
subprogram. Why, then, is there no list syntax like this:
alter function s.f() depends on extension tablefunc, fuzzystrmatch;
Trying it causes the generic 42601 syntax error at the comma.
*More detail*
"Chapter 52. System Catalogs" gave me what I needed to list out what functions
depend on what extensions. But the approach that I used was straight
Codd-and-Date: an intersection table between the procedures table and the
extensions table.
qu. 4. Why the asymmetry of approach with the way that, for example, the many
execute-grantees for a procedure are represented as an array,
Stonebraker-object-relational-style, in the procedures table? (It's the same,
too, for the list of session parameter settings that "alter
function/procedure... set…" establishes. (Here, you _can_ set many different
parameters with a single "alter".)
Just for completeness, this query (after my setup):
select
p.proname::text,
p.pronamespace::regnamespace::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.proname::text in ('a', 'b', 'c') and
p.pronamespace::regnamespace::text = 's'
order by 1, 2, 3;
produced this result:
proname | pronamespace | extname
---------+--------------+---------------
a | s | tablefunc
b | s | fuzzystrmatch
c | s | fuzzystrmatch
c | s | tablefunc
just as I expected.
