Re: clone_schema function - Mailing list pgsql-general

On 9/14/15 8:02 PM, Melvin Davidson wrote:

Actually, on further thought, you example shows that it works correctly
because we do want all references to the old schema to be changed to the
new schema, since all copies of functions will now reside in the new
schema. Otherwise, there is no point of duplicating those functions.

Read my example again:

  SELECT old.field FROM old.old;

That will end up as

  SELECT new.field FROM new.old

Which will give you this error:

ERROR:  missing FROM-clause entry for table "new"
LINE 1: SELECT new.field FROM new.old;

Even if you could fix that, there's yet more problems you'll run into, like if someone has a plpgsql block with the same name as the old schema.

I'm not trying to denigrate the work you and others have put into this script, but everyone should be aware that it's impossible to create a robust solution without a parser. Unfortunately, you could end up with a function that still compiles but does something rather different after the move. That makes the script potentially dangerous (granted, the odds of this are pretty low).

One thing I think would be very interesting is a parser that preserves whitespace and comments. That would allow us to store a parsed version of (at least plpgsql and sql) functions. The same technique would also be handy for views. This would allow a lot (all?) other renames to propagate to functions instead of breaking them (as currently happens).

Another option is supporting some kind of official way to specially designate database objects in any procedure language (ie, the @schema@ syntax that extensions use). That would make it possible to rename properly written functions without adverse side effects.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com