views:

1037

answers:

6

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:
http://stackoverflow.com/questions/119679/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?

A: 

Excluding the system stuff:

select proname from pg_proc where proowner <> 1;
windyjonas
Why <> 1? On my Postgresql installation, system procedures have a proowner of 10, not 1.
bortzmeyer
I didn't know that. The solution is of course to change the "1" accordingly.
windyjonas
+1  A: 
SELECT  proname
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      pronamespace = n.oid
WHERE   nspname = 'public'
Quassnoi
A: 

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.

Lukáš Lalinský
+1  A: 

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

Michał Niklas
A: 

information_schema.routines view does not contain as much information as the pg_proc dictionary table. For example, you will not find the source code (even if there is a routine_body column, it is never filled properly).
You will be able to list procedure / function names only from the information_schema.

Regards,
Christophe
http://www.nextep-softwares.com

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