Обсуждение: Grep'ing for a string in all functions in a schema?
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?
Clearly if you had all of your functions in a nice VCS you could do this but alas I don't yet live in that perfect world.
--
Wells Oliver
wellsoliver@gmail.com
Wells Oliver
wellsoliver@gmail.com
On Thu, Jan 30, 2014 at 12:45 PM, Wells Oliver <wellsoliver@gmail.com> wrote:
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';
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%';
This is the most helpful thing I've seen in months. Bravo.
On Thu, Jan 30, 2014 at 12:52 PM, bricklen <bricklen@gmail.com> wrote:
On Thu, Jan 30, 2014 at 12:45 PM, Wells Oliver <wellsoliver@gmail.com> 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@gmail.com
On Thu, Jan 30, 2014 at 12:45 PM, Wells Oliver <wellsoliver@gmail.com> 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?Clearly if you had all of your functions in a nice VCS you could do this but alas I don't yet live in that perfect world.
In this imperfect world, I usually just "pg_dump -s -n foo" to a file, then use grep, vi, etc. on that file.
Cheers,
Jeff
Wells Oliver wrote on 30.01.2014 21:45: > 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? > > Clearly if you had all of your functions in a nice VCS you could do > this but alas I don't yet live in that perfect world. As I occasionally come across this myself, I have built such a feature into SQL Workbench/J http://sql-workbench.net/manual/wb-commands.html#command-search-source http://sql-workbench.net/wbgrepsource_png.html or through the UI http://sql-workbench.net/objectsearcher_png.html it offers a bit more flexibility than just a LIKE on the source code (but that is of course not available if you are using psql or pgAdmin) Thomas
On Thu, Jan 30, 2014 at 12:52:35PM -0800, bricklen 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. Why not simply: select p.oid::regproc from pg_proc p join pg_namespace n on p.pronamespace = n.oid where n.nspname = 'your-schema' and p.prosrc~ 'searched-string'; depesz