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