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?

Saudra

List stored functions using a table in PostgreSQL – Merow

Answer 1

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

Cheque

As Lukáš Lalinský pointed out in his answer, PostgreSQL supports standard information_schema.routines -- useful for all that like standard things. – Landtag

Answer 2

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

Childhood

Maybe next time simply edit the post above your own rather than change a small thing – Chutzpah

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. – Unconscious

Answer 3

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.

Alleris

Answer 4

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.

Ministrant

Answer 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.

Aerometer

Useful, but doesn't really answer the question. – Latoyalatoye

Answer 6

Excluding the system stuff:

select proname from pg_proc where proowner <> 1;

Reclusion

Why <> 1? On my Postgresql installation, system procedures have a proowner of 10, not 1. – Amphithecium

I didn't know that. The solution is of course to change the "1" accordingly. – Reclusion

Answer 7

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

Gurglet

Thanks, this worked. Combined with SELECT pg_catalog.pg_get_functiondef('functionName'::regproc) you can get the create statements as well. – Powwow

The Extracting META... link was what worked for me I didn't see the "recipe" link till now. – Powwow

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. – Pigeon

Answer 8

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.

Blaise

Note that PostgreSQL does have the ILIKE keyword and the ~~* operator, both of which can be used for case-insesitive matching. – Rainie

Answer 9

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?

Galileo