Обсуждение: Query to retrieve all indexed columns

<div class="Section1"><p class="MsoNormal">Hi,<p class="MsoNormal"> <p class="MsoNormal">I am working on a query to
retrieveall indexed columns and came up with the following query:<p class="MsoNormal"> <p class="MsoNormal">select
pgc.relnameas indexname<p class="MsoNormal">      ,pgc2.relname as tablename<p class="MsoNormal">      ,pga.attname as
columnname<pclass="MsoNormal">      ,pga.attnum as columnnumber<p class="MsoNormal">      ,replace(pgi.indkey::text, '
',',') as columnindex<p class="MsoNormal">  from pg_class pgc<p class="MsoNormal">  join pg_namespace pgn ON (pgn.oid =
pgc.relnamespace<pclass="MsoNormal">                            AND pgn.nspname = 'public')<p class="MsoNormal">  left
joinpg_index pgi ON (pgi.indexrelid = pgc.oid)<p class="MsoNormal">  left join pg_class pgc2 ON (pgc2.oid =
pgi.indrelid)<pclass="MsoNormal">  left join pg_attribute pga ON (pga.attrelid = pgc2.oid<p
class="MsoNormal">                                AND attnum::text IN (replace(pgi.indkey::text, ' ', ',')))<p
class="MsoNormal">wherepgc.relkind = 'i'<p class="MsoNormal">order by indexname, columnindex;<p class="MsoNormal"> <p
class="MsoNormal">Tisquery works for single column indexes, but with multiple column indexes I get incorrect results…
I’mhaving a hard time figuring out how to join pg_attribute.indkey in this, could anyone help me out on this one?  <p
class="MsoNormal"> <pclass="MsoNormal">Thanks in advance,<p class="MsoNormal"> <p class="MsoNormal"><b><span lang="NL"
style="font-size:10.0pt;font-family:"Arial","sans-serif";
color:#1B5DA7">Bart van Houdt</span></b><p class="MsoNormal"><span lang="NL"
style="font-size:10.0pt;font-family:"Arial","sans-serif";
color:black">Syfact International B.V.</span><p class="MsoNormal"><span lang="NL"
style="font-size:10.0pt;font-family:"Arial","sans-serif";
color:black">Database developer</span></div>

"Bart van Houdt" <bart.van.houdt@syfact.com> writes:
> I am working on a query to retrieve all indexed columns and came up with
> the following query:
> ...
> Tis query works for single column indexes, but with multiple column
> indexes I get incorrect results... I'm having a hard time figuring out
> how to join pg_attribute.indkey in this, could anyone help me out on
> this one?  

Look at the information_schema views.  You might find that one of them
already solves your problem ... and even if not, they will provide
examples of how to deconstruct the catalog information.
share/information_schema.sql in an installed PG file set is the
most convenient thing to study.
        regards, tom lane