public inbox for [email protected]  

help / color / mirror / Atom feed

From: Norbert Sándor <[email protected]>
To: [email protected]
Subject: query multiple schemas
Date: Sun, 21 Apr 2024 22:12:22 +0200
Message-ID: <[email protected]> (raw)

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

view thread (2+ messages)  latest in thread

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]
  Subject: Re: query multiple schemas
  In-Reply-To: <[email protected]>

* 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