On Thu, Mar 23, 2023 at 4:20 PM Adrian Klaver <adrian.kla...@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. 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
