Re: List Functions and Code

Hello

2011/7/28 Rebecca Clarke <rebecca(at)clarke(dot)net(dot)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(at)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