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?
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. LEFT JOIN, instead of JOIN, is necessary to retrieve functions without parameters.
SELECT routines.routine_name, parameters.data_type, parameters.ordinal_position
LEFT JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
ORDER BY routines.routine_name, parameters.ordinal_position;