On Tue, Apr 23, 2024 at 11:08 AM Norbert Sándor <sandor.norbert@xxxxxxxxxxx> 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