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

  • 1
    maybe this can help: wiki.postgresql.org/wiki/Pg_depend_display
    – McNets
    Dec 21, 2016 at 14:25
  • And by functions you mean user-defined functions, or PL/pqSQL functions or all functions? Dec 21, 2016 at 18:38
  • Well, the most important part are user-defined functions (SQL or PL/pgSQL), but it's possible get get all function usages and filter them afterwards.
    – thomjah
    Dec 22, 2016 at 7:54

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.