Re: Convert pg_constraint.conkey array to same-order array of column names

On 3/23/23 04:12, 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.
>
> So is there a way to "convert" int2[] conkey array into a text[] of
> those column names?

A before coffee solution:

WITH ck AS (
SELECT
conrelid,
unnest(conkey) AS ky
FROM
pg_constraint
WHERE
conrelid = 'cell_per'::regclass
)
SELECT
array_agg(attname)
FROM
ck
JOIN
pg_attribute
ON
attnum = ck.ky AND ck.conrelid = pg_attribute.attrelid;

array_agg
-----------------------------
{category,line_id,category}

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com