Get list of functions used in a view

Question

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.)

Answer 1

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:

SELECT r.ev_class::regclass AS view, d.refobjid::regprocedure AS function
FROM   pg_rewrite r
JOIN   pg_depend  d ON d.objid = r.oid 
                   AND d.refclassid = 'pg_proc'::regclass  -- only functions
WHERE  r.ev_class = 'v123'::regclass;  -- name of view here (optionally schema-qualified)

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 just regproc) returns the function with argument types, uniquely identifying it. The name is schema-qualified automatically if the current search_path would not resolve differently otherwise.