I'd like to get a textual SQL representation of expression and partial
indexes. I've found that the information is somehow contained in
pg_index indexprs and indpred, but apparently in parsed form. There
obviously is a way to get back an SQL representation, pgadmin3 does it,
but how?

Michael

--
Michael Schuerig
mailto:michael (AT) schuerig (DOT) de
http://www.schuerig.de/michael/

Michael Schuerig <michael (AT) schuerig (DOT) de> wrote:
see pg_indexes, column indexdef. Or use pg_get_indexdef(index_oid).

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Andreas Kretschmer wrote:

Thanks, I stopped when I found pg_index and didn't notice pg_indexes.

Michael

--
Michael Schuerig
mailto:michael (AT) schuerig (DOT) de
http://www.schuerig.de/michael/

Andreas Kretschmer wrote:

I have been a bit sloppy in my wording. I get what I asked for, but not
in exactly the form I want:

SELECT DISTINCT i.relname, d.indisunique, c.i,
CASE WHEN d.indkey[c.i] = 0 THEN NULL
ELSE a.attname
END,
pg_get_indexdef(i.oid, c.i + 1, true) AS column_def,
pg_get_indexdef(i.oid, 0, true) AS index_def
FROM pg_class AS i
JOIN pg_index AS d ON d.indexrelid = i.oid
JOIN pg_class AS t ON t.oid = d.indrelid
JOIN pg_attribute AS a ON (a.attrelid = t.oid)
JOIN generate_series(0, 9) AS c(i) ON d.indkey[c.i] IN (a.attnum, 0)
WHERE t.relname = 'people'
AND t.relnamespace IN
(SELECT oid FROM pg_namespace WHERE nspname IN ('public'))
AND d.indisprimary = 'f'
ORDER BY i.relname, c.i;

For a partial index, I can extract the WHERE clause from the result I
get from pg_get_indexdef(indexoid, 0, true) -- but do I have to?

Michael

--
Michael Schuerig
mailto:michael (AT) schuerig (DOT) de
http://www.schuerig.de/michael/