List stored functions that reference a table in PostgreSQL

Question

Just a quick and simple question: in PostgreSQL, how do you list the names of all stored functions/stored procedures using a table using just a SELECT statement, if possible? If a simple SELECT is insufficient, I can make do with a stored function.

My question, I think, is somewhat similar to this other question, but this other question is for SQL Server 2005:
List of Stored Procedure from Table

(optional) For that matter, how do you also list the triggers and constraints that use the same table in the same manner?

[List stored functions **using a table** in PostgreSQL](http://stackoverflow.com/questions/31166303/how-to-get-a-list-of-stored-procedures-using-a-specific-table-in-postgresql/31168357#31168357) — klin, Jul 13 '15 at 07:45

score 101 · Answer 1 · edited Sep 28 '18 at 08:09

101

SELECT  p.proname
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      p.pronamespace = n.oid
WHERE   n.nspname = 'public';

edited Sep 28 '18 at 08:09

wintermeyer

8,178
8
39
85

answered Oct 13 '09 at 09:03

Quassnoi

413,100
91
616
614

1

As Lukáš Lalinský pointed out in his answer, PostgreSQL supports standard `information_schema.routines` -- useful for all that like standard things. – Piotr Findeisen Oct 17 '14 at 15:18

score 18 · Answer 2 · answered Apr 14 '10 at 23:30

18

SELECT  proname, prosrc
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      pronamespace = n.oid
WHERE   nspname = 'public';

answered Apr 14 '10 at 23:30

davidwhthomas

189
1
2

3

Maybe next time simply edit the post above your own rather than change a small thing – Daniel L. VanDenBosch Oct 05 '17 at 17:22
Daniel L. VanDenBosch: He pretty much got his whole karma with the upvotes from this answer, so I don't see him doing that anytime soon. – Lucio Mollinedo Mar 29 '19 at 01:08

score 16 · Answer 3 · edited Dec 05 '14 at 04:05

16

If you are using psql, try \df

From the man page:

Tip
To look up functions taking arguments or returning values of a specific type, use your pager's search capability to scroll through the \df output.

Running \set ECHO_HIDDEN will reveal what \df is running behind the scenes.

edited Dec 05 '14 at 04:05

ustun

6,941
5
44
57

answered Oct 21 '14 at 23:05

dayer4b

978
14
26

score 10 · Answer 4 · answered Jan 28 '13 at 21:52

Same as @quassnoi and @davidwhthomas, except I added the argument names in there:

SELECT  proname, proargnames, prosrc 
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      pronamespace = n.oid
WHERE   nspname = 'public';

If the purpose behind listing the functions is to clean them up or iterate a new function with a changing params list, you will frequently need to drop functions:

DROP FUNCTION <name>(<args>);

By adding proargnames, I am able to construct the applicable function name for the drop.

Additionally, it's nice to see a more complete picture when evaluating the functions.

score 5 · Answer 5 · answered Oct 13 '09 at 09:09

5

You can use the standard information_schema schema to get metadata about your database (it's in the SQL standard, so it should work the same way in different database systems). In this case you want information_schema.routines.

answered Oct 13 '09 at 09:09

Lukáš Lalinský

40,587
6
104
126

1

Useful, but doesn't really answer the question. – Jonathan Allen Jul 21 '16 at 06:30

score 4 · Answer 6 · answered Oct 13 '09 at 08:59

4

Excluding the system stuff:

select proname from pg_proc where proowner <> 1;

answered Oct 13 '09 at 08:59

windyjonas

2,272
17
19

2

Why <> 1? On my Postgresql installation, system procedures have a proowner of 10, not 1. – bortzmeyer Oct 13 '09 at 19:43
I didn't know that. The solution is of course to change the "1" accordingly. – windyjonas Oct 14 '09 at 06:59

Michał Niklas · Answer 7 · 2015-07-13T04:57:05.053

4

Have a look at my recipe. It reads functions and triggers. It is based on informations from: Extracting META information from PostgreSQL (INFORMATION_SCHEMA)

edited Jul 13 '15 at 04:57

answered Oct 13 '09 at 09:12

Michał Niklas

53,067
18
70
114

Thanks, this worked. Combined with SELECT pg_catalog.pg_get_functiondef('functionName'::regproc) you can get the create statements as well. – Wulfhart Dec 19 '12 at 19:15
The Extracting META... link was what worked for me I didn't see the "recipe" link till now. – Wulfhart Dec 19 '12 at 19:19
1

Second link is dead. :-( Please incorporate the contents of the first link into this answer so we're not left in the dark when that one dies. Thanks. – Toby Speight Jul 01 '15 at 16:15

score 1 · Answer 8 · answered May 23 '20 at 09:25

1

Please change the schema_name and table_name in the below query:

SELECT n.nspname AS schema_name
     , p.proname AS function_name
     , pg_get_function_arguments(p.oid) AS args
     , pg_get_functiondef(p.oid) AS func_def
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
AND    n.nspname = 'schema_name'
AND    p.prosrc like '%table_name%'

Since the table name is case sensitive, so need to define the exact table name.

answered May 23 '20 at 09:25

Govind Gupta

1,555
4
15
24

1

Note that PostgreSQL does have the `ILIKE` keyword and the `~~*` operator, both of which can be used for case-insesitive matching. – mkopriva Aug 18 '21 at 06:14

score 0 · Answer 9 · edited May 23 '17 at 12:17

0

For retrieving the argument types of the functions, which are required when referencing the function in ALTER -- using oldevectortypes worked well for me.

See How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?

edited May 23 '17 at 12:17

Community

1
1

answered Jun 28 '16 at 18:28

storm_m2138

2,281
2
20
18

List stored functions that reference a table in PostgreSQL

9 Answers9

Linked