On Tue, Jan 16, 2024 at 2:24 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> > On 1/16/24 10:11 AM, Ron Johnson wrote: > > On Tue, Jan 16, 2024 at 12:40 PM Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 1/16/24 09:20, Ron Johnson wrote: >> > Some RDBMSs have CREATE ALIAS, which allows you to refer to a table by >> a >> > different name (while also referring to it by the original name). >> > >> >> > >> > Maybe updatable views? >> > CREATE VIEW mtqry.sometable AS SELECT * FROM mtuser.sometable; >> > >> >> Assuming sometable is the same name in both schemas then the above will >> not work as: >> >> https://www.postgresql.org/docs/current/sql-createview.html >> >> "The name of the view must be distinct from the name of any other >> relation (table, sequence, index, view, materialized view, or foreign >> table) in the same schema." >> >> You would get a conflict with the existing table MTQRY.sometable. >> > > > CREATE VIEW mtqry.sometable AS SELECT * FROM mtuser.sometable; > > But mtqry is not the same schema as mtuser.. > > dba=# create schema mtuser; > CREATE SCHEMA > dba=# create schema mtqry; > CREATE SCHEMA > dba=# > dba=# create table mtuser.sometable(f1 int); > CREATE TABLE > dba=# > dba=# create view mtqry.sometable as select * from mtuser.sometable; > CREATE VIEW > > But what are the down-sides that I haven't thought of? > > > What happened to the MYQRY schema in your OP? > ? > In the above you still have a relation with the same name in different > schema. > Yes. That's the whole point. > How does that change the issue? > I'm asking how to mimic table aliases, where a table is in MTUSER, but -- via the mechanism of aliases -- can be referenced from schema MTQRY.