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
