6

In PostgreSQL (9.3) is there a simple way to get a list of the stored procedures that use a specific table?

I'm changing several tables and need to fix the stored procedures that use them.

klin
  • 112,967
  • 15
  • 204
  • 232
Alan Wayne
  • 5,122
  • 10
  • 52
  • 95
  • 1
    http://stackoverflow.com/questions/1559008/list-stored-functions-using-a-table-in-postgresql – Greg Jul 01 '15 at 16:05
  • @Greg Problem is, I need only the procedures using a specific table--not a list of all the procedures in the database ?? Do I need to read each procedure manually? – Alan Wayne Jul 01 '15 at 16:50
  • didn't know that, sorry.. – Greg Jul 01 '15 at 17:22

2 Answers2

7

Functions which have text 'thetable' in their body.

The query returns function name, line number and line containg 'thetable':

select *
from (
    select proname, row_number() over (partition by proname) as line, textline
    from (
        select proname, unnest(string_to_array(prosrc, chr(10))) textline
        from pg_proc p
        join pg_namespace n on n.oid = p.pronamespace
        where nspname = 'public'
        and prosrc ilike '%thetable%'
        ) lines
    ) x
    where textline ilike '%thetable%';

Functions which have any argument or return value of type associated with thetable.

For example:

create function f2(rec thetable)...
create function f1() returns setof thetable...

This query gives name, return type and types of arguments of the functions:

with rtype as (
    select reltype 
    from pg_class
    where relname = 'thetable')
select distinct on (proname) proname, prorettype, proargtypes
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
cross join rtype
where nspname = 'public'
and (
    prorettype = reltype 
    or reltype::text = any(string_to_array(proargtypes::text, ' ')))

Of course, you can merge the queries into one. I am using them for different purposes.

klin
  • 112,967
  • 15
  • 204
  • 232
3

This task is not simple, because PostgreSQL has not any evidence about dependencies between functions and tables. What I know, there are not any public tool, that does it. One did Skype, but I am not sure if this tool was released outside Skype. If you know C, then you can modify plpgsql_check, where this information is available, but it is not used.

There is poor solution - you can try to search specific string in source codes.

postgres=# CREATE OR REPLACE FUNCTION foo()
RETURNS int AS $$
BEGIN
  RETURN (SELECT a FROM t1);
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION

postgres=# SELECT oid::regprocedure FROM pg_proc WHERE prosrc ~ '\mt1\M';
┌───────┐
│  oid  │
╞═══════╡
│ foo() │
└───────┘
(1 row)

\m \M are Regular Expression Constraint see related docs.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • So I'm guessing a text dump to a file followed by a manual search on table name ?? – Alan Wayne Jul 01 '15 at 17:39
  • 1
    And to search the complete function definition including the header (where the table name could pop up as type name) use `pg_get_functiondef()` Details: http://stackoverflow.com/a/22191178/939860 (When used as row type in the header, there is a dependency on the type in the system, though.) – Erwin Brandstetter Jul 01 '15 at 17:59
  • @AlanWayne, no - you can search in table, where source code is stored. – Pavel Stehule Jul 01 '15 at 18:53