Re: Mimic ALIAS in Postgresql?

On 1/16/24 11:40 AM, Ron Johnson wrote:
> 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?
>
> ?

"... 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.