Re: 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 |
+------------------------+