Em dom., 21 de abr. de 2024 às 17:12, Norbert Sándor <
sandor.norb...@erinors.com> escreveu:
> Hello,
>
> I have a database with multiple tenants with a separate schema for each
> tenant.
> The structure of each schema is identical, the tenant ID is the name of
> the schema.
>
> What I would like
On Tue, Apr 23, 2024 at 11:08 AM Norbert Sándor
wrote:
> *> And if/when I get back to this issue myself, I'll do the same.*
> My current solution is not much different than the one I posted in my
> original question.
>
> CREATE OR REPLACE FUNCTION tenant_query_json(tbl anyelement)
>
> RETURNS set
> using a dynamic number of schemas
Although there are historical reasons behind our "schema per tenant"
architecture, it provides very good logical separation of data, and
is very convenient that we don't need to include the tenant ID in
each query (I'm sure that i
On Sun, Apr 21, 2024 at 11:12 PM Tom Lane wrote:
> Steve Baldwin writes:
> > If the number of tenant schemas is reasonably static, you could write a
> > plpgsql function to create a set of UNION ALL views
>
> Another idea is to build a partitioned table
>
Hi Norbert. I asked a [similar question
Steve Baldwin writes:
> If the number of tenant schemas is reasonably static, you could write a
> plpgsql function to create a set of UNION ALL views with one view for each
> table in all tenant schemas. You could re-run the function each time a
> tenant schema is added. Having the set of views wo
On Sunday, April 21, 2024, Norbert Sándor
wrote:
>
>
> The structure of each schema is identical, the tenant ID is the name of
> the schema.
>
You’ve hit the main reason why the scheme you choose is usually avoided.
Better to just add tenant_id to your tables in the first place. And use
partition
e to query tables in all schemas at
> once with the tenant IDs added to the result set.
>
> I experimented with typed solutions like described in
> https://dev.to/staab/how-to-query-multiple-schemas-with-postgresql-28kj
> without much success.
> So I turned to a more dynamic JSON-ba
set.
I experimented with typed solutions like described in
https://dev.to/staab/how-to-query-multiple-schemas-with-postgresql-28kj
without much success.
So I turned to a more dynamic JSON-based solution.
Please note that I'm new to plpgsql, so /any/ (even a less related)
advice is we
