MIME-Version: 1.0
In-Reply-To: <30b10437-647c-48c9-8bd8-0f45b4af2d7b@erinors.com>
References: <30b10437-647c-48c9-8bd8-0f45b4af2d7b@erinors.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sun, 21 Apr 2024 13:41:23 -0700
Message-ID: <CAKFQuwYLpVCeOaAo6X8yCw02JAtRT+yiLZ2Gc-N_UJyJAv-V6A@mail.gmail.com>
Subject: query multiple schemas
To: =?UTF-8?Q?Norbert_S=C3=A1ndor?= <sandor.norbert@erinors.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001d95570616a158b3"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYLpVCeOaAo6X8yCw02JAtRT%2ByiLZ2Gc-N_UJyJAv-V6A%40mail.gmail.com>
Precedence: bulk

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

On Sunday, April 21, 2024, Norbert S=C3=A1ndor <sandor.norbert@erinors.com>
wrote:
>
>
> The structure of each schema is identical, the tenant ID is the name of
> the schema.
>
You=E2=80=99ve hit the main reason why the scheme you choose is usually avo=
ided.
Better to just add tenant_id to your tables in the first place.  And use
partitioned tables if you desire physical separation.

> The above solution seems to work, my questions are:
>
>    1. Is there a better way to achieve the same functionality? Maybe
>    without using JSON as an intermediate representation?
>
>
In-database, I doubt it (though I didn=E2=80=99t study your specific soluti=
on in
depth).  Json provides the easiest way to generate the virtual tables you
need.

Otherwise maybe try something with say bash scripting and psql scripts; or
some other client-side setup where you separate the query and the metadata
lookups so the queries just return normal results and the client takes them
are merges them.

David J.

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

On Sunday, April 21, 2024, Norbert S=C3=A1ndor &lt;<a href=3D"mailto:sandor=
.norbert@erinors.com" target=3D"_blank">sandor.norbert@erinors.com</a>&gt; =
wrote:<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-l=
eft:1px #ccc solid;padding-left:1ex"><div><p><br>
      The structure of each schema is identical, the tenant ID is the
      name of the schema.</p></div></blockquote><div>You=E2=80=99ve hit the=
 main reason why the scheme you choose is usually avoided.=C2=A0 Better to =
just add tenant_id to your tables in the first place.=C2=A0 And use partiti=
oned tables if you desire physical separation.</div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-le=
ft:1ex"><div>
    <p>The above solution seems to work, my questions are:<br></p>
    <ol>
      <li>Is there a better way to achieve the same functionality? Maybe
        without using JSON as an intermediate representation?</li></ol></di=
v></blockquote><div><br></div><div>In-database, I doubt it (though I didn=
=E2=80=99t study your specific solution in depth).=C2=A0 Json provides the =
easiest way to generate the virtual tables you need.</div><div><br></div><d=
iv>Otherwise maybe try something with say bash scripting and psql scripts; =
or some other client-side setup where you separate the query and the metada=
ta lookups so the queries just return normal results and the client takes t=
hem are merges them.</div><div><br></div><div>David J.</div>

--0000000000001d95570616a158b3--