From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Convert pg_constraint.conkey array to same-order array of column names |
Date: | 2023-03-24 10:28:49 |
Message-ID: | CAFCRh-_--K41mOJf3BqR-2h2KZQcY9+3Snnbpz2ph0LA357Jjg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2023-03-24 10:51:19 | Re: Convert pg_constraint.conkey array to same-order array of column names |
Previous Message | Inzamam Shafiq | 2023-03-24 08:00:37 | Re: Oracle to PostgreSQL Migration |