PostgreSQL – Select PL/pgSQL Function Definitions

Something that I did before, but never documented until now was querying the database for all of the PL/pgSQL function definitions. In PostgreSQL, you can run the following to pull all of them along with the owner, schema, function name, and function definition:

SELECT a.rolname AS "owner",
  n.nspname AS "schema",
  p.proname AS "name",
  pg_get_functiondef(p.oid) AS "definition"
FROM pg_language AS l
JOIN pg_proc AS p
ON p.prolang = l.oid
JOIN pg_namespace AS n
ON p.pronamespace = n.oid
JOIN pg_authid AS a
ON a.oid = p.proowner
WHERE l.lanname = 'plpgsql'
ORDER BY "schema" ASC,
  "name" ASC

Of course, you can modify the above query so that you can search for whatever you want based on all of the retrieved fields. If you are still learning about PostgreSQL, I definitely suggest looking through all of the tables, views, and functions in the pg_catalog schema.