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@xxxxxxxxxxx
