On 1/16/24 11:40 AM, Ron Johnson wrote:
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?
?
"... with two schemas: MTUSER and MTQRY. For example, sometimes
refer to
MTUSER.sometable and other times refer to it as MYQRY.sometable.
"
Now if MYQRY.sometable in your OP is in fact MTQRY.sometable then this:
create view mtqry.sometable as select * from mtuser.sometable;
is not going to work as a view cannot have the same name as a table in the same schema.
