Re: query multiple schemas - Mailing list pgsql-general

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