Say I have a Function like this:
create function house_analysis(ingeo geometry)
returns table(count_all numeric, count_important numeric) as
$$
select count(*), count(*) filter (where h.import_flag)
from house_table h where st_intersects(ingeo, h.geom)
$$ language sql stable;
And I define a View like this:
create or replace view postzone_analysis as (
select p.zipcode, ha.count_all, ha.count_important
from postzone_table p, house_analysis(p.geom) ha
);
The question is:
How can I query the system catalogs (pg_catalog.*
) with my view (postzone_analysis
or its oid
) get a list of the functions used within it? Their pg_proc.oid
values are fine.
I know the database keeps track, since I cannot drop the function, but I can't find the entries in pg_depend
.
DB is PostgreSQL 9.5.
(The real-life case is a lot more complex – it's scoped down to be lowest viable example. The view calls like 6 analysis functions, which combines the data from difference sources, and there are multiple views that are based on different area classes.)
Best Answer
Views are implemented as table plus rewrite rule in Postgres. Details in the manual, chapter Views and the Rule System.
The tricky part: not the view itself depends on involved functions, only the rewrite rule does. So this query should be your solution:
Returns all user-defined functions (excluding built-in functions).
The query assumes you did not define any additional rules for the view. Else filter the one with
rulename = '_RETURN'
, which is the default name for the rewrite rules of a view.The cast to
regprocedure
(not justregproc
) returns the function with argument types, uniquely identifying it. The name is schema-qualified automatically if the currentsearch_path
would not resolve differently otherwise.