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?

score 256 · Answer 1 · edited Sep 25 '16 at 19:09

256

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

edited Sep 25 '16 at 19:09

Adobe

12,967
10
85
126

answered Aug 28 '09 at 16:27

Milen A. Radev

60,241
22
105
110

5

Could you paste in what that query is? – Rudd Zwolinski Aug 28 '09 at 20:30
4

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 n.nspname ~ '^(public)$' ORDER BY 1, 2, 4; Above is the query generated (from \set ECHO_HIDDEN 'on'). – Simon D Jan 06 '18 at 09:03
3

A problem with this approach is that the query is generated for a specific version of Postgres, and might fail for other versions. For instance, on Postgres 11 you get `ERROR: column p.proisagg does not exist`. – Code4R7 Feb 04 '21 at 20:43
thanks for the mention of `--echo-hidden`, this is certainly super helpful – radiospiel Mar 30 '21 at 08:55
by running `\c ma_database` then `set search_path to my_schema;`, command `\df` do the job ! – bcag2 Mar 18 '22 at 09:40

score 134 · Accepted Answer · edited Oct 26 '17 at 16:28

134

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;

edited Oct 26 '17 at 16:28

Yuliya Plotka

99
5

answered Aug 28 '09 at 15:11

Rudd Zwolinski

26,712
17
57
60

5

You will find `oidvectortypes` really useful too. See new answer: http://stackoverflow.com/a/24034604/398670 – Craig Ringer Jun 04 '14 at 10:09
The code above will not show all functions, You need a LEFT JOIN instead of JOIN to also show functions with no input parameters. – David May 14 '17 at 16:31

score 41 · Answer 3 · edited May 23 '17 at 12:10

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;

The `proname` is the name, but how to get the OID for, eg. use in `pg_catalog.pg_get_function_result(oid))`? — Peter Krauss, May 12 '16 at 06:06
@PeterKrauss The `oid` column of `pg_proc`. It's a hidden column. — Craig Ringer, May 12 '16 at 06:23
See also https://stackoverflow.com/a/25388031/161040 for how to exclude extension-dependent functions (e.g. functions from PostGIS). — Simon D, May 07 '18 at 11:09

score 38 · Answer 4 · edited Jun 04 '14 at 10:08

38

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.

edited Jun 04 '14 at 10:08

Craig Ringer

307,061
76
688
778

answered Dec 12 '13 at 17:18

jb.

23,300
18
98
136

1

Just came across your answer and tried the query on Postgres 11.5. It says: `ERROR: column p.proisagg does not exist` – Christiaan Westerbeek Apr 30 '20 at 11:50
Thanks for this; the two top-voted answers didn't show my function! – machineghost Jul 02 '20 at 19:11
1

@ChristiaanWesterbeek this is for < 11 for 11+ use `p.prokind = 'a'` for agg and `p.prokind = 'w' for window – DiamondDrake Feb 04 '22 at 16:43

score 25 · Answer 5 · edited May 28 '16 at 21:47

25

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

edited May 28 '16 at 21:47

luka5z

7,525
6
29
52

answered May 28 '16 at 18:48

laudarch

573
5
7

score 24 · Answer 6 · edited Aug 12 '20 at 15:41

24

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;

edited Aug 12 '20 at 15:41

ololobus

3,568
2
18
23

answered Nov 19 '19 at 09:55

maddy

1,109
1
6
4

score 10 · Answer 7 · edited May 30 '20 at 23:15

10

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%';

edited May 30 '20 at 23:15

Sergei Krivonos

4,217
3
39
54

answered Dec 23 '15 at 10:03

Alberto Morales Fernández

142
1
4

score 4 · Answer 8 · edited May 27 '14 at 15:30

4

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)

edited May 27 '14 at 15:30

Lukas Knuth

25,449
15
83
111

answered Sep 24 '13 at 11:58

Ritu

49
1
2

6

An how is that different to Milen's answer? – Sep 24 '13 at 12:01
3

This is not a query, its a command of the `psql` Postgres client interface. This will only work in `psql` and is not technically a SQL query. – GregT Aug 04 '15 at 21:49

score 4 · Answer 9 · answered Nov 19 '19 at 08:02

4

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

answered Nov 19 '19 at 08:02

Alex

119
3

score 1 · Answer 10 · answered Nov 14 '22 at 09:44

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;

score 1 · Answer 11 · answered Jun 19 '23 at 14:12

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

score -1 · Answer 12 · answered Apr 30 '23 at 08:46

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)

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

12 Answers12

Linked