How to list all the stored procedure in AWS RedShift

Question

I was checking this, but not find the proper one. So I prepared one and sharing that query here.

Haemal

See also: How to get all the procedure name and definition in a given schema in Redshift? – Phatic

Answer 1

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_user b on
    b.usesysid = p.proowner
where
    nspname not in ('information_schema',
    'pg_catalog')

Haemal

There is a very small mistake, missed out the schema name for pg_user. ``` 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') ``` – Onomastics

Answer 2

This was really helpful. There is a very small mistake, missed out the schema name for pg_user.

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')

Onomastics