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:
- Is there a better way to achieve the same functionality? Maybe without using JSON as an intermediate representation?
- 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
