How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?

Question

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?

Answer 1

Show the user-defined functions and procedures in the database in PostgreSQL

SELECT   quote_ident(n.nspname) as schema , quote_ident(p.proname) as function 
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace 
WHERE  n.nspname not like 'pg_%'

Answer 2

I have extended one of the codes by the comment. I am using this for documentation purpose.

SELECT
    n.nspname as "Schema",
    p.proname as "Name",
    pg_catalog.pg_get_function_result(p.oid) as "Returns",
    pg_catalog.pg_get_function_arguments(p.oid) as "Parameters",
    des.description as "Comment"
FROM pg_catalog.pg_proc p
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
    LEFT JOIN (select * from pg_catalog.pg_description where objsubid = 0) des ON des.objoid = p.oid
WHERE pg_catalog.pg_function_is_visible(p.oid)
    AND n.nspname = 'my_schema'
ORDER BY 1, 2;

Answer 3

Here’s my adaptation of Rudd Zwolinski’s answer, which gives three columns with function name, parameter names, and return type:

SELECT 
  routines.routine_name AS name,
  ARRAY_AGG(parameter_name) AS parameter_names,
  routines.data_type AS return_type
FROM information_schema.routines
LEFT JOIN information_schema.parameters ON (routines.specific_name = parameters.specific_name)
WHERE routine_type = 'FUNCTION' AND routine_schema = 'public'
GROUP BY routines.routine_name, routines.data_type
ORDER BY routines.routine_name;

Output example:

name	parameter_names	return_type
add_user	{userid, password, username}	boolean
edit_user	{userid, email, firstname, lastname, phone}	boolean

Answer 4

cli npm module:

  PGHOST=localhost \
  PGUSER=process.env.USER \
  PGDATABASE=process.env.USER \
  PGPASSWORD=null \
  PGPORT=5432 \
  npx @nitra/pg-reassign-owner NEW_DB_OWNER

Reassign all objects in database (process.env.PGDATABASE) to new owner (NEW_DB_OWNER)

Answer 5

The joins in abovementioned answers returns not only input parameters, but also outputs. So it is necessary to specify also parameter_mode. This select will return list of functions with its input parametrs (if having some). Postgres 14.

select r.routine_name, array_agg(p.data_type::text order by p.ordinal_position) from information_schema.routines r left join information_schema.parameters p on r.specific_name = p.specific_name
where r.routine_type = 'FUNCTION' and r.specific_schema = 'schema_name' and (p.parameter_mode = 'IN' or p.parameter_mode is null)
group by r.routine_name order by r.routine_name;

Answer 6

Get List of function_schema and function_name...

---

SELECT
    n.nspname AS function_schema,
    p.proname AS function_name
FROM
    pg_proc p
    LEFT JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE
    n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY
    function_schema,
    function_name;

Answer 7

Is a good idea named the functions with commun alias on the first words for filtre the name with LIKE Example with public schema in Postgresql 9.4, be sure to replace with his scheme

SELECT routine_name 
FROM information_schema.routines 
WHERE routine_type='FUNCTION' 
  AND specific_schema='public'
  AND routine_name LIKE 'aliasmyfunctions%';

Answer 8

This function returns all user defined routines in current database.

SELECT pg_get_functiondef(p.oid) FROM pg_proc p
INNER JOIN pg_namespace ns ON p.pronamespace = ns.oid
WHERE ns.nspname = 'public';

Answer 9

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;

Answer 10

\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.

Answer 11

Run below SQL query to create a view which will show all functions:

CREATE OR REPLACE VIEW show_functions AS
    SELECT routine_name FROM information_schema.routines 
        WHERE routine_type='FUNCTION' AND specific_schema='public';

Answer 12

There's a handy function, oidvectortypes, that makes this a lot easier.

SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) 
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
WHERE ns.nspname = 'my_namespace';

Credit to Leo Hsu and Regina Obe at Postgres Online for pointing out oidvectortypes. I wrote similar functions before, but used complex nested expressions that this function gets rid of the need for.

See related answer.

---

(edit in 2016)

Summarizing typical report options:

-- Compact:
SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes))

-- With result data type: 
SELECT format(
       '%I.%I(%s)=%s', 
       ns.nspname, p.proname, oidvectortypes(p.proargtypes),
       pg_get_function_result(p.oid)
)

-- With complete argument description: 
SELECT format('%I.%I(%s)', ns.nspname, p.proname, pg_get_function_arguments(p.oid))

-- ... and mixing it.

-- All with the same FROM clause:
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
WHERE ns.nspname = 'my_namespace';

NOTICE: use p.proname||'_'||p.oid AS specific_name to obtain unique names, or to JOIN with information_schema tables — see routines and parameters at @RuddZwolinski's answer.

---

The function's OID (see pg_catalog.pg_proc) and the function's specific_name (see information_schema.routines) are the main reference options to functions. Below, some useful functions in reporting and other contexts.

--- --- --- --- ---
--- Useful overloads: 

CREATE FUNCTION oidvectortypes(p_oid int) RETURNS text AS $$
    SELECT oidvectortypes(proargtypes) FROM pg_proc WHERE oid=$1;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION oidvectortypes(p_specific_name text) RETURNS text AS $$
    -- Extract OID from specific_name and use it in oidvectortypes(oid).
    SELECT oidvectortypes(proargtypes) 
    FROM pg_proc WHERE oid=regexp_replace($1, '^.+?([^_]+)$', '\1')::int;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION pg_get_function_arguments(p_specific_name text) RETURNS text AS $$
    -- Extract OID from specific_name and use it in pg_get_function_arguments.
    SELECT pg_get_function_arguments(regexp_replace($1, '^.+?([^_]+)$', '\1')::int)
$$ LANGUAGE SQL IMMUTABLE;

--- --- --- --- ---
--- User customization: 

CREATE FUNCTION pg_get_function_arguments2(p_specific_name text) RETURNS text AS $$
    -- Example of "special layout" version.
    SELECT trim(array_agg( op||'-'||dt )::text,'{}') 
    FROM (
        SELECT data_type::text as dt, ordinal_position as op
        FROM information_schema.parameters 
        WHERE specific_name = p_specific_name 
        ORDER BY ordinal_position
    ) t
$$ LANGUAGE SQL IMMUTABLE;

Answer 13

If any one is interested here is what query is executed by psql on postgres 9.1:

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

You can get what psql runs for a backslash command by running psql with the -E flag.

Answer 14

Example:

perfdb-# \df information_schema.*;

List of functions
        Schema      |        Name        | Result data type | Argument data types |  Type  
 information_schema | _pg_char_max_length   | integer | typid oid, typmod integer | normal
 information_schema | _pg_char_octet_length | integer | typid oid, typmod integer | normal
 information_schema | _pg_datetime_precision| integer | typid oid, typmod integer | normal
 .....
 information_schema | _pg_numeric_scale     | integer | typid oid, typmod integer | normal
 information_schema | _pg_truetypid         | oid     | pg_attribute, pg_type     | normal
 information_schema | _pg_truetypmod        | integer | pg_attribute, pg_type     | normal
(11 rows)