On 2023-Mar-23, Dominique Devienne wrote:
> We have a query returning 1 row per constraint column,
> which until recently we didn't realize wasn't preserving order of the
> columns.
>
> A colleague fixed that, with something like below:
>
> SELECT ...
> FROM pg_catalog.pg_constraint cnstr
> ...
> CROSS JOIN LATERAL UNNEST(cnstr.conkey) WITH ORDINALITY AS cols(value, rank)
> ORDER BY cols.rank
>
> But I'm wondering about getting 1 row per constraint instead,
> and fetching an array of column names.
IIRC you can use array_agg(unnest ORDER BY ordinality), grouping by
constraint OID or name.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"La espina, desde que nace, ya pincha" (Proverbio africano)