如何使用PostgreSQL中的特定表获取存储过程的列表？ [英] How to get a list of stored procedures using a specific table in PostgreSQL?

问题描述

在PostgreSQL（9.3）中有一个简单的方法来获取使用特定表的存储过程的列表？

我改变了几个表，需要修复使用它们的存储过程。

解决方案

strong>

查询返回函数名称，行号和包含'thetable'的行：

  select * 
 from（
 select proname，row_number（）over（partition by proname）as line，textline 
 from（
 select proname，unnest（string_to_array （prosrc，chr（10）））textline 
从pg_proc p 
加入pg_namespace n在n.oid = p.pronamespace 
其中nspname ='public'
和prosrc ilike' ％thetable％'
）lines 
）x 
其中textline ilike'％thetable％'; 
  

具有与
$ b

code>创建函数f2（rec table）...
创建函数f1（）返回setof thetable ...

此查询给出函数的参数的名称，返回类型和类型：

  as（
 select reltype 
 from pg_class 
其中relname ='thetable'）
 select distinct on（proname）proname，prorettype，proargtypes 
 from pg_proc p 
 join pg_namespace n on n.oid = p.pronamespace 
 cross join rtype 
其中nspname ='public'
和（
 prorettype = reltype 
或reltype :: text = any（string_to_array（proargtypes :: text，''）））
  

将查询合并为一个。我使用它们用于不同的目的。

In PostgreSQL (9.3) is there a simple way to get a list of the stored procedures that use a specific table?

I'm changing several tables and need to fix the stored procedures that use them.

解决方案

Functions which have text 'thetable' in their body.

The query returns function name, line number and line containg 'thetable':

select *
from (
    select proname, row_number() over (partition by proname) as line, textline
    from (
        select proname, unnest(string_to_array(prosrc, chr(10))) textline
        from pg_proc p
        join pg_namespace n on n.oid = p.pronamespace
        where nspname = 'public'
        and prosrc ilike '%thetable%'
        ) lines
    ) x
    where textline ilike '%thetable%';

Functions which have any argument or return value of type associated with thetable.

For example:

create function f2(rec thetable)...
create function f1() returns setof thetable... 

This query gives name, return type and types of arguments of the functions:

with rtype as (
    select reltype 
    from pg_class
    where relname = 'thetable')
select distinct on (proname) proname, prorettype, proargtypes
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
cross join rtype
where nspname = 'public'
and (
    prorettype = reltype 
    or reltype::text = any(string_to_array(proargtypes::text, ' '))) 

Of course, you can merge the queries into one. I am using them for different purposes.

这篇关于如何使用PostgreSQL中的特定表获取存储过程的列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！