"ERROR: "array_agg" is an aggregate function" while getting function definitions
pg_get_functiondef() does not work on aggregate functions, and pg_catalog.pg_proc contains a number of aggregate functions. You can filter them out using the pg_proc.proisagg boolean, as follows:
select proname as name, pronamespace::pg_catalog.regnamespace, pg_catalog.pg_get_functiondef(oid)from pg_catalog.pg_procwhere proisagg is false; -- this filters them out
Here's the answer:
SELECT n.nspname AS schema_name , p.proname AS function_name , pg_get_function_arguments(p.oid) AS args , pg_get_functiondef(p.oid) AS func_defFROM (SELECT oid, * FROM pg_proc p WHERE NOT p.proisagg) pJOIN pg_namespace n ON n.oid = p.pronamespaceWHERE n.nspname LIKE 'pga%'AND pg_get_functiondef(p.oid) LIKE '%userid%';Taken from this related answer on dba.SE and adapted to my needs: