public inbox for [email protected]  

help / color / mirror / Atom feed

From: Steve Baldwin <[email protected]>
To: Norbert Sándor <[email protected]>
Cc: [email protected]
Subject: Re: query multiple schemas
Date: Mon, 22 Apr 2024 06:35:41 +1000
Message-ID: <CAKE1AiaoBZvtcM-ifUbgXYAJoxD-U0c1rq-s+eW9qQuB6jAYpA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

Hi Norbi,

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 would allow you to query
them as you would any of the underlying tables, and the query planner could
likely optimise the query better. With your current function, if you needed
to add a WHERE clause and the underlying tables were large, it would likely
not perform as well as the UNION ALL view.

Cheers,

Steve

On Mon, Apr 22, 2024 at 6:12 AM Norbert Sándor <[email protected]>
wrote:

> 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 to achieve is to able 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-based solution.
>
> Please note that I'm new to plpgsql, so *any* (even a less related)
> advice is welcome :)
>
> My current experimental function is:
>
> CREATE OR REPLACE FUNCTION demo.tenant_union_query(tbl anyelement)
>
> RETURNS setof json AS $func$
>
> declare
>
> _select text;
>
> begin
>
> _select := (select
>
> string_agg(
>
> format('select t.*, %L tenantId from %I.%I t', schema_name, schema_name,
> pg_typeof(tbl)),
>
> E'\n' || ' union all ' || E'\n')
>
> from (
>
> SELECT schema_name
>
> FROM information_schema.schemata
>
> where schema_name not in ('information_schema') and schema_name not like
> 'pg_%'
>
> ) tenants
>
> );
>
> return query execute 'select row_to_json(r) from (' || _select || ') as r'
> ;
>
> END;
>
> $func$ LANGUAGE plpgsql;
>
> And this is how I use it to query a "usual" result-set-like result with
> the tenant ID in the 1st column, followed by the fields from the given
> table:
>
> select r->>'tenantid' tenantId, json_populate_record(null::mytable, r)
> from tenant_union_query(null::mytable) r order by tenantId; -- (1)
>
> 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?
>    2. Is there a way to further simplify the usage, like instead of the
>    query (1) above something more simple, like: select * from
>    tenant_union_query_2(null::mytable) order by tenantId;
>
> Thanks for your help in advance.
> Best regards,
> Norbi
>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: query multiple schemas
  In-Reply-To: <CAKE1AiaoBZvtcM-ifUbgXYAJoxD-U0c1rq-s+eW9qQuB6jAYpA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox