Hello
2011/7/28 Rebecca Clarke <rebecca@xxxxxxxxxxxxx>:
> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
