Re: query multiple schemas

On Tue, Apr 23, 2024 at 11:08 AM Norbert Sándor <sandor(dot)norbert(at)erinors(dot)com>
wrote:

> *> And if/when I get back to this issue myself, I'll do the same.*
> My current solution is not much different than the one I posted in my
> original question.
>
> CREATE OR REPLACE FUNCTION tenant_query_json(tbl anyelement)
>
> RETURNS setof json AS $func$ [...]
>
>
> CREATE OR REPLACE FUNCTION tenant_query(tbl anyelement)
>
> RETURNS table(tenantId text, record anyelement) AS $func$ [...]
>
> Thanks for sharing Norbi. I'm not well versed in PG/PLsql, so using
`anyelement` and `returns setof / table` is interesting to see.

Regarding the type system, I don't know if PostgreSQL supports "structural"
typing,
i.e. types from different schemas, despite having the same "shape", are not
interoperable.
Thus your need to go through JSON to "anonymize" the types in your inner
function, then
de-anonymize them (to a schema's specific type) as a record, in your outer
function.

One solution in your case of homogenous tables types, is to centralize your
types in
a single data-less schema, and then create each tenants tables based on
those types,
i.e. all tables (across tenant schemas) share the same type. But that's
speculation mostly.

--DD

I.e. 2nd form of CREATE TABLE, from
https://www.postgresql.org/docs/current/sql-createtable.html:
CREATE [...] TABLE [...] *table_name* OF *type_name*