Subject: How to choose table in SELECT clause without schema qualifier or 'set local'

From: intmail01 <[email-masked]>
Date: Fri, 22 Mar 2024 10:11:23 +0300

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 time
I add company then code can works for any schema.

Thanks.

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 22 Mar 2024 06:28:42 -0700

On Friday, March 22, 2024, intmail01 <[email-masked]> wrote:

> Hi,
>
> My db have many schemas (company name) and there are SELECT code related
> to tables that I distribute for each company users.
>
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 “alter role … set”.  But it is search_path you need to
be using.

David J.