Search code examples
postgresqlfunction

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?


Solution

  • 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;