Re: Mimic ALIAS in Postgresql?

On Tue, Jan 16, 2024 at 2:24 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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