Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux