List stored functions that reference a table in PostgreSQL

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?

Answers


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

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

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.


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.


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.


Excluding the system stuff:

select proname from pg_proc where proowner <> 1;

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


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?


Need Your Help

Pull multiple Git repositories with EGit on Eclipse at once

eclipse git pull repository

I have cloned ~60 git repositories and I would like to update the most of them with git pull through EGit. Not all, because some of them have uncommited or unstaged changes.

How to save a struct to realm in swift?

swift realm

It is easy to use Realm with classes by inheriting from Object. But how would I save a struct containing several fields to realm in Swift? E.g.