Thread: How to correct schema-names within function when the schema gets renamed?
Hi, how can I correct schema-names within function when the schema gets renamed? PG corrects automagicaly every view and foreign key. Though I found that it doesn't update the schema-name in sql-functions. Say there where a table "bar" in the schema "foo" and there is a function f(int) that results to "select * from foo.bar where id = $1". Then I rename "foo" to "sansi" and all seems nice till I call f(). Now I get an error that schema "foo" can't be found. How could I find all functions that use the now uncorrect schema-name? Btw, server is PG 8.4.
Re: How to correct schema-names within function when the schema gets renamed?
From
damien clochard
Date:
Le 29/08/2011 13:44, Andreas a écrit : > Hi, > how can I correct schema-names within function when the schema gets > renamed? > PG corrects automagicaly every view and foreign key. > > Though I found that it doesn't update the schema-name in sql-functions. > Say there where a table "bar" in the schema "foo" > and there is a function f(int) that results to "select * from foo.bar > where id = $1". > > Then I rename "foo" to "sansi" and all seems nice till I call f(). > Now I get an error that schema "foo" can't be found. > > How could I find all functions that use the now uncorrect schema-name? > Not sure, but the query below might help : SELECT proname,prosrc FROM pg_proc WHERE prosrc LIKE '%foo.%'; -- damien clochard dalibo.com | dalibo.org
Re: How to correct schema-names within function when the schema gets renamed?
From
Merlin Moncure
Date:
On Mon, Aug 29, 2011 at 6:44 AM, Andreas <maps.on@gmx.net> wrote: > Hi, > how can I correct schema-names within function when the schema gets renamed? > PG corrects automagicaly every view and foreign key. > > Though I found that it doesn't update the schema-name in sql-functions. > Say there where a table "bar" in the schema "foo" > and there is a function f(int) that results to "select * from foo.bar where > id = $1". > > Then I rename "foo" to "sansi" and all seems nice till I call f(). > Now I get an error that schema "foo" can't be found. > > How could I find all functions that use the now uncorrect schema-name? > > Btw, server is PG 8.4. IMO, the 'right' way to do functions, especially if you have a lot of code to deal with, is to keep your function bodies in .sql text files and properly checked into a SCM. If you have to do a major name change, then you can use standard code refactoring techniques and re-apply the functions. Querying the pg_proc table (as damien noted) is a way to search after the fact, but having to do that suggests you're using the database to manage your code. merlin