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 1pfkSt-0002Qk-SN for pgsql-general@arkaria.postgresql.org; Fri, 24 Mar 2023 16:40:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pfkSs-0000jo-7O for pgsql-general@arkaria.postgresql.org; Fri, 24 Mar 2023 16:40:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pfkSq-0000jf-M8 for pgsql-general@lists.postgresql.org; Fri, 24 Mar 2023 16:40:29 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pfkSi-00042I-3F for pgsql-general@postgresql.org; Fri, 24 Mar 2023 16:40:28 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.nyi.internal (Postfix) with ESMTP id 4425E5C0072; Fri, 24 Mar 2023 12:40:17 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Fri, 24 Mar 2023 12:40:17 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm1; t= 1679676017; x=1679762417; bh=rs9oRW/pcjyaeEUDWnli4pfi/w2mZVY9Ilv pzlQQar8=; b=nn8RIDewNLEfmGp+bSgiGy8wSyRADQuYabLg7Hx+Lvj86AJlt7P M4qZ7XUoqUrIa+PoyRulcl41Wjxe1Pl4WKAGLv08ipV0PnlKhtGH6LmG00T3MF/0 T9BcUdxVsL6UbIIzTKiYYuHOKs4DrRGdw4z1tLc82qbIVrzulAYtKWyMMsiCb+gX cqe+hwRDS8XSdbDOsUb21cgGXGRO6DTGPlcuk/nR6k9nAPcBPvJHNfGSbyYkADOH JxVYbnOYLk/ibYaxrt115hBUoq1g6aOUUhrco9Jag8QuS6AA4RlNNt6fwQBnzlKU mvigOAbacxrEi4/4d4FfWt2FqErd2gG/O9A== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t= 1679676017; x=1679762417; bh=rs9oRW/pcjyaeEUDWnli4pfi/w2mZVY9Ilv pzlQQar8=; b=niKtJnMsUScgyeVFaniyjbleFCrUZYQ0NpegThRwKOuaWD+hukl 5/ofi7lMErL0jySi8+kjZdevMS1waV8COtyREV1/lkhCxDdus3I0WZhcHCb9CeyM ubjCvOmQAqbE3hyJoOAzCc4jkflFGKeStZDSPm8ZSDNoBeHGx+SiaHv6CVeD+ia9 mqrjKd85AaoIonOLQOgTjJL6l1EdAb0vXpfVMsslIS6lpApSd3K91p48NTkGQn+9 SLGoA+nautjAPP6VlL5VS6TcY3GcBqVryrJUqUK5PlLDHV1uXuPOlGrk9BRFO5BL nh3QaIgGpkV8vDsZ0MJuv6Dsw9NJvWP1Ocw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrvdegiedgledvucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvvehfhfgjtgfgsehtkeertddtfeejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhephedugfethfettdehieevteeukedtjedvhfevkefg udefgeevteduvdeuvdeggeegnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpe hmrghilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 24 Mar 2023 12:40:16 -0400 (EDT) Message-ID: <3f21d1db-9240-ed81-e130-c19ce61fdbb7@aklaver.com> Date: Fri, 24 Mar 2023 09:40:15 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.0 Subject: Re: Convert pg_constraint.conkey array to same-order array of column names To: Dominique Devienne Cc: pgsql-general@postgresql.org References: <4916ad6f-7677-d061-d447-d73176eaba4a@aklaver.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/24/23 03:28, Dominique Devienne wrote: > On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver > wrote: > > On 3/23/23 04:12, Dominique Devienne wrote: > > CROSS JOIN LATERAL UNNEST(cnstr.conkey) WITH ORDINALITY AS > cols(value, rank) > >   ORDER BY cols.rank > A before coffee solution: > > > Thanks for answering Adrian. And sorry for the delay in responding. > > WITH ck AS ( >      SELECT >          conrelid, >          unnest(conkey) AS ky >      FROM >          pg_constraint >      WHERE >          conrelid = 'cell_per'::regclass > ) > > > This part surprised me. I didn't know a table-valued function could be > used like this on the select-clause. > > Both queries below yield the same rows for me, in the same order: > > => select conname, unnest(conkey), conrelid::regclass::text from > pg_constraint where conrelid::regclass::text like ... and > cardinality(conkey) = 8; > => select conname, key.value, conrelid::regclass::text from > pg_constraint cross join lateral unnest(conkey) as key(value) where > conrelid::regclass::text like ... and cardinality(conkey) = 8; > > So your compact form is equivalent to the second form? > What about the order? Is it guaranteed? > I was "raised" on the "order is unspecified w/o an order-by-clause". Why > would be it be different here? > In our case, the query is more complex, with joins on pg_namespace, > pg_class, and pg_attribute, on > all constraints of a schema, and the order came out wrong w/o adding > WITH ORDINALITY and ordering on it. Your original question was: "But I'm wondering about getting 1 row per constraint instead, and fetching an array of column names. So is there a way to "convert" int2[] conkey array into a text[] of those column names?" That is what I showed as a simple example. > > Thus I worry the order is plan-dependent, and not guaranteed. Am I wrong > to worry? > The form you provide seems no different from our old form, to my > non-expert eye. --DD -- Adrian Klaver adrian.klaver@aklaver.com