5

I have a lot of functions and stored procedures in my PostgreSQL database that is dependent on each other. I want to run a script that will compile those function fist which is independent and not referring any other functions. Then I want to compile next level functions and so on until I reach top level function. SQL Server has sys.sql_expression_dependencies table which keeps track of referencing objects and referenced objects? Do we have anything like that in Postgres? if not how to achieve it.

Partha
  • 402
  • 4
  • 15
Ritu
  • 51
  • 4
  • 1
    While not listing the dependencies, [`check_function_bodies(false)`](https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-CHECK-FUNCTION-BODIES) allows creating the functions in any order – JGH Jun 05 '18 at 12:18

2 Answers2

1

What is reason? PostgreSQL PL/pgSQL functions has not compile (better validation) time dependency. There is runtime dependency only. Currently there is not a tool for this purpose, what I know. But some dependency can be taken from PL profiler https://bitbucket.org/openscg/plprofiler.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • About "reason?".... *Funcion-dependency* is an important library manager parameter. When I change a version of a function in a big library is important to check if exists other functions that will be impacted or need some change also. – Peter Krauss Dec 06 '18 at 23:00
  • How to list the depency chain of each function of my library using [PL Profiler Extension](https://bitbucket.org/openscg/plprofiler)? – Peter Krauss Dec 06 '18 at 23:03
  • 2
    @PeterKrauss - I checked source code of PLProfiler - it collects runtime dependency, and you can process their data from `pl_profiler_saved_callgraph` table by some recursive query. I wrote plpgsql_check https://github.com/okbob/plpgsql_check where is `plpgsql_show_dependency_tb function`, that can show static dependency. – Pavel Stehule Dec 07 '18 at 04:11
  • @PavelStehule great work with this `plpgsql_check`. Do you think it is possible to extend it so it will list which columns of the referenced object (table or view or function) are used? – Roman Pekar May 31 '19 at 15:56
  • 1
    @RomanPekar - I think so it's possible, but I have not a idea, how difficult task it is. Probably you have to iterate over plan and collect it. When I think about it, probably it is possible on tables and functions, but it is not easy. I am skeptically about views. – Pavel Stehule May 31 '19 at 17:24
  • @PavelStehule thanks. I work with Sql Server where I have some dependency engine which allow me to track dependencies between objects, using system sql server views/functions. It's not fully reliable though, but there're no way to write your own mechanism for that. I'd like to try this in Postgresql and it's nice that one can write such an extension – Roman Pekar Jun 05 '19 at 06:55
1

You can try this query:

select t.*, f.* from
(
    SELECT  table_name,table_schema,
            table_schema||'.'||table_name full_name,
            table_type 
      FROM information_schema.tables
     WHERE table_schema not in ('information_schema') and table_schema not like 'pg%'

) t
JOIN 
(
    SELECT n.nspname AS schema_name,
           p.proname AS function_name,
           pg_get_function_arguments(p.oid) AS args,
           pg_get_functiondef(p.oid) AS func_def
    FROM   pg_proc p
    JOIN   pg_namespace n ON n.oid = p.pronamespace
    WHERE  n.nspname not in ('pg_catalog','information_schema') and    n.nspname not like 'pg%' 
) f 
on position(t.table_name in f.func_def) >0
Darko Margetic
  • 21
  • 3