Oracle以及PG中將指定使用者的primary，unique索引按照指定格式輸出

--- Oracle

SELECT
    LOWER(c.table_name) || ':' || LOWER(i.index_name) || ':' || LOWER(wm_concat(c.column_name)) AS output
FROM
    all_indexes i
JOIN
    all_ind_columns c ON i.index_name = c.index_name AND i.table_name = c.table_name AND i.table_owner = c.table_owner
WHERE
    i.owner = 'SIT'
    AND (i.index_type = 'NORMAL' OR i.index_type = 'UNIQUE')
    AND i.table_owner = 'SIT'
GROUP BY
    c.table_name, i.index_name;

--- PG

SELECT
    LOWER(t.table_name) || ':' || LOWER(i.indexname) || ':' || LOWER(string_agg(a.attname, ',')) AS output
FROM
    pg_indexes i
JOIN
    pg_class c ON i.indexname = c.relname
JOIN
    pg_attribute a ON c.oid = a.attrelid AND i.indexrelid = a.attnum
JOIN
    information_schema.tables t ON c.relname = t.table_name
WHERE
    t.table_schema = 'SIT'
    AND (i.indexdef ILIKE 'PRIMARY%' OR i.indexdef ILIKE 'UNIQUE%')
GROUP BY
    t.table_name, i.indexname;