pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Dominique Devienne <ddevienne@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Convert pg_constraint.conkey array to same-order array of column names
Date: Fri, 24 Mar 2023 09:40:15 -0700
Message-ID: <3f21d1db-9240-ed81-e130-c19ce61fdbb7@aklaver.com> (raw)
In-Reply-To: <CAFCRh-_--K41mOJf3BqR-2h2KZQcY9+3Snnbpz2ph0LA357Jjg@mail.gmail.com>
References: <CAFCRh-_RY7x0AE0=j-Vu5i5f+FQ=x6ze+4wp1suyAZ=nPQVS1A@mail.gmail.com>
	<4916ad6f-7677-d061-d447-d73176eaba4a@aklaver.com>
	<CAFCRh-_--K41mOJf3BqR-2h2KZQcY9+3Snnbpz2ph0LA357Jjg@mail.gmail.com>

On 3/24/23 03:28, Dominique Devienne wrote:
> On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> 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






view thread (8+ messages)  latest in thread

Message-ID: <3f21d1db-9240-ed81-e130-c19ce61fdbb7@aklaver.com>
Permalink:  ../3f21d1db-9240-ed81-e130-c19ce61fdbb7@aklaver.com/
Also on:    postgresql.org/message-id/3f21d1db-9240-ed81-e130-c19ce61fdbb7@aklaver.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, ddevienne@gmail.com
  Subject: Re: Convert pg_constraint.conkey array to same-order array of column names
  In-Reply-To: <3f21d1db-9240-ed81-e130-c19ce61fdbb7@aklaver.com>
  References: <CAFCRh-_RY7x0AE0=j-Vu5i5f+FQ=x6ze+4wp1suyAZ=nPQVS1A@mail.gmail.com> <4916ad6f-7677-d061-d447-d73176eaba4a@aklaver.com> <CAFCRh-_--K41mOJf3BqR-2h2KZQcY9+3Snnbpz2ph0LA357Jjg@mail.gmail.com> <3f21d1db-9240-ed81-e130-c19ce61fdbb7@aklaver.com>

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

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