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