Skip site navigation (1) Skip section navigation (2)

Re: How to correct schema-names within function when the schema gets renamed?

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Andreas <maps(dot)on(at)gmx(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to correct schema-names within function when the schema gets renamed?
Date: 2011-08-29 13:41:09
Message-ID: CAHyXU0y-MxzCKFunwBs6-8smDidTRKUA2F6=eTi02TSy_FZc7w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Mon, Aug 29, 2011 at 6:44 AM, Andreas <maps(dot)on(at)gmx(dot)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

In response to

pgsql-novice by date

Next:From: AndreasDate: 2011-08-29 15:26:16
Subject: Known problem? Column mixup in sql-function
Previous:From: damien clochardDate: 2011-08-29 13:38:13
Subject: Re: How to correct schema-names within function when the schema gets renamed?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group