5

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 '16 at 14:25
  • And by functions you mean user-defined functions, or PL/pqSQL functions or all functions? – Erwin Brandstetter Dec 21 '16 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 '16 at 7:54
3

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, privacy policy and cookie policy

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