MIME-Version: 1.0
In-Reply-To: 
 <CAKJANSDpz7TXn30vv1HVSjRv0N+xSnyiSnfyMdFaeXh1_yfFuw@mail.gmail.com>
References: 
 <CAKJANSDpz7TXn30vv1HVSjRv0N+xSnyiSnfyMdFaeXh1_yfFuw@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Fri, 22 Mar 2024 06:28:42 -0700
Message-ID: 
 <CAKFQuwbpYt=P2RudjAs_ZMUHZEO9PWrVcBhX9bR7GRUvTHPu+Q@mail.gmail.com>
Subject: Re: How to choose table in SELECT clause without schema qualifier or
 'set local'
To: intmail01 <intmail01@gmail.com>
Cc: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000072e57506143fcd5f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwbpYt%3DP2RudjAs_ZMUHZEO9PWrVcBhX9bR7GRUvTHPu%2BQ%40mail.gmail.com>
Precedence: bulk

--00000000000072e57506143fcd5f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Friday, March 22, 2024, intmail01 <intmail01@gmail.com> wrote:

> Hi,
>
> My db have many schemas (company name) and there are SELECT code related
> to tables that I distribute for each company users.
>
> The structure inside are the same for all schemas.
>
> It is a nightmare to change code each time to modify the schema qualifier
> or SET LOCAL search_path when I add a new company.
> Is there command, configurations or any trick to not modify code each tim=
e
> I add company then code can works for any schema.
>
>
Do multi-tenant via partitioning and a tenant_id column instead of by
schema.

Otherwise, maybe attach a custom search_path value to the logon user each
company uses?  See =E2=80=9Calter role =E2=80=A6 set=E2=80=9D.  But it is s=
earch_path you need to
be using.

David J.

--00000000000072e57506143fcd5f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Friday, March 22, 2024, intmail01 &lt;<a href=3D"mailto:intmail01@gmail.=
com">intmail01@gmail.com</a>&gt; wrote:<br><blockquote class=3D"gmail_quote=
" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><=
div dir=3D"ltr"><div>Hi,</div><div><br></div><div>My db have many schemas (=
company name) and there are SELECT code related to tables that I distribute=
 for each company users.</div><div><br></div><div>The structure inside are =
the same for all schemas.</div><div><br></div><div>It is a nightmare to cha=
nge code each time to modify the schema qualifier or SET LOCAL search_path =
when I add a new company.</div><div>Is there command, configurations or any=
 trick to not modify code each time I add company then code can works for a=
ny schema.<br></div><div><br></div></div></blockquote><div><br></div><div>D=
o multi-tenant via partitioning and a tenant_id column instead of by schema=
.</div><div><br></div><div>Otherwise, maybe attach a custom search_path val=
ue to the logon user each company uses?=C2=A0 See =E2=80=9Calter role =E2=
=80=A6 set=E2=80=9D.=C2=A0 But it is search_path you need to be using.</div=
><div><br></div><div>David J.</div><div><br></div>

--00000000000072e57506143fcd5f--