Re: query multiple schemas

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 AM Norbert Sándor <sandor(dot)norbert(at)erinors(dot)com>
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 := (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
>