Any way to list all user-defined PostgreSQL functions?

Question

PostgreSQL provides the command \dv to list all views. Is there a similar way to list all user-defined functions or perhaps just those function defined by a particular user? \sf requires you to know a function's name and it will provide a function's definition. \df lists all functions (and there are a lot). I'd like a way to just show a list of the functions I've defined.

Answer 1

The best way to find such a query is to use psql with the --echo-hidden option. Then run the psql meta-command and you will see the query that is used.

For \df this 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 pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

You could adjust that by e.g. changing the where clause to:

AND n.nspname = 'public'

Which is equivalent to \df public.*

---

If you check the documentation of pg_proc you will notice that there is a proowner column so you could also run:

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"
FROM pg_catalog.pg_proc p
  JOIN pg_catalog.pg_roles u ON u.oid = p.proowner
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
  AND n.nspname = 'public' 
  AND u.rolname = current_user --<< this limits the functions to those that the current user owns.