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 1rydY9-00HFUu-J7 for pgsql-general@arkaria.postgresql.org; Sun, 21 Apr 2024 20:12:33 +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 1rydY8-00FtvX-0q for pgsql-general@arkaria.postgresql.org; Sun, 21 Apr 2024 20:12:32 +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 1rydY7-00FtvJ-E4 for pgsql-general@lists.postgresql.org; Sun, 21 Apr 2024 20:12:31 +0000 Received: from mail-ej1-x62c.google.com ([2a00:1450:4864:20::62c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rydY2-003xlj-Ad for pgsql-general@lists.postgresql.org; Sun, 21 Apr 2024 20:12:29 +0000 Received: by mail-ej1-x62c.google.com with SMTP id a640c23a62f3a-a55b481cf0fso42332666b.3 for ; Sun, 21 Apr 2024 13:12:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=erinors-com.20230601.gappssmtp.com; s=20230601; t=1713730343; x=1714335143; darn=lists.postgresql.org; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:from:to:cc:subject:date:message-id:reply-to; bh=1FRTcajov2X2Yc9KFdpOWUXv7o/wtswzsDlJp5Q/Vo8=; b=NxzIrQHreU1Rk1vRutUDpIxXpwvCTVb19BNvTO1B+WRB2Bwf+YsXeeOBkJ1Vij498l CQaT5ICRLQx9aTeyAh9RA5uvH1f+DWt40r2GTyUDdAzRKJy7tIxouKC5dgK1yTuacqsV yTlPJw5ib5qPFSemBV3Van1FQ4Be+mw0Dec0FLTJiA0QRXP6njGukcTisUOPW/ByB5KP jY8O5zuFkV4CgTmMwNbuuxHlw8X1qTXZOc5HA5VKPYVSaWznMQTw2Ex8cy3xABrTht2Z NcLGwKFF/9SFe1vZ/uMiNOF7cgv4QsPkRrA9hV05PmdPp0c9israYdpshNhwXf4zUnDg X7Cg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713730343; x=1714335143; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=1FRTcajov2X2Yc9KFdpOWUXv7o/wtswzsDlJp5Q/Vo8=; b=Fu2c6kw9NOmgs1vh+xl3FHhNUh+UacJqZEqmlAqW8yV74tvoblXIiWdEDr/RGiDC4r d8POi9ZeqCGtoUjEVSLnEEKoywEPFQOaDGWoELa7GxVBo3xAEpwo2ItwwCUv82a4sgvM oyFLJKzci67orCOsdjnzhCqd1wXVwS8Dh96Rqn/sfQUcHABO/DaHgBWIlUBe1810eDTJ zaL9RyqwHz+x8K9kfNc+wasHiMkCLqyh95sBFavoeWAOd/yzo2+ByQvsNVxkYWqf5rcj fYWnhvTnXNExarBHlOPoald74PkgNBCLf1zi8HiO2a0y4Ye92IujbFtCsF38/5naqPTc JMSw== X-Gm-Message-State: AOJu0YyIkuHCAhvMLsRmyVP5uFRItZaOrJutL0R12TpmtIRcsOMGtRF7 hpWWwwF86ivm+N/vRjCBRbk+WN4xAEo4HXV8NrfqtxgObrtNVFkfDbKpnTAv0dARgJU8rzmDW8N 4 X-Google-Smtp-Source: AGHT+IGldCh269h1PSRn4Qkh3sJuvlTrN+KvIN1iWmtUA47jP5gdv9QU/KH5CgfX3hc+gyPbzrLzHQ== X-Received: by 2002:a17:907:6d02:b0:a55:b27c:1299 with SMTP id sa2-20020a1709076d0200b00a55b27c1299mr1357109ejc.70.1713730343480; Sun, 21 Apr 2024 13:12:23 -0700 (PDT) Received: from ?IPV6:2a02:ab88:850a:4c00:161d:3b58:120:2a6f? ([2a02:ab88:850a:4c00:161d:3b58:120:2a6f]) by smtp.gmail.com with ESMTPSA id q21-20020a170906771500b00a51d408d446sm4905772ejm.26.2024.04.21.13.12.22 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 21 Apr 2024 13:12:23 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------nikT1ZpPCJB9ihJcUfjZXlfZ" Message-ID: <30b10437-647c-48c9-8bd8-0f45b4af2d7b@erinors.com> Date: Sun, 21 Apr 2024 22:12:22 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Content-Language: en-US, hu To: pgsql-general@lists.postgresql.org From: =?UTF-8?Q?Norbert_S=C3=A1ndor?= Subject: query multiple schemas List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------nikT1ZpPCJB9ihJcUfjZXlfZ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------nikT1ZpPCJB9ihJcUfjZXlfZ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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

--------------nikT1ZpPCJB9ihJcUfjZXlfZ--