Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rydvr-00HIaP-1N for pgsql-general@arkaria.postgresql.org; Sun, 21 Apr 2024 20:37:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ryduq-00G0iG-Ta for pgsql-general@arkaria.postgresql.org; Sun, 21 Apr 2024 20:36:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ryduq-00G0i5-8N for pgsql-general@lists.postgresql.org; Sun, 21 Apr 2024 20:36:00 +0000 Received: from mail-ed1-x52f.google.com ([2a00:1450:4864:20::52f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1ryduk-003xu0-Ie for pgsql-general@lists.postgresql.org; Sun, 21 Apr 2024 20:35:58 +0000 Received: by mail-ed1-x52f.google.com with SMTP id 4fb4d7f45d1cf-56e477db7fbso6071110a12.3 for ; Sun, 21 Apr 2024 13:35:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713731753; x=1714336553; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=bubmwFZ/1wy4hvBb+3XGYeVduYPDnF81HKrCv8ok5k8=; b=UFBg1ZU1LfvobumjzXbaURf243lw9E2l9Xc9biQyZWIkoTmfSv5JiwuVf+FI8vOhj8 fgRiKeAywMDG91SxCXQxQZ2Punq16CCxDOZabBzX7TIvrQKTAHNBrVNcbsus/f9NBKiH yHi14TUPcdNYTVyNu+Em3GHxE7tLDKO3AtIkhRKZeFZKi9g4DO0/1fU6YJhjs1wsNu+s eVJkUFKOxxw15Ham9J5tzfdbnkyESDM/HusSzKKF6aeVtVEriPrVfFYITfguUMe5bkAv weX2Bj8FVy7aEHmA5PkaO0bFE91IqRYYOyj/hGS3n2nHvZyNH6/EsjL7bYJVm0x8P30Z YIqA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713731753; x=1714336553; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=bubmwFZ/1wy4hvBb+3XGYeVduYPDnF81HKrCv8ok5k8=; b=Uui+wGH+5Qe6NUe+w7WMLVBRfArL0nu6xJehz20MUcpRNACc8Wscmzua6Ts91ExQm0 T9WJyzFHSEd3XUIJrLJNikfOJX48iTKLzSt27eY5u0eixswuyjZKHRFlu3aVdo9cS8T8 aB58U63M6ggUJmSP4bWSZFT0r8iCmBFYsfTKi8PNGDlvxWONMTuxqmM7PvfcwkpRbBBJ JH7QlR96Xdju7pnYw43LvwBwJegRsGQiMjeFv/1ksFaUbvU8JKujKW8Qz8xJMFCm7dGu wali9UWdjpt/G4Clq/BKyNtjmGMrBjP//5bZc54cZSIFLysXMFprqGozlHQ/8tj+NF+a ln/g== X-Gm-Message-State: AOJu0Yy/szAm9RVlPNXwX0pqRV1Xbomaj5GPOwXjb2dQOke/AsyU3DJp USHWZuyo6qLKTseycv+Eu/OKaUH+3YFtzJ7ugryZt4KRvUrZqnXowA0aqp8+Eob1nzqoRHXPeaP kd0Gx2U4PWewIHndVB6YRsDYnQMhvlg== X-Google-Smtp-Source: AGHT+IFJGrHSUErJH/lce5dHH5V9/14ABF5xYZYsng9sAZNViPsmN4Xir6TkwVx/zmjc7CFWOAABuo/S0paOhTy5mEY= X-Received: by 2002:a50:d703:0:b0:56f:e4a2:1640 with SMTP id t3-20020a50d703000000b0056fe4a21640mr5811326edi.21.1713731752772; Sun, 21 Apr 2024 13:35:52 -0700 (PDT) MIME-Version: 1.0 References: <30b10437-647c-48c9-8bd8-0f45b4af2d7b@erinors.com> In-Reply-To: <30b10437-647c-48c9-8bd8-0f45b4af2d7b@erinors.com> From: Steve Baldwin Date: Mon, 22 Apr 2024 06:35:41 +1000 Message-ID: Subject: Re: query multiple schemas To: =?UTF-8?Q?Norbert_S=C3=A1ndor?= Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000005d4f8e0616a14479" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005d4f8e0616a14479 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Norbert S=C3=A1ndor 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 :=3D (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 > --0000000000005d4f8e0616a14479 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Norbi,

If the number of tenant schem= as 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 s= et of views would allow you to query them as you would any of the underlyin= g tables, and the query planner could likely optimise the query better. Wit= h your current function, if you needed to add a WHERE clause and the underl= ying tables were large, it would likely not perform as well as the UNION AL= L view.

Cheers,

Steve

On Mon, Apr 22, 2024 at 6:12=E2=80=AFAM Norbert S=C3=A1ndor <sandor.norbert@erinors.com> w= rote:
=20 =20 =20

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=C2=A0 set.

I experimented with typed solutions like described in https://dev.to/staab/how-to-query-multiple-schemas-with-postgr= esql-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$

d= eclare

= _select text;

<= span style=3D"color:rgb(128,0,0);font-weight:bold">begin

_select :=3D (select

st= ring_agg(

format(&#= 39;select t.*, %L tenantId from %I.%I t', schema_name, schema_name= , pg_typeof(tbl)),

E'\n' || ' union all ' || <= span style=3D"color:rgb(0,128,0);font-weight:bold">E'\n')

from (

SELECT s= chema_name

FROM inform= ation_schema.schemata

= where schema_name not in ('information_schema') and schema_name not= like 'pg_%'

<= p style=3D"margin:0px"> ) 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-lik= e 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(<= span style=3D"color:rgb(128,0,0);font-weight:bold">null::mytable) order by tenantId;

Thanks for your help in advance.
Best regards,
Norbi

--0000000000005d4f8e0616a14479--