select tenantId, (record).*or by using a temporary view:
from tenant_query(null::mytable)
where (record).type=2
order by tenantId, (record).name;
create temp view all_tenant_mytable as> In my case, the revision/version of the schema could be different as well
select tenantId, (record).* from tenant_query(null::mytable);
select *
from all_tenant_mytable
where type=2
order by tenantId, name;
As you can see in my examples above, I use the tenant_query() function but in your case (if your schemas are different) something similar to tenant_query_json() may work better.CREATE OR REPLACE FUNCTION tenant_query_json(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;
CREATE OR REPLACE FUNCTION tenant_query(tbl anyelement)
RETURNS table(tenantId text, record anyelement) AS $func$
begin
return query
select t.tenantId, t.rec
from (
select
jr->>'tenantid' tenantId,
json_populate_record(tbl, jr) rec
from tenant_query_json(tbl) jr
) t;
END;
$func$ LANGUAGE plpgsql;
On Sun, Apr 21, 2024 at 11:12 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Steve Baldwin <steve.baldwin@gmail.com> writes:
> If the number of tenant schemas is reasonably static, you could write a
> plpgsql function to create a set of UNION ALL views
Another idea is to build a partitioned table
Hi Norbert. I asked a [similar question][1] a while back,and unfortunately didn't get any actionable input, perhapsbecause I already mentioned in my message the optionsproposed here so far. Seems like people like us, using adynamic number of schemas, are outliers in database-land.
In my case, the revision/version of the schema could bedifferent as well, which would complicate the partitioning idea.
In any case, I'm interested in what works well for you.And if/when I get back to this issue myself, I'll do the same.
Thanks, --DD