In-Reply-To: <112402456@web.de>
References: <112402456@web.de>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <DE38B02B-CC33-409E-88AD-6EB53F504E88@skype.net>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Kristo Kaiv <kristo.kaiv@skype.net>
Subject: Re: show index from [table]
Date: Mon, 11 Jun 2007 09:28:13 +0300
To: Stefan Zweig <stefanzweig1881@web.de>


On 08.06.2007, at 18:25, Stefan Zweig wrote:
>
> but actually i would need the information from within a (postgres)  
> sql-query. is there a possibility to get information about the  
> indices which have been created on a table?
>
> if there is not, it might be sufficient for me to get the create  
> index strings, such like you get, when viewing a table in pgAdmin:


you can turn on echoing of psql commands sent to server with:
psql -E dbname
from there you can get the queries needed:

find the oid of table (unique object id)
********* QUERY **********
SELECT c.oid,
   n.nspname,
   c.relname
FROM pg_catalog.pg_class c
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(miljon)$'
   AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

find table indices (replace the oid by the value found with your  
previous query)
********* QUERY **********
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered,  
i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),  
c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,  
pg_catalog.pg_index i
WHERE c.oid = '16427' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname

here is my sample output:
    relname    | indisprimary | indisunique | indisclustered |  
indisvalid |                       
pg_get_indexdef                       | reltablespace
--------------+--------------+-------------+---------------- 
+------------ 
+------------------------------------------------------------ 
+---------------
miljon_pkey  | t            | t           | f              |  
t          | CREATE UNIQUE INDEX miljon_pkey ON miljon USING btree  
(id) |             0
idx_blahblah | f            | f           | f              |  
t          | CREATE INDEX idx_blahblah ON miljon USING btree  
(sisu)     |             0
(2 rows)

hope this helps

Kristo