CODEWITHSUNDEEP
sqlpostgresql
user6129679
user6129679

Reputation: 51

Is it possible to find dependency between PostgreSQL functions?

I'm using PostgreSQL 9.2.10

Suppose you have 2 PostgreSQL functions, 'called_function' and 'caller_function', second one is calling the first one. Exactly:

CREATE FUNCTION called_function () RETURNS varchar AS
$BODY$
BEGIN
  RETURN 'something';
END;

CREATE FUNCTION caller_function () RETURNS varchar AS
$BODY$
BEGIN
  RETURN called_function ();
END;

Now, using SQL and knowing only function name, I would like to find out if 'caller_function' calls some other function. Or if 'called_function' is called by some other function. Is it possible?

I tried to get function's OID (let's say it is '123') and then I looked into pg_depend table:

SELECT * FROM pg_catalog.pg_depend dep WHERE dep.objid = 123 OR dep.objsubid = 123 OR dep.refobjid = 123 OR dep.refobjsubid = 123 OR dep.refobjsubid = 123;

But it finds only pg_language and pg_namespace dependency. Nothing more.

Upvotes: 5

Views: 4189

Answers (3)

Vainstein K
Vainstein K

Reputation: 202

Impossible in the general case; but a limited (restricted-domain) solution is perfectly doable --- and might prove adequate for your needs.

(The Most Obvious of the Many) Limitations

  • Fails (false negative) if name of callee (a function to be invoked) is specified as a quoted identifier.
  • Fails (false negative) if name of callee is passed as argument.
  • Fails (false negative) if name of callee is read from a relation at runtime.
  • Fails (false negative) if name of callee is assembled from tokens.
  • Fails (false positive) if name of callee is present just as literal.
  • Fails (false positive) if name of callee is present in a multi-line comment.
  • Does not account for function overloading.
  • Does not account for functions invoked via triggers.
  • Does not account for functions invoked per query-rewrite rules.
  • Does not account for effects of query rewriting rules.
  • Knows nothing about functions written in non-interpreted PLs like C.

Sample Output

Your routine... | ...calls these routines:
---------------------------------+-------------------------------------------------
create_silo_indexes | {get_config__f_l__ea_silo,subst_silo_id}
demux__id_creat_thread | {}
grow__sensor_thhourly | {containing_hhour_t_begin}

SQL

WITH routine_names AS (
    SELECT DISTINCT(Lower(proname)) AS name                              --#0
    FROM pg_proc
    WHERE proowner = To_Regrole(current_role)
)
SELECT
    name AS "Your routine...",
    Array_Remove(                                                        --#8
        Array(                                                           --#7
            SELECT Unnest(                                               --#5
                String_To_Array(                                         --#4
                    Regexp_Replace(                                      --#3
                        Regexp_Replace(                                  --#2
                            Lower(PG_Get_Functiondef(To_Regproc(name)))  --#1
                            , '--.*?\n', '', 'g'
                        )
                        , '\W+', ' ', 'g'
                    )
                    , ' '
                )
            )
            INTERSECT                                                    --#6
            SELECT name FROM routine_names
            ORDER BY 1
        )
        , name
    ) AS "...calls these routines:"
FROM
    routine_names;

How It Works

  • #0 Collect names of all the routines which could be callers and/or callees. We cannot handle overloaded functions correctly anyway, so just DISTINCT to save trouble later on; SQL is case-insensitive apart from quoted identifiers which we are not bothering with anyway, so we just Lower() to simplify comparison later.
  • #1 PG_Get_Functiondef() fetches complete text of the CREATE FUNCTION or CREATE PROCEDURE command. Again, Lower().
  • #2 Strip single-line comments. Note the lazy (non-greedy) *? quantifier: the usual * quantifier, if used here, would remove the first single-line comment plus all subsequent lines!
  • #3 Replace all characters other than letters and digits and _, with a space. Note the + quantifier: it ensures that 2+ contiguous removed characters are replaced by just 1 space.
  • #4 Split by spaces into an array; this array contains bits of SQL syntax, literals, numbers, and identifiers including routine names.
  • #5 Unnest the array into a rowset.
  • #6 INTERSECT with routine names; result will consist of routine names only.
  • #7 Convert rowset into an array.
  • #8 Since input was complete text of a CREATE FUNCTION f ... command, extracted routine names will obviously contain f itself; so we remove it with Array_Remove().

(SQL tested with PostgreSQL 12.1)

Upvotes: 0

vatandoost
vatandoost

Reputation: 3737

I had same problem to define a function and because of dependency It didn't work. Then I solved my problem with adding this command before the commands

SET check_function_bodies = false;

hope to help someone else

Upvotes: 1

Rémy Baron
Rémy Baron

Reputation: 1399

Look at the table pg_proc for example :

select nspname,proname,prosrc from pg_proc join  pg_namespace nsp on (pronamespace=nsp.oid) where prosrc like '%called_function%'

Upvotes: 0

Related Questions