On Tue, Jan 16, 2024 at 2:24 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 1/16/24 10:11 AM, Ron Johnson wrote:
On Tue, Jan 16, 2024 at 12:40 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.
