Search Postgresql Archives

Re: Grep'ing for a string in all functions in a schema?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



This is the most helpful thing I've seen in months. Bravo.


On Thu, Jan 30, 2014 at 12:52 PM, bricklen <bricklen@xxxxxxxxx> wrote:

On Thu, Jan 30, 2014 at 12:45 PM, Wells Oliver <wellsoliver@xxxxxxxxx> wrote:
Since Postgres does not consider a table as a dependency of a function if that table is referenced in the function (probably a good reason), I often find myself in a position of asking "is this table/sequence/index referenced in any of these N number of functions?"

Is there an easy way of essentially grep'ing all of the functions in a given schema for a string?

A method I've used in the past is to create a view of function source which can then be searched.
Eg.

CREATE OR REPLACE VIEW function_def as
SELECT n.nspname AS schema_name,
       p.proname AS function_name,
       pg_get_function_arguments(p.oid) AS args,
       pg_get_functiondef(p.oid) AS func_def
FROM   (SELECT oid, * FROM pg_proc p WHERE NOT p.proisagg) p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname !~~ 'pg_%'
AND    n.nspname <> 'information_schema';

select * from function_def where func_def ilike '%foo%';



--
Wells Oliver
wellsoliver@xxxxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux