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.klaver@aklaver.com