Find Procedure/Function Dependencies

Question

I want to find where all the stored procedure or function is getting used in database like in other procedures, functions, views etc.

In MSSQL we used:

DECLARE
    @sp nvarchar(100)
SET @sp = N'proc_name'

SELECT dm.*,o.type_desc
FROM sys.dm_sql_referencing_entities(@sp, 'OBJECT') dm
left join sys.objects o on dm.referencing_entity_name= o.name;

Looking for similar fashion query in the PostgreSQL.

By searching found pg_depend but didn't understand how to get similar output as shown above in MSSQL query.

score 0 · Answer 1 · answered Feb 22 '23 at 18:16

Using this SQL query you can view all functions or procedures names, return types, input parameters and etc.

SELECT 
    n.nspname::varchar as "schema_name",
    p.proname::varchar as "function_name",
    pg_catalog.pg_get_function_result(p.oid) as "return_type",
    pg_catalog.pg_get_function_arguments(p.oid) as "input_params", 
    pg_catalog.pg_get_functiondef(p.oid) as "function_body"
FROM pg_catalog.pg_proc p
LEFT JOIN 
    pg_catalog.pg_namespace n ON n.oid = p.pronamespace
where n.nspname = 'public'

If you need gets only all functions which using inside the another functions, you can join this SQL query with itself and search for it in the BODY section with like as where function_body LIKE '%' || function_name || '%'

Find Procedure/Function Dependencies

1 Answers1