public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dominique Devienne <[email protected]>
To: Norbert Sándor <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Steve Baldwin <[email protected]>
Cc: [email protected]
Subject: Re: query multiple schemas
Date: Tue, 23 Apr 2024 11:47:32 +0200
Message-ID: <CAFCRh-_vhppeWG2VQ+A9kb3U6+q+tyiwc2BdMKF-r13+a2bFXg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<CAKE1AiaoBZvtcM-ifUbgXYAJoxD-U0c1rq-s+eW9qQuB6jAYpA@mail.gmail.com>
	<[email protected]>
	<CAFCRh--7Oh2CFdAhqN-GXbGt63djxHN2SYLHUwt_MKcYZZuTKg@mail.gmail.com>
	<[email protected]>

On Tue, Apr 23, 2024 at 11:08 AM Norbert Sándor <[email protected]>
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*

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: query multiple schemas
  In-Reply-To: <CAFCRh-_vhppeWG2VQ+A9kb3U6+q+tyiwc2BdMKF-r13+a2bFXg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox