Re: Mimic ALIAS in Postgresql?

[Adrian Klaver]

On 1/16/24 11:40 AM, Ron Johnson wrote:
On Tue, Jan 16, 2024 at 2:24 PM Adrian Klaver 
<adrian.kla...@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.kla...@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.