Thread: Stored procedures and schema renames

I would like to rename schemas without breaking the stored procedures in
them.  Currently, this does not work if the stored procedure definition
contains a schema self-reference because that does not get renamed.  I
tried "SET search_path FROM CURRENT", but that seems to expand the
search path on function definition time, which does not address this
issue either.

If backend code changes are required, what would be a reasonable way to
approach this?  Would adding a CURRENT_SCHEMA pseudo-schema which can be
used in stored procedures work?

--
Florian Weimer / Red Hat Product Security Team

Code for db functions should be a repository. Easy edit and rerun

Sent from my iPhone

> On Apr 10, 2014, at 6:19 AM, Florian Weimer <fweimer@redhat.com> wrote:
>
> I would like to rename schemas without breaking the stored procedures in them.  Currently, this does not work if the
storedprocedure definition contains a schema self-reference because that does not get renamed.  I tried "SET
search_pathFROM CURRENT", but that seems to expand the search path on function definition time, which does not address
thisissue either. 
>
> If backend code changes are required, what would be a reasonable way to approach this?  Would adding a CURRENT_SCHEMA
pseudo-schemawhich can be used in stored procedures work? 
>
> --
> Florian Weimer / Red Hat Product Security Team
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On 04/10/2014 03:29 PM, Rob Sargent wrote:

> Code for db functions should be a repository. Easy edit and rerun

Well, not necessarily inside the transaction that renames the schema.

I've settled for this inside the transaction (running as a superuser):

    UPDATE pg_proc
       SET proconfig = '{"search_path=symboldb, public"}'
       WHERE pronamespace = (SELECT oid FROM pg_namespace
                             WHERE nspname = 'symboldb');


--
Florian Weimer / Red Hat Product Security Team