Re: Cross-schema view issue/question

On Thu, Apr 14, 2011 at 07:33:17PM -0700, Bosco Rama wrote:
> 1) a function that removes/creates the views, etc in the 'xyz' schema
> that gets called as part of the replacement process for schema 'abc'
>
> 2) replacing the views, etc. with functions that return tables or
> the results of dynamic queries.
>
> 3) have the user create the views, etc. as 'temp' items in their
> session each time. Though this may still conflict with the
> replacement since there will still be a tight coupling between
> the temp objects and the 'abc' schema and the replacement occurs
> regardless of the current state of user connections.

#2 will screw up query planning substantially; I'd steer clear of it. The
other two options are essentially deciding whether you or your user will
recreate the xyz objects each time you replace abc. That's kinda a religious
issue, and depends on things like how stable your user expects the objects in
xyz to be.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com