List of Stored Procs on a Redshift database

CREATE OR REPLACE VIEW public.v_get_stored_proc_params AS WITH arguments AS (SELECT oid, arg_num , arg_names[arg_num] AS arg_name , arg_types[arg_num - 1] AS arg_type FROM (SELECT GENERATE_SERIES(1, arg_count) AS arg_num , arg_names, arg_types, oid FROM (SELECT oid , proargnames AS arg_names , proargtypes AS arg_types , pronargs AS arg_count FROM pg_proc WHERE proowner != 1 AND prolang = 100356 ) t) t) SELECT n.nspname AS schema_name , p.proname AS proc_name , p.oid::INT AS proc_id , a.arg_num AS order , NVL(a.arg_name, '') AS parameter , FORMAT_TYPE(a.arg_type, NULL) AS data_type FROM pg_proc p LEFT JOIN pg_namespace n ON n.oid = p.pronamespace LEFT JOIN arguments a ON a.oid = p.oid WHERE p.proowner != 1 AND p.prolang = 100356 ORDER BY 1,2,3,4;

https://docs.aws.amazon.com/redshift/latest/dg/r_PG_PROC_INFO.html

PG_PROC_INFO
PG_PROC_INFO is an Amazon Redshift system view built on the PostgreSQL catalog table PG_PROC and the internal catalog table PG_PROC_EXTENDED. PG_PROC_INFO includes details about stored procedures and functions, including information related to output arguments, if any.

Here is a sample query over this view:

SELECT p.proname sp_name , n.nspname sp_schema , p.proargnames sp_args 
FROM pg_catalog.pg_namespace n 
JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid 
WHERE p.proowner > 1 AND p.prolang = 100125 ;

Once you know the name of the stored procedure you can retrieve the DDL to create it using SHOW PROCEDURE my_proc;

Create a view so you can keep using it:

CREATE OR REPLACE VIEW
public.v_get_stored_proc_params AS
SELECT
    n.nspname,
    b.usename,
    p.proname,
    p.prosrc
FROM
    pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON
    pronamespace = n.oid
JOIN pg_catalog.pg_user b on
    b.usesysid = p.proowner
WHERE
    nspname not in ('information_schema',
    'pg_catalog')