Обсуждение: Search for text in any function
Greetings!
Can anyone suggest a query that will tell me the names of all functions (both trigger and normal) that contain a given string of text?
Here's what happened:
My company's database has a table named "charge" and a view named "availcharges" that returns all charges that are available. The view's SELECT statement lists all fields in the charge table explictly.
We have two functions that use data in the charge table the same way:
declare
ChargeRec charge%rowtype
begin
select into ChargeRec * from availcharges
This used to work. But then one of us added a field to the charge table, as the last field in the table. The availcharges view was adjusted accordingly. However, behind the scenes something changed. I'm pretty sure that the view no longer returns fields in the same order. My immediate fix was to suggest using either
declare
ChargeRec record;
or
declare
ChargeRec availcharges%rowtype;
Either one works.
But I need to make sure that we don't get burned by this anywhere else. Therefore, I would like to be able to find all functions that uses the phrase "charge%rowtype". I haven't been able to find where the text of functions is stored.
Thank you very much.
RobR
On Thu, Feb 19, 2009 at 12:46:41PM -0500, Rob Richardson wrote: > Can anyone suggest a query that will tell me the names of all functions > (both trigger and normal) that contain a given string of text? assuming you mean pl/* functions, you can simply use: select proname from pg_proc where prosrc ~* 'string'; another solution might be to use pg_dump, and grep the dump, but select from pg_proc does it's job. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
Thank you very much. It works, as I'm sure you knew. But I have a further question. I've never seen the "~*" operator before, and searching for it in the docs and on Google did not return any results I could find. What does it mean? Thanks again! RobR
On Thu, Feb 19, 2009 at 01:27:23PM -0500, Rob Richardson wrote: > Thank you very much. It works, as I'm sure you knew. > > But I have a further question. I've never seen the "~*" operator > before, and searching for it in the docs and on Google did not return > any results I could find. What does it mean? http://www.postgresql.org/docs/current/interactive/functions-matching.html *~ is: Matches regular expression, case insensitive Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007