Re: clone_schema function

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