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

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%';

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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