How to list all the stored procedure in AWS RedShift

user_6885516 0
Viewed 9536

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

amazon-redshift amazon-web-services plsql
2 Answers
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')
user_68855160

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