如何在Redshift中获取给定架构中的所有过程名称和定义? [英] How to get all the procedure name and definition in a given schema in Redshift?

问题描述

使用Redshift时，我想获取在架构中创建的所有过程的名称及其定义.

When using Redshift, I would like to get the names of all the procedure that were created in a schema, along with their definition.

我知道您可以使用SHOW PROCEDURE命令来获取定义，但这需要具有过程名称.

I know you can use the SHOW PROCEDURE command to get the definition but that requires to have the procedure name.

在SVV_TABLE中，只有关于表和视图的信息，而没有过程的信息.

In SVV_TABLE there is only information regarding tables and view but not procedure.

那么，如果有人知道如何做到这一点?

So if anyone knows how to get that ?

推荐答案

Redshift尚无此系统视图，但是您可以使用tbe PG_PROC表并将其与pg_namespace结合使用以过滤模式名称.

Redshift doesn't have a system view for that yet but you can use tbe PG_PROC table and join it with pg_namespace to filter on schema name.

    select proname, proargnames, prosrc
    from PG_PROC
    join pg_namespace on pg_namespace.oid = pg_proc.pronamespace
    where nspname = 'your_schema_name' and procsecdef = true;

procsecdef = true 仅用于获取存储过程定义，否则您还将获得python UDF.

The procsecdef = true is to get only stored procedure definition otherwise you also get python UDF.

这篇关于如何在Redshift中获取给定架构中的所有过程名称和定义?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！