Re: [GENERAL] Grep'ing for a string in all functions in a schema?
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 signature.asc Description: Digital signature
Re: [GENERAL] Grep'ing for a string in all functions in a schema?
On Thu, Jan 30, 2014 at 12:45 PM, Wells Oliver wellsoli...@gmail.comwrote: 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_%' ANDn.nspname 'information_schema'; select * from function_def where func_def ilike '%foo%';
Re: [GENERAL] Grep'ing for a string in all functions in a schema?
This is the most helpful thing I've seen in months. Bravo. On Thu, Jan 30, 2014 at 12:52 PM, bricklen brick...@gmail.com wrote: On Thu, Jan 30, 2014 at 12:45 PM, Wells Oliver wellsoli...@gmail.comwrote: 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_%' ANDn.nspname 'information_schema'; select * from function_def where func_def ilike '%foo%'; -- Wells Oliver wellsoli...@gmail.com
Re: [GENERAL] Grep'ing for a string in all functions in a schema?
On Thu, Jan 30, 2014 at 12:45 PM, Wells Oliver wellsoli...@gmail.comwrote: 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
Re: [GENERAL] Grep'ing for a string in all functions in a schema?
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
