Thread: About to know the info of foreign table reference used in any of call ,views,functions

Hi

On 2021-04-27 09:15, Durgamahesh Manne wrote:
> Hi Team
> 
> By using the system view and function
> 
> "I need to get the info of foreign table reference used in any of call
> ,views,functions"
> 
> I found info of views and functions and sprocs that are executed
> frequently through application using pg_stat_user_functions view
> 
> Please help  for the info i need
> 
> Thanks & Regards
> Durgamahesh Manne

I am not sure how reliable that is, but as you mention you could query 
the system catalogs.

The definition of views and the code of functions are stored in pg_views 
and pg_proc.
So you can check if those contain the name of the tables you are 
interested in.

You can find a list of the foreign tables using:

SELECT relnamespace::regnamespace AS schema_name,
        relname AS table_name
FROM pg_class
WHERE relkind = 'f';

You can then use this information to query views defintions and function 
bodies:

SELECT schemaname,
        viewname
FROM pg_views
WHERE definition ~ '(schema_name\.)?table_name';

SELECT pronamespace::regnamespace,
        proname
FROM pg_proc
WHERE prosrc ~ '(schema_name\.)?table_name';

schema_name and table_name refer to the result of the first query.

If you have overloaded functions you may need to extract more 
information to identify them correctly, such as the list of parameters. 
The documentation is very helpful in this context.

Hope this helps.

Regards
Charles

-- 
Charles Clavadetscher
Spitzackerstrasse 9
CH - 8057 Zürich

https://www.swisspug.org

+------------------------+
|   ____  ______  ___    |
|  /    )/      \/   \   |
| (     / __    _\    )  |
|  \    (/ o)  ( o)   )  |
|   \_  (_  )   \ ) _/   |
|     \  /\_/    \)/     |
|      \/ <//|  |\\>     |
|           _|  |        |
|           \|_/         |
|                        |
|    Swiss PostgreSQL    |
|       Users Group      |
+------------------------+