Thread: List Functions and Code
Hi
I want to search and list all the functions in a database that reference a particular table within its code. Is there a way to do this?
I can list all the functions from pg_proc, however there is nothing there which provides the code of the function, so therefore I can't query if it mentions a table. I've tried looking in information_schema.routines but this unfortunately does not have it either.
Rebecca
On Thursday, July 28, 2011 6:31:32 am Rebecca Clarke wrote:
> Hi
>
> I want to search and list all the functions in a database that reference a
> particular table within its code. Is there a way to do this?
>
> I can list all the functions from pg_proc, however there is nothing there
> which provides the code of the function, so therefore I can't query if it
> mentions a table. I've tried looking in information_schema.routines but
> this unfortunately does not have it either.
>
> Rebecca
http://www.postgresql.org/docs/9.0/interactive/catalog-pg-proc.html
prosrc in pg_proc should, with the following caveat from the docs:
"For compiled functions, both built-in and dynamically loaded, prosrc contains the function's C-language name (link symbol). For all other currently-known language types, prosrc contains the function's source text. probin is unused except for dynamically-loaded C functions, for which it gives the name of the shared library file containing the function. "
--
Adrian Klaver
adrian.klaver@gmail.com
Hello
2011/7/28 Rebecca Clarke <rebecca@clarke.net.nz>:
> Hi
> I want to search and list all the functions in a database that reference a
> particular table within its code. Is there a way to do this?
> I can list all the functions from pg_proc, however there is nothing there
> which provides the code of the function, so therefore I can't query if it
> mentions a table. I've tried looking in information_schema.routines but this
> unfortunately does not have it either.
> Rebecca
>
>
from psql console you can use a \df command
[pavel@pavel-stehule ~]$ psql postgres
psql:/home/pavel/.psqlrc:4: \pset: unknown option: linestyle
Output format is wrapped.
psql (8.4.8, server 9.2devel)
WARNING: psql version 8.4, server version 9.2.
Some psql features might not work.
Type "help" for help.
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-------------------+------------------+---------------------+--------
public | getdaigtest_raise | void | | normal
public | getdiagtest | void | | normal
public | getdiagtest_raise | void | | normal
public | getdigtest_raise | void | | normal
(4 rows)
postgres=#
\dfS show a system functions - you can use a wild chars
postgres=# \dfS *agg
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+---------------------+------
pg_catalog | array_agg | anyarray | anyelement | agg
pg_catalog | string_agg | text | text, text | agg
pg_catalog | xmlagg | xml | xml | agg
(3 rows)
you can you a extended view via symbol plus
postgres=# \dfS+ *agg
List of functions
-[ RECORD 1 ]-------+------------------------------------------
Schema | pg_catalog
Name | array_agg
Result data type | anyarray
Argument data types | anyelement
Type | agg
Volatility | immutable
Owner | postgres
Language | internal
Source code | aggregate_dummy
Description | concatenate aggregate input into an array
-[ RECORD 2 ]-------+------------------------------------------
Schema | pg_catalog
Name | string_agg
Result data type | text
Argument data types | text, text
Type | agg
Volatility | immutable
Owner | postgres
Language | internal
Source code | aggregate_dummy
Description | concatenate aggregate input into a string
-[ RECORD 3 ]-------+------------------------------------------
Schema | pg_catalog
Name | xmlagg
Result data type | xml
Argument data types | xml
Type | agg
Volatility | immutable
Owner | postgres
Language | internal
Source code | aggregate_dummy
Description | concatenate XML values
Regards
Pavel Stehule
I'm pretty sure there is a way to get the source from either the schema or catalog, but I do not know what it is. However,if you are dealing with a one-time need you could always just pg_dump the schema as text and search through it usingregex/grep tools (or even just "find") David J On Jul 28, 2011, at 9:31, Rebecca Clarke <rebecca@clarke.net.nz> wrote: > Hi > > I want to search and list all the functions in a database that reference a particular table within its code. Is there away to do this? > > I can list all the functions from pg_proc, however there is nothing there which provides the code of the function, so thereforeI can't query if it mentions a table. I've tried looking in information_schema.routines but this unfortunately doesnot have it either. > > Rebecca > >
On Thu, Jul 28, 2011 at 2:31 PM, Rebecca Clarke <rebecca@clarke.net.nz> wrote: > I want to search and list all the functions in a database that reference a > particular table within its code. Is there a way to do this? No, because functions can execute SQL dynamically. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services