Re: List Functions and Code - Mailing list pgsql-general

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