Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rPpPl-00ETE5-7D for pgsql-general@arkaria.postgresql.org; Tue, 16 Jan 2024 19:48:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rPpPk-006yOS-6U for pgsql-general@arkaria.postgresql.org; Tue, 16 Jan 2024 19:48:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rPpPi-006yOI-RD for pgsql-general@lists.postgresql.org; Tue, 16 Jan 2024 19:47:59 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rPpPf-001rio-Nk for pgsql-general@postgresql.org; Tue, 16 Jan 2024 19:47:57 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.48]) by mailout.nyi.internal (Postfix) with ESMTP id C2C435C00E6; Tue, 16 Jan 2024 14:47:54 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute7.internal (MEProxy); Tue, 16 Jan 2024 14:47:54 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm2; t=1705434474; x=1705520874; bh=yIdZcZauW/ uKdafcjmzhaF/07YbEbKS45hu9a73bgAg=; b=bKDWa/c3Ci0jj9124ND3/TuS4T jxy4A7xWPIAj1T9nxY91sxAOExWYGb501ODhyndtxxwmQaUeO3vpqBlztKK65qim b8xIoTC+ULnMqg8CaEvtsTDOGDvkTim2tQRH3euERpxF8hvLuGr3t16nnEkpMjAP 98jrn4rmP5XFVnEKv7oSzbrxEqH27zrIvS9yLFEqjLALhkiS5dlyTDutnJ4VitMk XFtros790qj0nXfF/3sguUKAj4kdMJkNbujqVmzrKqcHAag/AnWSvoT3zNEKlyTU QTYXUvhjpXvWWtZsdv8SKcsGgIQGUFjG81iBX5fkcQgOYd5yPzcM5ANOF1DQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm3; t=1705434474; x=1705520874; bh=yIdZcZauW/uKdafcjmzhaF/07YbE bKS45hu9a73bgAg=; b=E0KYwpIcxOEZFTNrbBiDJ2EBFr4lODlNd2v4RVQw3H+j 6pUFIfSEqRu6Ms/RkZeO3kTVoGMvQxWCIOXA9RxIDLuqoGBG1tZ7PDgiQneIpThl 7ktSf1x5FYuPLA8HMjqYq0gZfMM8HeykqaknRR1DYFjYuodEfAixvd0VJezJaxGa c7eRXyHRlFmYkKTqVUFTj8xb6BIo9E698+kN6Ux9yRKt4f78j/VWNIYs59/m5AkG 4cWWKGUZXTxTL8hFwpaQNcu5bO+CstJy65NWOE6GLxViWqA6IN8flYdsONxJFxbt qFXysgJDBdDBRH5lizHnQi/GK3ITu2cHfhJ4H4Pc0Q== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvdejfedguddvlecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurheptgfkffggfgfuvfevfhfhjgesrg dtreertddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdr khhlrghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeffjeekve ektdejtdeuveetiefhgeeghfejheeufefgfeeltdeiteehffefkeffheenucffohhmrghi nhepphhoshhtghhrvghsqhhlrdhorhhgpdhsohhmvghtrggslhgvrdhnohifnecuvehluh hsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhirghnrdhk lhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 16 Jan 2024 14:47:53 -0500 (EST) Content-Type: multipart/alternative; boundary="------------y4kWmfmlFKdOhxctgRWddtjs" Message-ID: Date: Tue, 16 Jan 2024 11:47:52 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Mimic ALIAS in Postgresql? Content-Language: en-US To: Ron Johnson Cc: pgsql-general References: From: Adrian Klaver In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------y4kWmfmlFKdOhxctgRWddtjs Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/16/24 11:40 AM, Ron Johnson wrote: > On Tue, Jan 16, 2024 at 2:24 PM Adrian Klaver > wrote: > > > On 1/16/24 10:11 AM, Ron Johnson wrote: >> On Tue, Jan 16, 2024 at 12:40 PM Adrian Klaver >> 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. --------------y4kWmfmlFKdOhxctgRWddtjs Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


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.



--------------y4kWmfmlFKdOhxctgRWddtjs--