How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
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?
\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 the above command.
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
FROM information_schema.routines
LEFT 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;