Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rzCkf-002TZN-OY for pgsql-general@arkaria.postgresql.org; Tue, 23 Apr 2024 09:47:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rzCke-007Zmu-DU for pgsql-general@arkaria.postgresql.org; Tue, 23 Apr 2024 09:47:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rzCkd-007Zmm-Uq for pgsql-general@lists.postgresql.org; Tue, 23 Apr 2024 09:47:48 +0000 Received: from mail-ot1-x329.google.com ([2607:f8b0:4864:20::329]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rzCkb-004EDZ-6n for pgsql-general@lists.postgresql.org; Tue, 23 Apr 2024 09:47:46 +0000 Received: by mail-ot1-x329.google.com with SMTP id 46e09a7af769-6eb7ef71c03so3295016a34.0 for ; Tue, 23 Apr 2024 02:47:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713865664; x=1714470464; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=2kw2M/a+xheGm+RWre1Y1pTLp+KQo21divOirA3NVEk=; b=ghP4r33jZqnx2kL//QBJrl9I5ry535kmLatzrm1Y7vIJtwrBu+uzzwuEXkLohxXdTg t0vEhC+jtVzLn52KiHkNpEIZRUPa6v3zWbTRvzOHTspVxzp523K+oXIs1Tlp8WwUgQ7Q 75uFyUwNXqLnmOETCfDA0tTO9IEVzFRKjnhKPLAQgkvlhzuw/HbCl5JgkOy4/5gQW3VH ejsThyM+5DgKUmTpwADQuH+ipAQNTv4LrqgtoDniXFrb/vhZcqt3HLnTYKCBrJfsh07K 0A3Om0EdEJ+Hzejir+GkWWpzivH7r7E9q4JlFNagMSfh6RkiSaI28fi8NxzEhm5HzfeC NMIw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713865664; x=1714470464; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=2kw2M/a+xheGm+RWre1Y1pTLp+KQo21divOirA3NVEk=; b=Lr72XVzJe4AqU1qBMWOU60Ef9paFsy0rljiflA2JRS0qsbHE/on64V5UG8VH7V4/Hm CppkXgAQZL/ZpAylRp3C/2ZkkJdb5k3ZWpMl8OsT4vy8gs7IFDGVSq4iNwzhankP9suy s/FCiK+sekKhlYzWrQQ0UIMQRj9fUGagqiCTTXsIhEsNF49HmuJA7OaMR4sumyR+4W43 nixEym3axrCLFSlsbHxtcW1qVwgz8HgVzP+I45aXiqh9MnUQe/85xJsbxfcymlqV86uK RipXfc5fDWSIFBuPGrjWwgrnYt33DQFX7tdqdaKWFrDSdAiIALkFIM87mqLzVVov99tF uE0w== X-Forwarded-Encrypted: i=1; AJvYcCWbHeDkbUUgZUlfAUIY7NQ3r39P9S72yhOzWYy+rSKQhOMiBLS3qzAbvzzw55r55NBaYKF9vin/cNrZdhKpARZMweTMLt7WJjqOYVW5SarssAEr X-Gm-Message-State: AOJu0YwYKgrlMcYT+RQ0ranMc7lXGcHdFf7MPruwnU8gzNwrHB91kmio DkXC/ZG0l8l+gLbq9TH9GwawMkhihIzbmsZidQPNBviun7NEqR66ZoYphb0nlnhY2KdCxEO7Vyl cZ3HVtcKmI/piS0SrBN6nbHaXgk0= X-Google-Smtp-Source: AGHT+IGWxeFUBVpfXBwNrvM4mRSC+iuXlEGyOf/5mPH+Y6WEewNeCfeIxJS/G5qTVciHQvOcBZntFwnApuX/MUKNBmg= X-Received: by 2002:a05:6870:61c8:b0:22e:cc3c:4d86 with SMTP id b8-20020a05687061c800b0022ecc3c4d86mr16729845oah.45.1713865663893; Tue, 23 Apr 2024 02:47:43 -0700 (PDT) MIME-Version: 1.0 References: <30b10437-647c-48c9-8bd8-0f45b4af2d7b@erinors.com> <2857958.1713733916@sss.pgh.pa.us> <0d83d817-0ae5-4b1b-af18-63ec89701301@erinors.com> In-Reply-To: <0d83d817-0ae5-4b1b-af18-63ec89701301@erinors.com> From: Dominique Devienne Date: Tue, 23 Apr 2024 11:47:32 +0200 Message-ID: Subject: Re: query multiple schemas To: =?UTF-8?Q?Norbert_S=C3=A1ndor?= Cc: Tom Lane , Steve Baldwin , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000016d9900616c072e1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000016d9900616c072e1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Apr 23, 2024 at 11:08=E2=80=AFAM Norbert S=C3=A1ndor 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* --00000000000016d9900616c072e1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Apr 23, 2024 at 11:08=E2=80=AFAM = Norbert S=C3=A1ndor <sando= r.norbert@erinors.com> wrote:
=20 =20 =20
> 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 FUN= CTION tenant_query_json(tbl anyelement)

RETURNS setof json AS $func$ [...= ]


CREATE OR REPL= ACE FUNCTION tenant_query(tbl anyelement)

RETURNS table(te= nantId text, record anyelement) AS $func$ [...]

Thanks for sharing Norbi. I'm not well versed in PG/PLsq= l, so using
`anyelement` and `returns setof / table` is interesti= ng to see.

Regarding the type system, I don= 9;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 &quo= t;anonymize" the types in your inner function, then
de-anony= mize them (to a schema's specific type) as a record, in your outer func= tion.

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

--DD

I.e= . 2nd form of CREATE TABLE, from=C2=A0https://www.postgresql.org/docs/current= /sql-createtable.html:
CREATE [...] TABLE [...] table_name OF type_name<= /div>
--00000000000016d9900616c072e1--