views:

59

answers:

2

I want to be able to connect to a PostgreSQL database and find all of the functions for a particular schema.

My thought was that I could make some query to pg_catalog or information_schema and get a list of all functions, but I can't figure out where the names and parameters are stored. I'm looking for a query that will give me the function name and the parameter types it takes (and what order it takes them in).

Is there a way to do this?

A: 

After some searching, I was able to find the information_schema.routines table and the information_schema.parameters tables. Using those, one can construct a query for this purpose.

SELECT routines.routine_name, parameters.data_type, parameters.ordinal_position
FROM information_schema.routines
    JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
WHERE routines.specific_schema='my_specified_schema_name'
ORDER BY routines.routine_name, parameters.ordinal_position;
Rudd Zwolinski
+1  A: 

"\df <schema>.*" in psql gives the necessary information.

To see the query that's used internally connect to a database with psql and supply an extra "-E" (or "--echo-hidden") option and then execute "\df <schema>.*".

Milen A. Radev
Could you paste in what that query is?
Rudd Zwolinski