From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Mimic ALIAS in Postgresql?
Date: Tue, 16 Jan 2024 11:47:52 -0800
Message-ID: <c6757c8a-6b0e-4e6d-82d1-8315f5bc5375@aklaver.com> (raw)
In-Reply-To: <CANzqJaAJ=CmfqYi6qC6YKxkZgywLXmSzxuFyv5i66UUYSzw5Ew@mail.gmail.com>
References: <CANzqJaCc5jVh3J3EA0j4Dz7V2HsrcR9eE0AE45pt-PmMrjhdmg@mail.gmail.com>
<eb7f0942-ce43-4d5b-9ab7-c57b6e2c276f@aklaver.com>
<CANzqJaC0_evZnPi5tX8fYuTQ0cBbm05A55LgLcdZ8Ahjv6xadQ@mail.gmail.com>
<cadba02c-a185-4d39-bd83-a767d54d3945@aklaver.com>
<CANzqJaAJ=CmfqYi6qC6YKxkZgywLXmSzxuFyv5i66UUYSzw5Ew@mail.gmail.com>
On 1/16/24 11:40 AM, Ron Johnson wrote:
> On Tue, Jan 16, 2024 at 2:24 PM Adrian Klaver
> <adrian.klaver@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.klaver@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?
>
> ?
"... 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, ronljohnsonjr@gmail.com
Subject: Re: Mimic ALIAS in Postgresql?
In-Reply-To: <c6757c8a-6b0e-4e6d-82d1-8315f5bc5375@aklaver.com>
References: <CANzqJaCc5jVh3J3EA0j4Dz7V2HsrcR9eE0AE45pt-PmMrjhdmg@mail.gmail.com> <eb7f0942-ce43-4d5b-9ab7-c57b6e2c276f@aklaver.com> <CANzqJaC0_evZnPi5tX8fYuTQ0cBbm05A55LgLcdZ8Ahjv6xadQ@mail.gmail.com> <cadba02c-a185-4d39-bd83-a767d54d3945@aklaver.com> <CANzqJaAJ=CmfqYi6qC6YKxkZgywLXmSzxuFyv5i66UUYSzw5Ew@mail.gmail.com> <c6757c8a-6b0e-4e6d-82d1-8315f5bc5375@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox