pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.

view thread (19+ messages)  latest in thread

Message-ID: <c6757c8a-6b0e-4e6d-82d1-8315f5bc5375@aklaver.com>
Permalink:  ../c6757c8a-6b0e-4e6d-82d1-8315f5bc5375@aklaver.com/
Also on:    postgresql.org/message-id/c6757c8a-6b0e-4e6d-82d1-8315f5bc5375@aklaver.com

 · 

reply

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