218

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?

Improve this question

14 Answers 14

Reset to default
275
+200 
\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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Could you paste in what that query is?
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').
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.
thanks for the mention of --echo-hidden, this is certainly super helpful
by running \c ma_database then set search_path to my_schema;, command \df do the job !
152

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;
Improve this answer

2 Comments

You will find oidvectortypes really useful too. See new answer: stackoverflow.com/a/24034604/398670
The code above will not show all functions, You need a LEFT JOIN instead of JOIN to also show functions with no input parameters.
45

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;
Improve this answer

3 Comments

The proname is the name, but how to get the OID for, eg. use in pg_catalog.pg_get_function_result(oid))?
@PeterKrauss The oid column of pg_proc. It's a hidden column.
See also stackoverflow.com/a/25388031/161040 for how to exclude extension-dependent functions (e.g. functions from PostGIS).
39

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.

Improve this answer

4 Comments

Just came across your answer and tried the query on Postgres 11.5. It says: ERROR: column p.proisagg does not exist
Thanks for this; the two top-voted answers didn't show my function!
@ChristiaanWesterbeek this is for < 11 for 11+ use p.prokind = 'a' for agg and `p.prokind = 'w' for window
For those who need a bit more handholding, replace the first two WHEN lines above with: WHEN p.prokind = 'a' THEN 'agg' WHEN p.prokind = 'w' THEN 'window'
31

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;
Improve this answer

Comments

27

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';
Improve this answer

Comments

11

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%';
Improve this answer

Comments

5

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';
Improve this answer

Comments

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)
Improve this answer

2 Comments

An how is that different to Milen's answer?
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.
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
Improve this answer

Comments

1

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;
Improve this answer

Comments

0

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;
Improve this answer

Comments

0

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_%'
Improve this answer

Comments

-1

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)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.