How do I make this postgres query to find table indexes compatible with versions below 9.4?-CodePudding

I maintain Beekeeper Studio, and one of it's functions is to display index information.

To get index information in Postgres I use the below query, but turns out this isn't compatible with Postgres < 9.4 because of the use of WITH ORDINALITY.

I'm really struggling to rewrite this in a way that would work for earlier versions. Hoping someone can offer some advice?

    SELECT i.indexrelid::regclass AS indexname,
        k.i AS index_order,
        i.indexrelid as id,
        i.indisunique,
        i.indisprimary,
        coalesce(a.attname,
                  (('{' || pg_get_expr(
                              i.indexprs,
                              i.indrelid
                          )
                        || '}')::text[]
                  )[k.i]
                ) AS index_column,
        i.indoption[k.i - 1] = 0 AS ascending
      FROM pg_index i
        CROSS JOIN LATERAL unnest(i.indkey) WITH ORDINALITY AS k(attnum, i)
        LEFT JOIN pg_attribute AS a
            ON i.indrelid = a.attrelid AND k.attnum = a.attnum
        JOIN pg_class t on t.oid = i.indrelid
        JOIN pg_namespace c on c.oid = t.relnamespace
      WHERE
       c.nspname = $1 AND
       t.relname = $2

CodePudding user response：

You can try this which has been tested in postgres 9.5, but not before :

SELECT i.indexrelid::regclass AS indexname,
    k.i AS index_order,
    i.indexrelid as id,
    i.indisunique,
    i.indisprimary,
    coalesce(a.attname,
              (('{' || pg_get_expr(
                          i.indexprs,
                          i.indrelid
                      )
                    || '}')::text[]
              )[k.i]
            ) AS index_column,
    i.indoption[k.i - 1] = 0 AS ascending
  FROM pg_index i
    CROSS JOIN LATERAL (SELECT unnest(i.indkey), generate_subscripts(i.indkey, 1)   1) AS k(attnum, i)
    LEFT JOIN pg_attribute AS a
        ON i.indrelid = a.attrelid AND k.attnum = a.attnum
    JOIN pg_class t on t.oid = i.indrelid
    JOIN pg_namespace c on c.oid = t.relnamespace
  WHERE
   c.nspname = $1 AND
   t.relname = $2