Skip site navigation (1) Skip section navigation (2)

Re: List Functions and Code

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Rebecca Clarke <rebecca(at)clarke(dot)net(dot)nz>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: List Functions and Code
Date: 2011-07-28 13:50:01
Message-ID: CAFj8pRB7xZk_Fic_L0AQU1ub1vJPD9=LjL_O8eMj9=r2vxJDcQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Merlin MoncureDate: 2011-07-28 14:03:26
Subject: Re: eval function
Previous:From: Adrian KlaverDate: 2011-07-28 13:43:29
Subject: Re: List Functions and Code

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group