MIME-Version: 1.0
References: <30b10437-647c-48c9-8bd8-0f45b4af2d7b@erinors.com>
In-Reply-To: <30b10437-647c-48c9-8bd8-0f45b4af2d7b@erinors.com>
From: Marcos Pegoraro <marcos@f10.com.br>
Date: Tue, 23 Apr 2024 09:41:25 -0300
Message-ID: <CAB-JLwYSDNPJwss7=0LbHtpc4BzBpmfTYA2L8b58wV1Gqq96uQ@mail.gmail.com>
Subject: Re: query multiple schemas
To: =?UTF-8?Q?Norbert_S=C3=A1ndor?= <sandor.norbert@erinors.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000e869dd0616c2e135"
Archived-At: <https://www.postgresql.org/message-id/CAB-JLwYSDNPJwss7%3D0LbHtpc4BzBpmfTYA2L8b58wV1Gqq96uQ%40mail.gmail.com>
Precedence: bulk

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

Em dom., 21 de abr. de 2024 =C3=A0s 17:12, Norbert S=C3=A1ndor <
sandor.norbert@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 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 =3D Current_Date','{cus_001,cus_035,cus_175}')
as (SchemaName text, Order_ID integer, sum_of_items Numeric)

regards
Marcos

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

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:verdana,sans-serif;color:rgb(102,102,102)"><span style=3D"font-fam=
ily:Arial,Helvetica,sans-serif;color:rgb(34,34,34)">Em dom., 21 de abr. de =
2024 =C3=A0s 17:12, Norbert S=C3=A1ndor &lt;<a href=3D"mailto:sandor.norber=
t@erinors.com" target=3D"_blank">sandor.norbert@erinors.com</a>&gt; escreve=
u:</span><br></div></div><div class=3D"gmail_quote"><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex"><u></u>

 =20

   =20
 =20
  <div>
    <p>Hello,</p>
    <p>I have a database with multiple tenants with a separate schema
      for each tenant.<br>
      The structure of each schema is identical, the tenant ID is the
      name of the schema.</p>
    <p>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=C2=A0 set.</p=
></div></blockquote><div><span class=3D"gmail_default" style=3D"font-family=
:verdana,sans-serif;color:rgb(102,102,102)">I have a similar structure and=
=C2=A0</span><span class=3D"gmail_default" style=3D"font-family:verdana,san=
s-serif;color:rgb(102,102,102)">do my multi tenant queries this way.</span>=
</div><div><span class=3D"gmail_default" style=3D"font-family:verdana,sans-=
serif;color:rgb(102,102,102)">The only problem I see is that we have to def=
ine every result type, because I return a record, but it runs fine.</span><=
/div><div><span class=3D"gmail_default" style=3D"font-family:verdana,sans-s=
erif;color:rgb(102,102,102)"><br></span></div><div><span class=3D"gmail_def=
ault" style=3D"font-family:verdana,sans-serif;color:rgb(102,102,102)">creat=
e function sql_per_tenant(sql text, tenants text[]) returns setof record la=
nguage plpgsql AS $function$<br>declare<br>=C2=A0 Result record;<br>=C2=A0 =
schemas text;<br>begin<br>=C2=A0 for schemas in select unnest(tenants) loop=
<br>=C2=A0 =C2=A0 execute Format(&#39;set local search_path to %s, public;&=
#39;, schemas);<br>=C2=A0 =C2=A0 for Result in execute Format(&#39;select $=
$%1$s$$ tenant, * from (%2$s) x&#39;, schemas, sql) loop<br>=C2=A0 =C2=A0 =
=C2=A0 return next Result;<br>=C2=A0 =C2=A0 end loop;<br>=C2=A0 end loop;<b=
r>end;$function$;<br><br>select * from sql_per_tenant(&#39;select Order_ID,=
 sum(Value) from Orders inner join Items using(Order_ID)=C2=A0</span></div>=
<div><span class=3D"gmail_default" style=3D"font-family:verdana,sans-serif;=
color:rgb(102,102,102)">where Due_Date =3D Current_Date&#39;,&#39;{cus_001,=
cus_035,cus_175}&#39;)=C2=A0</span></div><div><span class=3D"gmail_default"=
 style=3D"font-family:verdana,sans-serif;color:rgb(102,102,102)">as (Schema=
Name text, Order_ID integer, sum_of_items Numeric)<br></span></div><div><br=
></div><div><div class=3D"gmail_default" style=3D"font-family:verdana,sans-=
serif;color:rgb(102,102,102)">regards</div><div class=3D"gmail_default" sty=
le=3D"font-family:verdana,sans-serif;color:rgb(102,102,102)">Marcos</div><b=
r></div></div></div>

--000000000000e869dd0616c2e135--