Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kwNWP-0000yO-Bj for pgsql-bugs@arkaria.postgresql.org; Mon, 04 Jan 2021 10:55:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kwNWO-0004zl-9B for pgsql-bugs@arkaria.postgresql.org; Mon, 04 Jan 2021 10:55:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kwNWM-0004ze-Qy for pgsql-bugs@lists.postgresql.org; Mon, 04 Jan 2021 10:55:32 +0000 Received: from [66.111.4.26] (helo=out2-smtp.messagingengine.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kwNWI-0000Pu-Uc for pgsql-bugs@lists.postgresql.org; Mon, 04 Jan 2021 10:55:29 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.nyi.internal (Postfix) with ESMTP id 411775C01A0; Mon, 4 Jan 2021 05:55:16 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute1.internal (MEProxy); Mon, 04 Jan 2021 05:55:16 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:in-reply-to; s=fm3; bh=OswjsWQmUbJpF0qvrMs03Q75Pck zr9nKnAGuovLYeDc=; b=Vb9ua9/D5j47h8guGL6qCnsze+P1xtfPKXPIJbRowxy 09SF1TRAIof/sdvrapU039DePkpgUoKI24OfxT+X3x+e/4QQzUXs9t2arGcCtHWr ZnbpNT1p2yniK6dLZy4QEkj3rx2A4lGe63OcqZ7CX6tnMxMMlQXMrA4jS7M9K3J4 7Eps0i5nG9Orxmk1h54OL9Cqti+9nGXtBYIDP8MmeCtVIEqxsol187vpxmvsIkeR YVym1R+1gKU1GkUTz4Yo97YDOpihDRIuIBH4lUPeHQ1pZGfDVcqvmChEX4sC4Knd dqey9DYFhIQ1flVh5ux+nQddnGF2Mz1ESCxbUBcKFpg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=OswjsW QmUbJpF0qvrMs03Q75Pckzr9nKnAGuovLYeDc=; b=henKnBmjuYo9emlYkATRC4 6bHZtKt7BB7dsbysGOVP9FXMzdlqiNeV60MWa7pyImYGngc4hgHFs69mgRqfarnk kxCN4XkCxXGItRV30xkXzv+3tB+BsU2ymcF1Vm1G/zA3Y3vOiNvJQMpvpX/SYoX6 ca9W3t6Bxznap0D19NCOyZKQDtIM8uEwr6bFvMi7dJUxN+yJU4csGArGWV8/QJt1 z8eaFStJhsqc4srsTq9FCbe5qeYNMuesVZ3YbTPdoUJCiaVJejmhAE6xurw5RkmM n30hztP0kZ5MxtVL/0/Q/oyggWBKp29AqAmfq7QDifCtj26FxeyBbiVWE2/D8Zfw == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedujedrvdeffedgvdegucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne gfrhhlucfvnfffucdljedtmdenucfjughrpeffhffvuffkfhggtggujgesghdtreertddt vdenucfhrhhomhepofhitghhrggvlhcurfgrqhhuihgvrhcuoehmihgthhgrvghlsehprg hquhhivghrrdighiiiqeenucggtffrrghtthgvrhhnpedvgeduuefhtdeuleettdevjeeh heeiveeuieegleetgeeljeelieeuieehgeevhfenucfkphepudduuddruddtvddrudektd drudekheenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhm pehmihgthhgrvghlsehprghquhhivghrrdighiii X-ME-Proxy: Received: from paquier.xyz (ee0822lan1.rev.em-net.ne.jp [111.102.180.185]) by mail.messagingengine.com (Postfix) with ESMTPA id 302A81080059; Mon, 4 Jan 2021 05:55:14 -0500 (EST) Date: Mon, 4 Jan 2021 19:55:11 +0900 From: Michael Paquier To: Alexander Mills Cc: pgsql-bugs@lists.postgresql.org Subject: Re: how to prepare a create table statement Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="Duda26nu9BtQr/Q7" Content-Disposition: inline In-Reply-To: X-Host-Lookup-Failed: Reverse DNS lookup failed for 66.111.4.26 (deferred) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Duda26nu9BtQr/Q7 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Sun, Jan 03, 2021 at 08:46:19PM -0800, Alexander Mills wrote: > I am trying to create 500 partitions using a loop: >=20 > do $$ > declare > counter integer :=3D 0; > begin > while counter <=3D 500 loop > PREPARE create_table(int) AS > CREATE TABLE mbk_auth_method_$1 PARTITION OF mbk_auth_method FOR > VALUES WITH (modulus 500, remainder $1); > EXECUTE create_table (counter); > counter :=3D counter + 1; > end loop; > end$$; >=20 > Anyone know how I can accomplish the above? Seems like this is a missing > feature - to prepare a CREATE TABLE statement.. What if you simply used a FOR loop and EXECUTE with format()? Say, roughly, something like that: FOR i IN 1..num_tables LOOP EXECUTE format(' CREATE TABLE mbk_auth_method_%I PARTITION OF mbk_auth_method FOR VALUES WITH (modulus 500, remainder %I)', i, i); END LOOP; -- Michael --Duda26nu9BtQr/Q7 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl/y9A8ACgkQnvQgOdby QH11ig//XVSh18hw7c52TvW0TRzM+6nPviLbTjFf9GQ1NcAU1AXwL1OQSANsJK4/ WObSUz5CQz3RscaNhWn7Ii9xMTPt48zVaE/E/Ha0E/k1TUVpEjxpJDIY5NBlVteV CZ9YwD7CePmXLDBcA0mQLqrnLNZJsl5TiTbfWT2TDobl2C5ECY7qZaWfUkEnUw75 L3aEEdqSZQdmeqmqwQ8TLXtkWLt5s7jaex9fKjrccYPJumiZeqPoeD++s3Xe29lk inpA/YVxyQr7nbTe3oJo0issJGSM4Wx4rN8fsjph1DLOVYPU+qtaa5IuHIkBKl7o pSGd/neG99yTtRxD5AUsWw3ypepMteixHhngjI8BDRxTAHZPBhNlhDCxA8WqClhp rgycyBgaK8X60fTr4NheTRpd+627gobpb0y2Mn+6KRBZOf0ypPng7++Mil52TQwW 1cCTwE3aE/ytHvAWMD7k8ApVrboyCmC4i51ySIzM+htIeCHMXxzISZO89IskiZlg OU3U/SY3n1yJtKc6T1tPQprAChNYzw+u9X+I6AxHbliopGf1W0weAC6j+q5ITQ+p wl+IzZCdGucMYgq7XyRb0vZh17FQVOPjZ/HDaFEmCD0C3iL0wFVOpof8zNN7N3wj MzpEyB0gkT0944nEi/iYXDW+cyQlL7Xo4ZuPQACISRRfXiSMd+c= =mJud -----END PGP SIGNATURE----- --Duda26nu9BtQr/Q7--