Subject: query multiple schemas

From: Norbert Sándor <[email-masked]>
Date: Sun, 21 Apr 2024 22:12:22 +0200

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

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 21 Apr 2024 13:41:23 -0700

> The structure of each schema is identical, the tenant ID is the name of
You’ve hit the main reason why the scheme you choose is usually avoided.
Better to just add tenant_id to your tables in the first place.  And use
partitioned tables if you desire physical separation.

>    without using JSON as an intermediate representation?
In-database, I doubt it (though I didn’t study your specific solution in
depth).  Json provides the easiest way to generate the virtual tables you
need.

Otherwise maybe try something with say bash scripting and psql scripts; or
some other client-side setup where you separate the query and the metadata
lookups so the queries just return normal results and the client takes them
are merges them.

David J.