List Functions and Code

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 using regex/grep tools (or even just "find")

David J

On Jul 28, 2011, at 9:31, Rebecca Clarke <rebecca@clarke.net.nz> wrote:

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