You can query the system catalog view pg_proc_info to list all the stored procedures in a Redshift database.
pg_proc_info view has information on all functions and stored procedures. prokind column has value ‘f’ for regular functions, ‘a’ for aggregate functions and ‘p’ for stored procedures. Below query gives you the list of all stored procedures.
select proname from pg_proc_info where prokind='p';
You can view the stored procedures owned by a particular user by filtering on the proowner column.
select proname from pg_proc_info where prokind='p' and proowner='your_function_owner';