public inbox for [email protected]  

help / color / mirror / Atom feed

From: Marcos Pegoraro <[email protected]>
To: Norbert Sándor <[email protected]>
Cc: [email protected]
Subject: Re: query multiple schemas
Date: Tue, 23 Apr 2024 09:41:25 -0300
Message-ID: <CAB-JLwYSDNPJwss7=0LbHtpc4BzBpmfTYA2L8b58wV1Gqq96uQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

Em dom., 21 de abr. de 2024 às 17:12, Norbert Sándor <
[email protected]> 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 to achieve is to able to query tables in all schemas at
> once with the tenant IDs added to the result  set.
>
I have a similar structure and do my multi tenant queries this way.
The only problem I see is that we have to define every result type, because
I return a record, but it runs fine.

create function sql_per_tenant(sql text, tenants text[]) returns setof
record language plpgsql AS $function$
declare
  Result record;
  schemas text;
begin
  for schemas in select unnest(tenants) loop
    execute Format('set local search_path to %s, public;', schemas);
    for Result in execute Format('select $$%1$s$$ tenant, * from (%2$s) x',
schemas, sql) loop
      return next Result;
    end loop;
  end loop;
end;$function$;

select * from sql_per_tenant('select Order_ID, sum(Value) from Orders inner
join Items using(Order_ID)
where Due_Date = Current_Date','{cus_001,cus_035,cus_175}')
as (SchemaName text, Order_ID integer, sum_of_items Numeric)

regards
Marcos

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: <CAB-JLwYSDNPJwss7=0LbHtpc4BzBpmfTYA2L8b58wV1Gqq96uQ@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