public inbox for [email protected]
help / color / mirror / Atom feedquery multiple schemas
2+ messages / 2 participants
[nested] [flat]
* query multiple schemas
@ 2024-04-21 20:12 Norbert Sándor <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Norbert Sándor @ 2024-04-21 20:12 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* query multiple schemas
@ 2024-04-21 20:41 David G. Johnston <[email protected]>
parent: Norbert Sándor <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2024-04-21 20:41 UTC (permalink / raw)
To: Norbert Sándor <[email protected]>; +Cc: [email protected] <[email protected]>
On Sunday, April 21, 2024, Norbert Sándor <[email protected]>
wrote:
>
>
> The structure of each schema is identical, the tenant ID is the name of
> the schema.
>
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.
> 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?
>
>
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-04-21 20:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-21 20:12 query multiple schemas Norbert Sándor <[email protected]>
2024-04-21 20:41 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox